I can't update external data sources using vba
 

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 can't update external data sources using vba
 

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

"GrahamBaines" wrote in message
...
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 can't update external data sources using vba
 

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

I can't update external data sources using vba
 

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