Problems with RefreshAll method

[GrahamBaines]

Hi,

I've been writing a bit of code to automate the opening, refreshing of
external data, saving and closing of spreadsheets. I'm having problems which
I think are associated with the speed of the refesh. The workbook opens fine
but when I run the code I get a warning which states:- "This action will
cancel a pending data refresh command. Cancel?".

The code I'm running is:-

Private Sub CommandButton1_Click()

Application.Workbooks.Open ("X:\ Stats 2007.xls")

ActiveWorkbook.RefreshAll

ActiveWorkbook.Save

ActiveWorkbook.Close

End Sub

I tried disabling the messages using:-

"Application.DisplayAlerts = False"

That stopped the message and the workbook opened and closed fine, but the
refresh didn't save. I tried putting a wait statement into the code to pause
the 'save' command until the refresh has had time to complete, but that had
no effect. I inserted an 'On Error' statement and forced the code to loop
back to the 'save' command until it had completed, but again to no avail.

I find it inconceivable that it isn't possible to open, refresh, save and
close a workbook from another workbook using vba as it seems to me to be a
sensible and logical thing to want to do, but at present it's defeats me, any
ideas? All help gratefully accepted.................

Many Thanks

Graham

 

[Udo]

Hi,

I've been writing a bit of code to automate the opening, refreshing of
external data, saving and closing of spreadsheets. I'm having problems which
I think are associated with the speed of the refesh. The workbook opens fine
but when I run the code I get a warning which states:- "This action will
cancel a pending data refresh command. Cancel?".

The code I'm running is:-

Private Sub CommandButton1_Click()

Application.Workbooks.Open ("X:\ Stats 2007.xls")

ActiveWorkbook.RefreshAll

ActiveWorkbook.Save

ActiveWorkbook.Close

End Sub

I tried disabling the messages using:-

"Application.DisplayAlerts = False"

That stopped the message and the workbook opened and closed fine, but the
refresh didn't save. I tried putting a wait statement into the code to pause
the 'save' command until the refresh has had time to complete, but that had
no effect. I inserted an 'On Error' statement and forced the code to loop
back to the 'save' command until it had completed, but again to no avail.

I find it inconceivable that it isn't possible to open, refresh, save and
close a workbook from another workbook using vba as it seems to me to be a
sensible and logical thing to want to do, but at present it's defeats me, any
ideas? All help gratefully accepted.................

Many Thanks

Graham

Hi Graham,

try to substitute the RefreshAll by Calculate.

Good luck
Udo

 

[GrahamBaines]

Thanks Udo, tried various configs of the calculate command, it stops the
error but unfortunately doesn't refresh the external data.

Graham