OK, there is one thing that needs to be clarified here.
1) I understood that from a master file (as an example, Invoice Master), you
wanted to create subsequent files for each invoice you create. Each of these
files is called for example Invoice 1, Invoice 2, etc. Is that correct?
2) Or do you want that a master file only keeps in a cell a number going up
each time you open that file? Then you take care of saving the file yourself
under a new name?
3) Or do you want a combination of both, i.e. a file automatically bearing a
numbered name plus in one of its cells its own sequential number (like an
invoice would do)?
Now if the case that interests you is 1, then I have an updated version of
the code posted yesterday, because there was a small flaw concerning the way
the sequential number was kept in the properties (I know this is above your
head, but others may be interested). See the new code at the bottom of the
message. It entirely replaces what I posted yesterday.
If it's case 2, then what you got on MacFixIt was almost right. I assume
that you do a Save As, thus losing the value you want to keep in the master
file. See in your previous post how to easily fix it.
If it's case 3, then I'd have to make a few easy changes to my code.
Now, the term "Comment out" means to tell the compiler to ignore certain
lines, to treat them like comments instead of computer instructions. The
compiler ignores lines (or parts of lines) that begin with an apostrophe
('). The comments appear green in the code.
The constants are the lines that begin with the word Const. Look for them
near the top of the code. Can you at least make sense of a line that says:
Const InitialValue = 1 ' first sequential number to use
If you wanted to save your files under the names Invoice 1, Invoice 2 as
mentioned above, then you'd change the following line:
Const prefix = "Invoice " ' text to put before the sequential number
Don't be intimidated by Visual Basic because you've never studied it. Even
though you may not understand everything, you should have a basic
understanding of it - then - end if structures. Read the comments, it helps
too.
JL
Mac OS X 10.3.7, Office v.X 10.1.6
W. Kirk Lutz wrote on 2005/01/11 10:59:
I also posted this on MacFixit.com and someone sent this code:
Private Sub Workbook_Open()
Sheet1.[A1] = Sheet1.[A1].Value + 1
End Sub
I changed it to:
Private Sub Workbook_Open()
Sheet1.[E2] = Sheet1.[E2].Value + 1
ThisWorkbook.Save
End Sub
but it doesn't work quite right. It does increase the number in E2 by 1
(from 4401 to 4402) but it doesn't save over the template so the next
time the number is 4403.
Thoughts?
-Kirk
W. Kirk Lutz wrote:
Option Explicit
Private Sub Workbook_Open()
SequentialNumber
End Sub
Sub SequentialNumber()
' JL 2005-01-10
' update 1, 2005-01-11: sequential number now stays correct in Properties
' On each opening of a master file, create a new file named
' with a sequential number
Const InitialValue = 1 ' first sequential number to use
Const increment = 1 ' increment from one sequential number to the next
Const prefix = "" ' text to put before the sequential number
Const suffix = "" ' text to put after the sequential number
Dim existSN As Boolean
Dim existMF As Boolean
Dim master As Boolean
Dim cdp As DocumentProperty
Dim SeqNumber As Long
Dim FSName As Variant
existSN = False
existMF = False
master = False
With ThisWorkbook
For Each cdp In .CustomDocumentProperties
If cdp.Name = "Sequential Number" Then
existSN = True
SeqNumber = cdp + increment
ElseIf cdp.Name = "Master File" Then
existMF = True
master = cdp
End If
Next
' Creation of the custom properties the first time
' the master file is open
' Each property is tested separately in case somebody
' removed a property
If Not existSN Then
.CustomDocumentProperties.Add Name:="Sequential Number", _
LinkToContent:=False, Type:=msoPropertyTypeNumber, _
Value:=InitialValue
SeqNumber = InitialValue
End If
If Not existMF Then
.CustomDocumentProperties.Add Name:="Master File", _
LinkToContent:=False, Type:=msoPropertyTypeBoolean, _
Value:=True
master = True
End If
If master Then ' save a new file only if the master file is open
.CustomDocumentProperties.Item("Sequential Number") = SeqNumber
.Save ' save master file containing the new sequential number
FSName = prefix & Format(SeqNumber) & suffix
.CustomDocumentProperties.Item("Master File") = False
' Two methods for saving the new file.
' Put an apostrophe in front of the unnecessary method's lines
' before use.
' Method 1: new file in same directory than master file
.SaveAs FileName:=.Path & Application.PathSeparator & FSName, _
AddToMru:=True
' end of method 1
' Method 2: Save path requested from user
FSName = Application.GetSaveAsFilename(InitialFilename:=FSName)
If FSName <> False Then
.SaveAs FileName:=FSName, AddToMru:=True
Else
.Saved = True
' to avoid a dialog box if immediately closed
End If
' end of method 2
End If
End With
End Sub
