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