I can't update external data sources using vba

[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

 

[Don Guillett]

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

 

[john]

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

 

[GrahamBaines]

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