Add data validation to a dynamic range.

[Espnlee1]

Given a scenario where there are hundreds of files all with different ranges, how could we add data validation for a specific labeled column dynamically?

The list of strings can be static (I.e., Yes, No, Maybe) but the range to validate will be different for each file.

The below script takes a master file and creates new files based on the email column

$xlfile = nba.hof.xlsx

foreach($coach in (Import-Excel $xlfile | group 'Coach')) {
    $newXlfile = "C:\temp\$($coach.Name) Team Players.xlsx"
    #rm short for remove item
    rm $newXlfile -ErrorAction SilentlyContinue

    $manager.group | Export-Excel $newXlfile -WorksheetName $manager.Name -FreezeTopRow -TableStyle Light8 -BoldTopRow -AutoFilter -AutoSize

    $newXlfile
}
[nba.hof.xlsx](https://github.com/dfinke/ImportExcel/files/12754711/nba.hof.xlsx)

In the attached file the Player Hall of fame is the column that needs the validation.

[dfinke]

Does this help?

https://github.com/dfinke/ImportExcel/blob/master/Examples/ExcelDataValidation/MutipleValidations.ps1#L0-L1

[Espnlee1]

Thanks Doug, This helps but I think I’m struggling with defining the dynamic range. The ranges in those example are hard coded. In place of trying to define a dynamic range, would a better approach be to define a range you know the data will not exceed? If I take that approach it will be out of range but maybe wouldn’t be an issue? If so, those examples will help.

[dfinke]

Could you say a bit more about defining the dynamic range. You want that in Excel? I wonder if VBA can do it. If so, could be a clue how to do it with ImportExcel

[Espnlee1]

Each workbook (worksheet) will have a different range of rows but will have the same columns. In the examples, the validation went to 1001.

Chatgpt gave the below for getting dynamic column range.

 Define the worksheet where your data is located
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

    ' Specify the column number you want to get the dynamic range for
    col = 1 ' Change 1 to the desired column number

    ' Find the last row in the specified column
    lastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row

    ' Define the dynamic range
    Set rng = ws.Range(ws.Cells(1, col), ws.Cells(lastRow, col))

[dfinke]

ChatGPT FTW!

You can get the sheet dimnsion this way.

$xl = Open-ExcelPackage .\test.xlsx
$xl.Workbook.Worksheets["sheet1"].Dimension
Close-ExcelPackage $xl

Start   : OfficeOpenXml.ExcelCellAddress
End     : OfficeOpenXml.ExcelCellAddress
Table   :
Address : A1:I42
IsName  : False
Rows    : 42
Columns : 9

[Espnlee1]

Thanks Doug, I’m am now getting the below error with the following code. Also, -ShowErrorMessage $true seems to throw an error I haven’t been able to recreate due the existing issue.

Confirmed both variables $ws and $range are strings.

Would also like to know how get the dimensions of just 1 column starting the second cell?

$xl = Open-ExcelPackage ".\nba.hof.xlsx"

#Index first worksheet
$ws = $xl.Workbook.Worksheets[1] | Select-Object -ExpandProperty name

#Returns the dimensions of the entire workbook but how do I get the range for 1 column?
$range = $xl.Workbook.Worksheets[$ws].Dimension.Address

#set validation rules without splatting
$rt = Add-ExcelDataValidationRule -Worksheet $ws -Range $range -ShowErrorMessage $true -ErrorStyle stop -ErrorTitle 'Invalid Data' -ValidationType List `
-ValueSet @('YES', 'NO', 'INCORRECT OWNER') -ErrorBody "You must select an item from the list."

Add-ExcelDataValidationRule : Cannot process argument transformation on parameter 'Worksheet'. Cannot convert the "Sheet1" value of type "System.String" to type "OfficeOpenXml.ExcelWorksheet".
At line:11 char:46
+ $rt = Add-ExcelDataValidationRule -Worksheet $ws -Range $range -ShowE ...
+                                              ~~~
    + CategoryInfo          : InvalidData: (:) [Add-ExcelDataValidationRule], ParameterBindingArgumentTransformationException
    + FullyQualifiedErrorId : ParameterArgumentTransformationError,Add-ExcelDataValidationRule

[Espnlee1]

I tried a slightly different approach and still resulted in error. Any suggestions? ChatGPT has not provided me any working solution either.

Create Manager files from baseline where coach column -eq null
foreach ($coach in $baselineTable.Where({$_."coach" -like $null}) | group "coach"){
    $coachName = $coach.Group | select -First 1 coach
    $newXlfile = "$certfolder\$prefix $($coachName.coach) HOF PLAYERS.xlsx"
    #rm short for remove item
    rm $newXlfile -ErrorAction SilentlyContinue

    $data =  $coach.group

    $excelPackage = $data | Export-Excel $newXlfile -WorksheetName $manager.Name -FreezeTopRow -TableStyle Light8 -BoldTopRow -AutoFilter -AutoSize
    $ValidationParams = @{
    Worksheet        = $coach.Name
    ShowErrorMessage = $true
    ErrorStyle       = 'stop'
    ErrorTitle       = 'Invalid Data'
    }

    $MoreValidationParams = @{
    Range          = 'D2:D100001'
    ValidationType = 'List'
    ValueSet       = @('YES', 'NO', 'MAYBE')
    ErrorBody      = "You must select an item from the list."
    }

    Add-ExcelDataValidationRule @ValidationParams @MoreValidationParams

    Close-ExcelPackage -ExcelPackage $excelPackage

} #End foreach

Returns the below error:
```powershell

Add-excelDatavalidationule:cannot process argument transformation on parameter 'worksheet'. cannot convert the "
"officeopenxm1. Excelworksheet".
At line: 38 char: 33
Add-ExcelDatavalidationRule @validationParams @MorevalidationPara ...
CategoryInfo
Invalidata: (:) [Add-ExcelDatavalidationRule], ParameterBindingArgumentransformationException + FullyQualifiedErrorId: ParameterArgumentTransformationError,Add-ExcelDatavalidationRule
close-Excelpackage: cannot bind argument to parameter 'Excelpackage' because it is null.
At line:40 char: 38
close-ExcelPackage -ExcelPackage SexcelPackage
• CategoryInfo
: Invalidata: (:) [close-ExcelPackage], ParameterBindingvalidationException

[dfinke]

Too much going on for me to determine. The err msg misspells the function name, weird. I believe the Worksheet needs to be the actual instance of a sheet, and not a string.

[Espnlee1]

Agreed, I cleaned up the example, added some notes, and used Open-ExcelPackage to get the actual instance of a sheet.

No errors on the below but no data validation in column D of any of the files. Any idea what’s missing for this?

#Import-Excel
$coachData = Import-Excel "nba.hof.xlsx"

#Create files for coaches based on column C (Coach) where coach is not like $null
foreach ($coach in $coachData.Where({$_."coach" -notlike $null}) | group "coach"){

    #Create output file name for each and thier players data.
    $newXlfile = "$($coach.name) HOF Palyers.xlsx"

    #rm short for remove item
    rm $newXlfile -ErrorAction SilentlyContinue

    #Export data for each coach into seperate Excel files
    $coach.group | Export-Excel $newXlfile -WorksheetName $coach.Name -FreezeTopRow -TableStyle Light8 -BoldTopRow -AutoFilter -AutoSize

    #Opening Excel Package to get the actual instace of the first sheet in $newXlfile workbooks and assign to the $ws variable
    $xl = Open-ExcelPackage $newXlfile
    $ws = $xl.Workbook.Worksheets[1]

    #Splatting for validation based on example
    $ValidationParams = @{
    Worksheet        = $ws
    ShowErrorMessage = $true
    ErrorStyle       = 'stop'
    ErrorTitle       = 'Invalid Data'
    }#end $ValidationParams

    #Additional Splatting for validation based on example
    $MoreValidationParams = @{
    Range          = 'D2:D100001'
    ValidationType = 'List'
    ValueSet       = @('YES', 'NO', 'MAYBE')
    ErrorBody      = "You must select an item from the list."
    }# End $MoreValidationParams

    #Apply data validation to Each file
    Add-ExcelDataValidationRule @ValidationParams @MoreValidationParams

} #End foreach

[dfinke]

could post a simple repro I can run and look into?

[Espnlee1]

I invited you as a contributor to repo add-validation

[dfinke]

Sorry, I won't be able to do that.

Small repros of issues please.