Problems with RefreshAll method

  • Thread starter Thread starter GrahamBaines
  • Start date Start date
G

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
 
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
Click to expand...

Hi Graham,

try to substitute the RefreshAll by Calculate.

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

Graham
 
Back
Top