mplees
Active Member
- Joined
- Feb 6, 2006
- Messages
- 351
Hi,
I've been trying to find a way to auto number a workbook that is created from a template (.xlt). I've tried various bits of code, but nothing seems to work quite right. After browsing the forum archive, I first tried this code:
Private Sub Workbook_Open()
Dim nmbr As Long
Dim fNum As Integer
fNum = FreeFile
Open ThisWorkbook.Path & "\" & "lastvalue.txt" For Random As #fNum Len = Len(nmbr)
Get #fNum, 1, nmbr
nmbr = nmbr + 1
Put #fNum, 1, nmbr
Close #fNum
If Sheets("Start").Range("F14").Value = "" Then Sheets("Start").Range("F14").Value = "MSR" & nmbr + 50
End Sub
...which works fine with a workbook, but when I try it with the template, I cannot find the lastvalue.txt file anywhere, & the number does not always increment. I then tried a different approach:
Private Sub Workbook_Open()
Application.DisplayAlerts = False
Sheets("Start").Range("AA1").Value = Sheets("Start").Range("AA1").Value + 1
ThisWorkbook.SaveAs Filename:=Application.ActiveWorkbook.Path & "\" & "tst xxx machine study dd mmm yyyy.xlt"
Application.DisplayAlerts = True
End Sub
...which seems to work the first time I run it, except that when the .xlt file is saved, it doesn't overwrite the original template. & also, the workbook path is displayed as a null string. Again, this works fine if I use a workbook instead of a template.
Storing the incremental number within the template would be my preferred method, so can anyone point me in the right direction with this?
Regards,
Mark
I've been trying to find a way to auto number a workbook that is created from a template (.xlt). I've tried various bits of code, but nothing seems to work quite right. After browsing the forum archive, I first tried this code:
Private Sub Workbook_Open()
Dim nmbr As Long
Dim fNum As Integer
fNum = FreeFile
Open ThisWorkbook.Path & "\" & "lastvalue.txt" For Random As #fNum Len = Len(nmbr)
Get #fNum, 1, nmbr
nmbr = nmbr + 1
Put #fNum, 1, nmbr
Close #fNum
If Sheets("Start").Range("F14").Value = "" Then Sheets("Start").Range("F14").Value = "MSR" & nmbr + 50
End Sub
...which works fine with a workbook, but when I try it with the template, I cannot find the lastvalue.txt file anywhere, & the number does not always increment. I then tried a different approach:
Private Sub Workbook_Open()
Application.DisplayAlerts = False
Sheets("Start").Range("AA1").Value = Sheets("Start").Range("AA1").Value + 1
ThisWorkbook.SaveAs Filename:=Application.ActiveWorkbook.Path & "\" & "tst xxx machine study dd mmm yyyy.xlt"
Application.DisplayAlerts = True
End Sub
...which seems to work the first time I run it, except that when the .xlt file is saved, it doesn't overwrite the original template. & also, the workbook path is displayed as a null string. Again, this works fine if I use a workbook instead of a template.
Storing the incremental number within the template would be my preferred method, so can anyone point me in the right direction with this?
Regards,
Mark
rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New> <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Workbook_Open() 
