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

Help with SaveAs Macro on Auto_Open
 

Hi

If I understand this right; if it's not the template at the template
location running the do nothing ? If so try something like

If ThisWorkbook.Fullname < "F:\Whatever\Folder\EditThis\Template.xlt" Then
Exit Sub

HTH. Best wishes Harald

"Daen" skrev i melding
...
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

Help with SaveAs Macro on Auto_Open
 

Harald, I can't thank you enough for all the help you've given me lately.
Thats exactly what I need, but the only problem is the Template renames the
Log to Daily Log1 once it is doubleclicked. I think I can work around that
though, I'll try it and let you know how it goes.

Brilliant! Cheers!

"Harald Staff" wrote:

Hi

If I understand this right; if it's not the template at the template
location running the do nothing ? If so try something like

If ThisWorkbook.Fullname < "F:\Whatever\Folder\EditThis\Template.xlt" Then
Exit Sub

HTH. Best wishes Harald

"Daen" skrev i melding
...
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

Help with SaveAs Macro on Auto_Open
 

"Daen" skrev i melding
...
Harald, I can't thank you enough for all the help you've given me
lately.

You did anyway. Cheers!
Best wishes Harald