Power Query is not loading latest information from excel files

larsschottmadsen

New Member
Joined
Jun 8, 2017
Messages
2
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:

Code:
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,573
Messages
6,173,138
Members
452,501
Latest member
musallam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top