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

perhaps you need to suspend activity while refreshing

Application.Workbooks.Open ("X:\ Stats 2007.xls")
application.enableevents=false
ActiveWorkbook.RefreshAll
application.enableevents=true
ActiveWorkbook.Save

ActiveWorkbook.Close

End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"GrahamBaines" <(E-Mail Removed)> wrote in message
news:7339B943-E7A9-4B57-83DD-(E-Mail Removed)...
> 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
>

following seemed to work ok for me:

Private Sub CommandButton1_Click()
Dim FName As Workbook
With Application
.EnableEvents = False
Set FName = .Workbooks.Open("X:\Stats 2007.xls", False)
mybook = FName.Name
With FName
.RefreshAll
.Close True 'save file
End With
.EnableEvents = True
End With
msg = MsgBox(mybook & " Refreshed")
End Sub
--
JB


"GrahamBaines" wrote:

> 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
>

Many thanks for the assistance, I've learned some useful new commands, both
fragments of code run without error but don't update the external data.

Cheers

G