2

I have been trying to automatically refresh my table (obtained from an SQL database via power query) and directly save it as a text file (tab delimited) I'm very new with VBA and the macro i used is 

    Public Sub UpdatePowerQueries()
 'Macro to update the Power Query script(s) and save the file as .txt

Dim cn As WorkbookConnection

For Each cn In ThisWorkbook.Connections
 If Left(cn, 13) = "Power Query -" Then cn.Refresh
 Next cn

Application.DisplayAlerts = False
ActiveSheet.SaveAs Filename:="customfile" & Format(Date, "yyyymmdd") & ".txt", FileFormat:=xlTextWindows
Application.DisplayAlerts = True


 End Sub

now the issue I've been facing is that the refresh part and the save part work ok on their own, but if I put them in the same macro, the save part happens too soon, and the text file is empty. Can anyone help me ?

Thanks

Havimo
  • 61
  • 4
  • 1
    There are a couple of different choices you have, though `DoEvents` should work. Look at this thread for the discussion [Wait until ActiveWorkbook.RefreshAll finishes - VB](http://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba) – PeterT Apr 08 '15 at 15:13
  • Thank you very much ! the DoEvent worked once i disabled the background refresh on the queries. – Havimo Apr 09 '15 at 08:37

1 Answers1

0

Personnaly, I have choose to be a bit patient and added a timer to go on after a few seconds!

Try to add this in between :

DoEvents
Application.Wait (Now + TimeValue("0:00:05"))
DoEvents
R3uK
  • 14,417
  • 7
  • 43
  • 77
  • Thank you very much ! the DoEvent worked once i disabled the background refresh on the queries – Havimo Apr 09 '15 at 08:37
  • You are welcome! ;) Plz accept answer (tick just under down vote) to close subject. – R3uK Apr 09 '15 at 08:56