Power Query is not loading latest information from excel files

[larsschottmadsen]

Hi all

Firstly I have to admit that I don't know if my issue is related to Power Query, VBA or Excel in general .

I am using PQ to merge data from around 100 Excel files. Each Excel file contains a named data area which is loaded into the PQ master file. The Excel sheets are all containing a data connection to an SQL database so prior to refreshing my PQ master file I am refreshing the data connection in all my 100 Excel files. I have therefore made (read: stole) a VBA like this:

Sub UpdateAllLIVESheets()


Set fso = CreateObject("Scripting.FileSystemObject")
Set xl = CreateObject("Excel.Application")
'xl.Visible = True


For Each f In fso.GetFolder("\\MyShare\").Files
  If LCase(fso.GetExtensionName(f.Name)) = "xlsx" Then
    Set wb = xl.Workbooks.Open(f.Path)
   ' wb.Connections.Refresh
   wb.RefreshAll
       DoEvents
    wb.Save
    wb.Close
  End If
Next


xl.Quit

Now - when I am running this macro I can see that all my sheets are getting updated and the date modified on the files is getting updated, so everything should be fine. But when I run my Power Query master file it will not load the fresh data from the files? In order to do so I have to open the file and save it manually (even without refreshing data or making any other modification to the file). It will then load correctly into the PQ master file.

I have already tried to clear my PQ cache from Excel but this did not help.

Does anyone have an idea what is causing this issue? Thanks in advance.
LSM

 

[gazpage]

Not sure I understand exactly what is going on. So you've updated, saved, and closed all 100 files. Then you open your Power Query file, do Refresh All, and what happens? Nothing? Or does it appear to update but just result in stale data?

Separately, is it necessary to create the intermediate 100 excel files? If you can somehow bring this into Power Query and use Query Folding it will be so much faster. May not be possible of course if you are doing calculations in an intermediate step.

 

[larsschottmadsen]

Hi gazpage

Thanks for your reply. First of all I need the 100 files since these contain various logics. The results of each file needs to be loaded into a consolidation master file, so this is what I use PQ to do.

Once I have refreshed all the data source files (with the macro above) I do a refresh in the PQ masterfile. It is refreshing data but the numbers that are loaded into the file is not corresponding to the numbers in the source files. Only if I open up the file afterwards and save it manually (as opposed to the macro above) the correct result will be loaded into the PQ master file. T

wo strange things:
1) Not all my source files will behave in this way. Some of them will actually return the correct number to the master file.
2) When the wrong number is returned I have no idea what this is. I have for example tested if this number is referring to a previous version of the file as if it is not updated in PQ correctly but even this is not the case.

Lars