Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing External data from various sources | Excel Discussion (Misc queries) | |||
external data from multiple sources | Excel Programming | |||
Why can't I edit my external data sources? | Links and Linking in Excel | |||
Why can't I edit my external data sources? | Links and Linking in Excel | |||
External data sources in Excell... | Excel Programming |