Default view or cell position when opening Excel file

[tpidor]

At the moment, we are preparing an Excel file manually from Excel UI as our base template, put the initial required formatting, formula, etc and then we run a PowerShell script in Azure DevOps pipeline to generate copies of the template file, then read data from somewhere and populates the generated Excel files. However, we sometime forget to always set the default view or cell position so opening the Excel file in some cases the view starts from some cells that may not make sense to the first time viewer of the Excel file.

Is there some function in ImportExcel module that allow us to set the default view or default cell position when opening an Excel file?
Thanks in advance for any help.

[dfinke]

Thanks for using ImportExcel. Great question!

Does this help?

$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@

$xlfile = "d:\temp\activeCell.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue

$data | Export-Excel $xlfile -AutoSize -WorksheetName Tab1
$xlpkg = $data | Export-Excel $xlfile -AutoSize -WorksheetName Tab2 -Activate -PassThru
$xlpkg.Tab2.Select("C3")

Close-ExcelPackage $xlpkg -Show

[tpidor]

Hi @dfinke,
Thanks for your reply.
The tab.select() approach is not working. It does select the cell however it does not bring the view to that selected cell.
Please have a look and try the steps below.

Steps to recreate:
1 - First manually create an excel file called show-topleft-template.xlsx
2 - Scroll further down on Sheet1 until cell A1 is not shown anymore and click on cell A200.
3 - Type "This is to test if the display will show topleft cells"
4 - Save and close the file.
5 - Edit the script below with your own path for the workdir variable, save as ps1 script.
6 - Run the saved ps1 script.
7 - Open the generated show-topleft-result.xlsx file in your workdir path.
8 - Issue: The last saved view of the file is still shown instead of the selected A1 cell.

Install-Module -Name ImportExcel -Scope CurrentUser -force

$workdir = "<your_own_path>"

$templateFile = "$workdir\show-topleft-template.xlsx"
$resultFile = "$workdir\show-topleft-result.xlsx"

Copy-Item -Path $templateFile -Destination $resultFile

$xlsxObj = Open-ExcelPackage $resultFile
$xlsxObj."Sheet1".Select("A1")

Close-ExcelPackage -ExcelPackage $xlsxObj

[dfinke]

Don't know. Could be another setting needed, seems like it may need to have Excel open when set. ImportExcel only works on the xlsx. Not sure what that setting is.