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, but at present it's
defeats me, any ideas? All help gratefully accepted.................
Many Thanks
Graham
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, but at present it's
defeats me, any ideas? All help gratefully accepted.................
Many Thanks
Graham