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

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi DannyMcMate 

 

Just tested it and seems fine on my end.

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

DannyMcMate's avatar
DannyMcMate
Frequent Visitor
6 years ago

Hi Mariusz 

 

Thank you for trying to replicate this. I think it must be something to do with the generation of the file as here is what I get when I link to it without disabling freeze panes first. The column headers are ignored, and only the first row of data is shown (the report has 20k rows in it). If I disable freeze panes and try again it's fine, headers recognised and all rows included. Unfortunately I can't share the files as it contains senstive info.  Any thoughts?

 

 

 
  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi DannyMcMate 

     

    Try to mark this table as Table  in Excel 

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

    • DannyMcMate's avatar
      DannyMcMate
      Frequent Visitor
      6 years ago

      Hi Mariusz 

       

      I was hoping to find a method of correcting this in Power Query without having to open the files as there are many similar files that need to be saved down each month for this dashboard. I think this probably isn't possible though, the program generating of the files must be doing something weird and so the only option is to open and edit each file either by converting to a table as you suggest or disabling Freeze Panes before saving 😞

       

      Many thanks,

       

      D

      • Mariusz's avatar
        Mariusz
        Community Champion
        6 years ago

        Hi DannyMcMate 

         

        Can you sen the script from the advanced editor?

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        LinkedIn