Ignore workbook_open() when opening workbook next time

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.)
 
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

Dave Peterson said:
Oops. I forgot to include the link.

Chip Pearson's site:
http://www.cpearson.com/excel/vbe.htm
Click to expand...
 
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
Click to expand...
 
Thanks Dave. It solved my problem.
Worhtwhile staying up being a midnight programmer in Europe.
'gublues

Dave Peterson said:
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.
Click to expand...
 
Back
Top