Forum Discussion
Freeze Panes issue with Power Query
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
Hi DannyMcMate
Can you sen the script from the advanced editor?
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
Hi Mariusz
Here is the script with file name removed from File.Contents().
let
Source = Excel.Workbook(File.Contents(), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data_Sheet,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", type date}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type number}, {"Column9", type number}, {"Column10", type number}, {"Column11", type any}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type date}, {"Column18", type date}, {"Column19", type any}, {"Column20", type any}, {"Column21", type text}})
in
#"Changed Type"
Hi DannyMcMate
Sorry, there is nothing I can do with the code to fix the issue.
You can try naming the rage and see if it helps
Mariusz