Wait until query is refreshed to run the next macro

[AydeeDiaz]

Hi! I am trying to create a macro, but I need to wait until query is refreshed before doing the copy to paste in the workbook

Sub OpenAnyFile()

VBA.Shell "Explorer.exe C:\Users\vjrdcx\Aptiv\Aptiv Global Business Services - Documents\SPM\Open Items Report\OTC Semantic 2.0 AS&UX 2.xlsm", vbMaximizedFocus

'Wait until query is refreshed

End Sub

'Private Sub WorkbookCopyData()

'Workbooks("OTC Semantic 2.0 AS&UX 2.xlsm").Worksheets("AS&UX").Range("A2:AZ60000").Copy_
'Workbooks("2022.09.00 AS&UX Open Items.xlsm").Worksheets("AS&UX").Range ("A2")

'End Sub

Sub CopyData()

Dim mWB As Workbook 'This Workbook
'XDim dWB As Workbook 'XXData Workbook

'Set the main workbook
Set mWB = ThisWorkbook

'XX Selection the file manually XX
'XDim filePath As String
'XfilePath = Application.GetOpenFilename
'XDebug.Print filePath

'XX Set the data workbook XX
'XSet dWB = Workbooks.Open(filePath)
'XDIM dataSh As Worksheet
'XSet dataSh = dWB.Sheets("AS&UX")

'XX Copy the data table XX
'XdataSh.Range("A1").CurrentRegion.Copy

Workbooks("OTC Semantic 2.0 AS&UX 2.xlsm").Worksheets("AS&UX").Range("A2:AZ60000").Copy

'Paste the data table
mWB.Sheets("AS&UX").Range("A2").PasteSpecial xlPasteAll

'XsWB.Close False

End Sub

Sub WorkbookChange()

'ThisWorkbook.RefreshAll

'For Each Workbook In ThisWorkbook.Sheets
'For Each pvt In Workbook.PivotTables
'pvt.RefreshTable
'pvt.Update
'Next
'Next

'PivotTables("PivotTable10").RefreshTable

Dim pt As PivotTable

For Each pt In ThisWorkbook.Worksheets("PIVOT").PivotTables

pt.PivotCache.Refresh

Next pt

'Worksheets("PIVOT").PivotTables("PivotTable10").PivotCache.Refresh

End Sub

'Close Workbook datasource

Sub CloseWorkbook()

Workbooks("OTC Semantic 2.0 AS&UX 2.xlsm").Close SaveChanges = True

End Sub

'Completed message

Sub Message()

MsgBox ("The Refreshing is Completed!")

End Sub

'Save Workbook with today;s date

Sub SaveToday()

ActiveWorkbook.SaveAs ("C:\Users\vjrdcx\Aptiv\Aptiv Global Business Services - Documents\SPM\Open Items Report") & ".xlsx"

End Sub

[#2]

You can set the queries in question to be synchronous.

[#3]

@Jan Karel Pieterse 

Sorry what do you mean??

[#4]

There is a checkbox in the query properties that either allows or disallows the query to run in the background. Make sure it disallows it.

[#5]

But the thinks is that I don't have a code to wait until query is refreshed in OTHER workbook and then come back to the code workbook and continue the macro, how can I do it?

[#6]

As long as the queries are set up to be synchronous, the VBA code does not matter as it will wait for the refresh to finish before proceeding.

[#7]

Hi, in my case it doesn't wait, is there any code that I can use to make the macro wait until de query in ANTOHER workbook is refreshed? I have tried this:

'____TRY 1

'With ActiveWorkbook.Connections("Query from A2").ODBCConnection.BackgroundQuery = False
'End With
'ActiveWorkbook.Connections("Query from A2").Refresh

'Call CopyData

'____TRY 2

'Dim cn As WorkbookConnection
'For Each cn In ThisWorkbook.Connections
'cn.Ranges(1).ListObject.QueryTable.Refresh BackgroundQuery:=False
'Next cn


'____TRY 3


'Dim dWB As Workbook 'Data Workbook
'Dim FilePath As String
'FilePath = "Explorer.exe C:\Users\vjrdcx\Aptiv\Aptiv Global Business Services - Documents\SPM\Open Items Report\OTC Semantic 2.0 AS&UX 2.xlsm"
'Set dWB = Workbooks(FilePath)

'Dim dWs As Worksheet 'Data Worksheet
'Set dWs = Sheets("AX&US")

'dWB.Activate
'ws.Select

'With dWB.Connections("Query from A2").ODBCConnection.BackgroundQuery = False
'End With
'dWB.Connections("Query from A2").Refresh

[#8]

@AydeeDiaz Try 2 does not look wrong to me, if you change ThisWorkbook to ActiveWorkbook it might just work?