Excel "Open_Workbook" Macro-Refresh Data and Save

[Boxcar74]

Hi Everybody!!!

I have an Issue.

I have an Excel file that queries an Access db. I’m trying to have it so I don’t have to keep updating it manually everyday and save it to a network drive with the file name coming from a cell reference and the current date.

What I’ve done have the Excel file open via a Batch file and on “Workbook_Open” I run a few macro to refresh the data then remake and save it to the network drive.

My problem is the code will not wait until the refresh is completed. I belive it is trying to start to Save As… action.

The error message says: “This action will cancel a pending Refresh Data command. Continue?"

A few notes on the code: Keep in mind; I’m very limited as far as VBA knowledge
1. I’m using an If statement because I only want to run if is this specific file. So if some one opens the saved version it does not update.
2. I used the With…. End With. Because running this on it own (no batch file) it will wait until the refresh is done before saving. I have no Idea why, but it does.
3. I have one line of code in a module and the rest is in the “Workbook_Open” code. I read somewhere that this is the best way to save to a network drive.


In Module
Declare Function SetCurrentDirectory Lib "kernel32" Alias "SetCurrentDirectoryA" (ByVal lpPathName As String) As Long

In Workbook
Private Sub Workbook_Open()
If ActiveWorkbook.Name = "002 Printer Monitoring.xls" Then

With Worksheets(1).QueryTables(1)
ActiveWorkbook.RefreshAll
End With

Dim CurrentPath As String

''Store the current path
CurrentPath = CurDir

''Change the path to the one I want
SetCurrentDirectory "\\server\boxcar\
Dim newFile As String, fName As String

''Saves filename as value of A10 plus the current date.

fName = Range("A10").Value
newFile = fName & " " & Format$(Date, "mmddyyyy")

ThisWorkbook.SaveAs Filename:=newFile

''Change the path back
SetCurrentDirectory CurrentPath

ActiveWorkbook.Close
Application.Quit

End If
End Sub
------------------------------------

I know has to be a poor way to do this process. I’ve been putting the code together piece by piece and trying all different ways to get it to work. I don’t think I it is very well formated.

So any suggestions are welcome!! PLEASE!!

But my real questions is why am I getting the error message?

If it is because of the change directory and save commands.
How do I get it to wait before moving on to the next step?
(I tried the “Application.Wait” code but if pauses the refresh)

Thanks for any advice and feedback!!!

-- Boxcar