Help with SaveAs Macro on Auto_Open
Let me give you a bit of the scenario. I have a shortcut to a template that
is on a network drive so that everyone brings up a nice blank "Daily Log".
When it is opened it saves itself as "Daily Log for <date" in a directory
called Daily Log on each individual persons personal drive on the network.
This works great until one thing occurs... they try to open the daily log
again and it sees there's already a file named "Daily Log for <that day".
(The code I have for the Auto_Open is below)
Is there a way to either, have the macro not function after its been
saved as a different file from the Template? or open the existing file
instead of trying to overwrite it?
Sub Auto_Open()
Dim myPath As String
Dim myFileName As String
On Error Resume Next
MkDir ThisWorkbook.Path & "\" & "Daily Log"
myPath = ThisWorkbook.Path & "\" & "Daily Log"
myFileName = myPath & "\" & "Daily Log for " & Format(Date, "mm_dd_yy")
& ".xls"
ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
End Sub
|