Preserving data types and file configuration when working on existing excel files

[coyote1223]

Hello. I have an existing excel file that has multiple sheets and the data type of values in the cells is "Standard". When I simply open and close the file, the data types change from "Standard" to "Accounting". My goal is to open that existing file, make a change to one of its sheets and keep the configuration and settings (pivots, formulas etc.) it had before I edited the file.

This is the code I use to open and close the file:

$filePath = "C:\Users\myUserName\excelFiles\excel-Management.xlsx"
$workbook = Open-ExcelPackage -path $filePath
Close-ExcelPackage $workbook -SaveAs $filePath

I tried using the Set-WorksheetProtection in different ways but it unfortunately did not work.

I would be very thankful for support on this.

[dfinke]

Sorry - don't know. I have not worked with data and types in Excel with "Standard" to "Accounting".

I'm assuming you create or are given the xlsx already setup? Or do you create it with PowerShell and the module?

If the xlsx is created elsewhere and you need to process it, what happens if you open the xlsx and you change things, do the types change from "Standard" to "Accounting"?

[coyote1223]

Yes exactly. I have an existing .xlsx that my bosses use and in that file I want to edit one sheet with the data I have processed and created with ImportExcel module. And yes, when I do that, the data types change automatically from "Standard" to "Accounting". I would like to have all configurations and settings stay the same as they were and I do not know how to do that with ImportExcel.

[dfinke]

If I understand, it you take the file as is, open it, the types change?

It that happens, ImportExcel cannot help.
If it only happens when you use the PS ImportExcel - I don't know really how or why that occurs.

[coyote1223]

Yes, when I open the file with ImportExcel with the above code example I gave, then the data types in the cells change. When I open the excel file by hand manually then nothing changes.
Hmm okay. I might have to find a workaround for that then with a different module. Still, ImportExcel has been very helpful and has enabled me to do some good work so thank you for that!

[dfinke]

what happens if you take the file, don't open it with ImportExcel. Instead - open it with the Excel app. Does that change the types?

[coyote1223]

The types don't change when I open the file with Excel app. They remain the same. I just managed to open that file using ComObject approach, write some data to it and close it. The data types did not change...
Interesting, but at least I managed to do my work with that approach.

[dfinke]

Yes. I suspect the ImportExcel has an issue in a lower layer EPPLus.dll. Not sure it could be fixed.

-COM solves it