0

Refresh Excel Query after Running Jet report

mike young

April 24, 2025 10:06

None

Follow

I have Excel power Query's with the source data referencing a Jet report (contain in the same workbook). Currently I have to "Run" the Jet report then click on Excels "Refresh all" command to update the Power Queries. Is there a way to run / refresh both Jet and Queries in one procedure?

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  RJ Ek April 24, 2025 15:00

  The workaround I've had success with is to use a VBS script and a scheduled task to update the power query workbook. Separate the workbooks, one that's the jet report and the other is the power query workbook. Create a scheduled job for the jet report to run/update automatically, and update the source on the power query workbook to point to output of the scheduled jet report. 

  Now, in Notepad, paste the code below (changing the path to the location of the Power Query workbook) and save as a .vbs filetype. In Task Scheduler, have it start a program (C:\Windows\System32\wscript.exe) with the arguments pointing at the location of the VBS file. 

   

• 0
  
  mike young April 24, 2025 15:14

  Hi RRJ Ek,

  I am using windows task scheduler and a VB script on other files and was hoping there was a way to update Jet and the query in one process. The files I am working with are 40Mb so opening and saving takes a while. When you need to do that process twice (Jet and Query) it's slow, even when it's automated.

  I have recently discover that you can have the Query and the Jet report in one document without causing any issues... The jet part of the document will "Run" without upsetting the Query. You would think Jet would have a function to refresh the query, as a part of Jet's "Run" operation.

  I think you might be able to do this with a pivot table??? which isn't a million miles way for what I am wanting to achive.

• 0
  
  RJ Ek April 25, 2025 13:30

  Dim oExcel
  Set oExcel = CreateObject("Excel.Application") 

  oExcel.Visible = True
  oExcel.DisplayAlerts = False
  oExcel.AskToUpdateLinks = False
  oExcel.AlertBeforeOverwriting = False

  Set oWorkbook = oExcel.Workbooks.Open("FILE PATH")
  oWorkbook.RefreshAll
  oWorkbook.Save

  oExcel.Quit
  Set oWorkbook = Nothing
  Set oExcel = Nothing