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

gublues wrote:

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

Oops. I forgot to include the link.

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

Dave Peterson wrote:

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

gublues wrote:

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

--

Dave Peterson

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" wrote:

Oops. I forgot to include the link.

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

Dave Peterson wrote:

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

gublues wrote:

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

--

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.




gublues wrote:

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" wrote:

Oops. I forgot to include the link.

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

Dave Peterson wrote:

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

gublues wrote:

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

--

Dave Peterson


--

Dave Peterson

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

"Dave Peterson" wrote:

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.




gublues wrote:

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" wrote:

Oops. I forgot to include the link.

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

Dave Peterson wrote:

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

gublues wrote:

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

--

Dave Peterson


--

Dave Peterson