RefreshAll inconsistancies (1 Viewer)

[mdjks]

I am trying to use the following code to update seven Excel workbooks. At
this point (second file to refresh) I get Runtime error 1004 SaveAs method of workbook class failed.

xlWb.SaveAs ("P:\FileLocation\Speciality All Regions Forecast Template.xlsx")

I continue the code with F5 and the next error is Runtime error 1004 Cannot access "fourth file" again the code continues to run with F5.

I have run this numerous times and am not getting the errors for the same files each time. Just a combination of 1004 with Can't Access file or SaveAs method failed.

When I check the files, they show last updated at the correct time but the data may or may not have actually refreshed. Any help achieving consistent refreshed results would be appreciated.

Using Access 2010, W7

Dim xlApp As Object
Dim xlWb As Object

Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
Set xlWb = xlApp.Workbooks.Add("P:\FileLocation\Cariflex Forecast Template.xlsx")

xlWb.RefreshAll
xlWb.RefreshAll

xlWb.SaveAs ("P:\FileLocation\Cariflex Forecast Template.xlsx")
xlWb.Close SaveChanges:=True
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing

 

[Ranman256]

you cant saveAs if the target file is open

 

[mdjks]

I tried removing it but I ended up with dialogue boxes for save. I did try setting the App to Visible but the RefreshAll Command is still not working 100% of the time.

 

[mdjks]

Any additional thoughts would be appreciated. I've added DoEvents and more saves and split it into separate buttons. The RefreshAll is still not consistently running.

Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
Set xlWb = xlApp.Workbooks.Add("P:\Business Analyst Shared Folder\Business Analysis Tools\Forecast & EG\Flash Templates\Nexar Forecast Template.xlsx")
xlApp.Visible = True

    xlWb.RefreshAll
        DoEvents
xlWb.Save
    xlWb.RefreshAll

xlWb.Save
xlWb.SaveAs ("P:\Business Analyst Shared Folder\Business Analysis Tools\Forecast & EG\Flash Templates\Nexar Forecast Template.xlsx")
xlWb.Close SaveChanges:=True
'xlApp.DisplayAlerts = True
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing

 

[mdjks]

Finally found something to work, see if statements in red

Dim xlApp As Object
Dim xlWb As Object

Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
Set xlWb = xlApp.Workbooks.Add("P:\Business Analyst Shared Folder\Business Analysis Tools\Forecast & EG\Flash Templates\Nexar Forecast Template.xlsx")
xlApp.Visible = True

    xlWb.RefreshAll
[COLOR="Red"]If Not xlApp.CalculateUntilAsyncQueriesDone = xlDone Then
    DoEvents
End If
    
    xlApp.Calculate
If Not xlApp.CalculationState = xlDone Then
    DoEvents
End If[/COLOR]
        DoEvents
xlWb.Save
    xlWb.RefreshAll
        DoEvents


xlWb.Save
xlWb.SaveAs ("P:\Business Analyst Shared Folder\Business Analysis Tools\Forecast & EG\Flash Templates\Nexar Forecast Template.xlsx")
xlWb.Close SaveChanges:=True
'xlApp.DisplayAlerts = True
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing