Freeze Panes issue with Power Query

[DannyMcMate]

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 file seems to interfere with Power Query being able to correctly select the header row when I try to import data from this file, Power Query seems to only see the row directly below the row that freeze panes has been enabled on and truncates the rest of the report.

I am aware I can open the report and disable freeze panes, or turn the range into a table to correct this, but ideally I want to be able to just save the report into a folder and not have to open and edit it at all.

Thanks in advance!

D

[cscheeser]

hi I see same issue as of 7/2023. what is plan to fix, what is workaround?

[DannyMcMate]

Hi, the workaround I went with was to set up a macro that I could point at a folder containing my reports which would format the data in them as an Excel Table which I could then retrieve with PowerQuery.

This is the Sub I use:

Sub Select_Reports_Folder()

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String

'Macro optimisation settings
Application.Calculation = xlManual
Application.ScreenUpdating = False

'Retrieve reports folder path
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select Reports Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo ResetSettings
myPath = .SelectedItems(1)
End With

'Target file extension (must include wildcard "*")
myExtension = "*.xls*"

'Target path with ending extention
myFile = Dir(myPath & "\" & myExtension)

'Loop through each Excel file in folder
Do While myFile <> ""

'Set variable equal to opened workbook
Set wb = Workbooks.Open(Filename:=myPath & "\" & myFile)

'Formatting changes
wb.Sheets(1).Select
If Range("A1").CurrentRegion.ListObject Is Nothing Then
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "Table1"
wb.Close SaveChanges:=True
Else
wb.Close SaveChanges:=False
End If

'Get next file name
myFile = Dir
Loop

'Message box when tasks are completed
MsgBox "Task Complete"

'Reset macro optimisation settings
ResetSettings:
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub

[Anonymous · Accepted solution]

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]

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]

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]

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]

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]

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

[DannyMcMate]

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"

[Mariusz]

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