Forum Discussion

DannyMcMate's avatar
DannyMcMate
Frequent Visitor
6 years ago
Solved

Freeze Panes issue with Power Query

Hi all,   I have an excel spreadsheet report that has by default when generated freeze panes enabled to ensure the column headers are always visible. This said, having freeze panes enabled in the f...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    It would be rather easy to write a VBA macro (or even a script in PowerShell or Python) that would go through all Excel files in a folder, silently open them without even showing the file on the screen, change the setting on every sheet and then save them. No manual work required and very quick. Excel is an automation OLE server, so you can automate it from almost anything... All you need is to import the Excel object library which can be found on every computer with Excel.

    Best
    D

Anonymous's avatar
Anonymous
Not applicable
6 years ago

It would be rather easy to write a VBA macro (or even a script in PowerShell or Python) that would go through all Excel files in a folder, silently open them without even showing the file on the screen, change the setting on every sheet and then save them. No manual work required and very quick. Excel is an automation OLE server, so you can automate it from almost anything... All you need is to import the Excel object library which can be found on every computer with Excel.

Best
D