Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Minimising the Ribbon in an Auto_Open macro | Excel Discussion (Misc queries) | |||
Auto_Open Macro | Excel Discussion (Misc queries) | |||
Can I make an Auto_open macro conditional? | Excel Discussion (Misc queries) | |||
Force read-only in auto_open macro | Excel Discussion (Misc queries) | |||
Auto_Open vs. macro | Excel Programming |