Ignore workbook_open() when opening workbook next time

[Guest]

I have made a travel expense file template.
In the sub workbook_open() it creates a copy of the template with new
filename which add the day + month + year + hour+min+sec to make it
impossible to have two files with same name. However, I want people to be
able to open the new file without making another copy. People should be able
to edit the file without starting the macro below. How can I "delete" the
macro or disable the macro on the file copy?
I know the procedure of holding down shift key when opening, but is there
another way so people can open the file as they open every file?

Private Sub Workbook_Open()

Dim fNr As String
Dim Hr As Long
Dim Min As Long
Dim Sec As Long
Dim Dag As Long
Dim Mnd As Long
Dim Aar As Long

Dag = Day(Now())
Mnd = Month(Now())
Aar = Year(Now())
Hr = Hour(Now())
Min = Minute(Now())
Sec = Second(Now())
fNr = Trim(Str(Dag)) + Trim(Str(Mnd)) + Trim(Str(Aar)) + Trim(Str(Hr)) +
Trim(Str(Min)) + Trim(Str(Sec))
ChDir "o:\Travel"
ActiveWorkbook.SaveAs Filename:= _
"o:\Travel\Copy\Travel_" + fNr + ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
MsgBox ("You are now working on a copy of the travel expenses template.")

End Sub

 

[Dave Peterson]

Chip Pearson has some instructions on how to write code to remove code.



But maybe you could just check to see if the file's been saved:

Private Sub Workbook_Open()
if me.path <> "" then
exit sub
end if
'rest of code here
end sub

ps.

I'd use & to concatenate strings (like in the date/time stuff) instead of +'s.

But I'd think about doing:

fNr = format(now,"ddmmyyyy_hhmmss")

(I like leading 0's.)

 

[Dave Peterson]

Oops. I forgot to include the link.

Chip Pearson's site:
http://www.cpearson.com/excel/vbe.htm

 

[Guest]

Thanks for the last input.
the fNr= format... worked well and is exactly what I needed.

However, I haven't got the solution for how to make the new filename only
once. Your suggestion with if me.path did not work.

I tried myself this variant since the template has always the same filename,
but it exited the sub (as your suggestion)

Private Sub Workbook_Open()
If ActiveWorkbook.FullName = "o:\travel.xls" Then
'rest of code here
Else
Exit Sub
End If
End Sub

Private Sub Workbook_Open() is in ThisWorkbook and I did not know which
suggestion from cpearson.

*gublues

Oops. I forgot to include the link.

Chip Pearson's site:
http://www.cpearson.com/excel/vbe.htm

 

[Dave Peterson]

First, when you wrote template, I assumed you meant a real template
(travel.xlt--and saved as a template).

But if you're using an existing .xls file as a basis, you should be able to do:

If lcase(me.FullName) = "o:\travel.xls" Then

VBA's text comparisons are case sensitive unless you do something special.

And the me in me.fullname refers to the object holding the code--in this case
ThisWorkbook.

I think I'd change the order just a bit--I think it makes for easier reading:


If lcase(me.FullName) <> "o:\travel.xls" Then
exit sub
end if

'rest of code.

Thanks for the last input.
the fNr= format... worked well and is exactly what I needed.

However, I haven't got the solution for how to make the new filename only
once. Your suggestion with if me.path did not work.

I tried myself this variant since the template has always the same filename,
but it exited the sub (as your suggestion)

Private Sub Workbook_Open()
If ActiveWorkbook.FullName = "o:\travel.xls" Then
'rest of code here
Else
Exit Sub
End If
End Sub

Private Sub Workbook_Open() is in ThisWorkbook and I did not know which
suggestion from cpearson.

*gublues

 

[Guest]

Thanks Dave. It solved my problem.
Worhtwhile staying up being a midnight programmer in Europe.
'gublues

First, when you wrote template, I assumed you meant a real template
(travel.xlt--and saved as a template).

But if you're using an existing .xls file as a basis, you should be able to do:

If lcase(me.FullName) = "o:\travel.xls" Then

VBA's text comparisons are case sensitive unless you do something special.

And the me in me.fullname refers to the object holding the code--in this case
ThisWorkbook.

I think I'd change the order just a bit--I think it makes for easier reading:


If lcase(me.FullName) <> "o:\travel.xls" Then
exit sub
end if

'rest of code.