Solved Open Excel and allow to refresh external data and then save and close not workign on this bit ....

[Number11]

Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oExcel.DisplayAlerts = False
oExcel.AskToUpdateLinks = False
Set oWorkBook = oExcel.Workbooks.Open("filelocationhere")
oWorkBook.RefreshAll
oWorkBook.CalculateUntilAsyncQueriesDone << I get runtime error 438 - Object doesn't support this property or method
oWorkBook.Save
oWorkBook.Close
oExcel.Quit
Set oWorkBook = Nothing
Set oExcel = Nothing

 

[The_Doc_Man]

I have looked at the "official" documentation for workbooks and worksheets. There is no formal method of the name you used.

Workbook object (Excel)

Workbook object (Excel)

docs.microsoft.com

Worksheet.Calculate method (Excel)

Office VBA reference topic

docs.microsoft.com

I think what you want is an application method.

Application.CalculateUntilAsyncQueriesDone method (Excel)

Office VBA reference topic

docs.microsoft.com

If you named the wrong kind of object to activate that method, then what happens is your code in VBA "knows" the name but expects a different "thing" to use with that name. So you get the error because there IS such a method but NOT for workbooks.

 

[Number11]

I have looked at the "official" documentation for workbooks and worksheets. There is no formal method of the name you used.

Workbook object (Excel)

Workbook object (Excel)

docs.microsoft.com

Worksheet.Calculate method (Excel)

Office VBA reference topic

docs.microsoft.com

I think what you want is an application method.

Application.CalculateUntilAsyncQueriesDone method (Excel)

Office VBA reference topic

docs.microsoft.com

If you named the wrong kind of object to activate that method, then what happens is your code in VBA "knows" the name but expects a different "thing" to use with that name. So you get the error because there IS such a method but NOT for workbooks.

Ok thanks, just need to keep the excel worksheet open to allow the background Query to finish before saving and closing it

 

[bastanu]

Could try to use some DoEvents function like this:
Sub WaitFor(NumOfSeconds As Long) Dim SngSec as Long SngSec=Timer + NumOfSeconds Do while timer < sngsec DoEvents Loop 'to use it: Call WaitFor(10) 'wait for 10 seconds End sub

Cheers,
Vlad

 

[Number11]

Could try to use some DoEvents function like this:
Sub WaitFor(NumOfSeconds As Long) Dim SngSec as Long SngSec=Timer + NumOfSeconds Do while timer < sngsec DoEvents Loop 'to use it: Call WaitFor(10) 'wait for 10 seconds End sub

Cheers,
Vlad

Thanks and where do i put this code at the end of m code?

 

[The_Doc_Man]

While you can certainly do something like Vlad suggests, and he probably isn't wrong, you MIGHT try something simpler as a first shot. The keyword "Application" is automatically defined for VBA to mean "the application you are running right now." Therefore you do not need to try to define it. You might just change this line:

oWorkBook.CalculateUntilAsyncQueriesDone

to

Application.CalculateUntilAsyncQueriesDone

Then no other code is needed.

 

[bastanu]

If still needed it should go just before the oWorkBook.Save (and after the line DocMan mentions).

Cheers,