Re: Sequential Numbering from Blank Document (k)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: J Laroche (j.laro_at_NsOySmPpAaMtico.ca)
Date: 01/12/05 

Date: Tue, 11 Jan 2005 19:22:50 -0500

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:
>
>> You lost me after pasting the code into the master workbook code.
>>
>> I don't know how to verify constants or comment out the rest. I don't
>> know what I'm looking at.
>>
>> How do I use this? I currently have a cell with 00000 in it. How do I
>> get this cell to use this script? So the next time the template is
>> opened the number is 00001?
>>
>> Sorry, but I have never done anything other than add or multiply numbers
>> in Excel. This is way beyond me.
>>
>> -Kirk
>>
>>
>> J Laroche wrote:
>>
>>> Bernard Rey wrote on 2005/01/10 17:55:
>>>
>>>
>>>> W. Kirk Lutz wrote:
>>>>
>>>>
>>>>> I am trying to create an Excel master document that when opened,
>>>>> automatically creates a new version of itself with a number that grows
>>>>> with each open.
>>>>>
>>>>> So if the number starts at 00000 and the master template document is
>>>>> opened as an Excel sheet opens with that number as 00001
>>>>>
>>>>> What do I put in the number field for this to happen?
>>>>
>>>>
>>>> Find the tip on JE's page:
>>>>
>>>> http://www.mcgimpsey.com/excel/udfs/sequentialnums.html
>>>>
>>>>
>>>> ---------------------------------------------------------
>>>> Please reply to the newsgroup, and within the same thread.
>>>> Merci de répondre au groupe, et dans l'enfilade.
>>>
>>>
>>>
>>>
>>> If modifying JE's excellent code (I'm still discovering something:
>>> SaveSetting) to adapt to your situation is out of your or your friends'
>>> reach, select and copy the code below, and paste it in your master
>>> workbook's code. To access that code, control-click on the workbook's
>>> title
>>> bar and select View Code. Remove the empty Workbook_Open procedure, if
>>> there
>>> is one, before pasting.
>>>
>>> Only a few things have to be verified after pasting: the constants
>>> InitialValue, increment, prefix and suffix, and the selection of saving
>>> method, near the bottom. Comment out the unwanted one. Method 1 will
>>> automatically save the new workbook in the same folder than the master
>>> file,
>>> while method 2 will ask each time where to save (the file can be
>>> renamed at
>>> this point; saving can be cancelled, however the sequential number is
>>> still
>>> increased). That's up to you, but do it before using the master file
>>> for the
>>> first time.
>>>
>>> JL
>>> Mac OS X 10.3.7, Office v.X 10.1.6
>>>
>>>

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

Relevant Pages

  • Re: In-Memory Dataset
    ... - User clicks Create New Invoice ... - Detail foreign key is temporarily set to -1 (same as master ... I have to post master dataset BEFORE posting detail ... which should UpdateBatch everything at ...
    (borland.public.delphi.database.ado)
  • Re: Unable to locate rows for updating
    ... >I have a master detail invoice form where the user selects the customer, ... ADO needs to be able to find the record in your database to delete. ... Your choices are adCriteriaKey, adCriteriaAllCols, ...
    (borland.public.delphi.database.ado)
  • Master/Detail Databinding issue
    ... I have an invoice entry form, which is a simple Master fields / Detail grid. ... which is bound using a BindingSource. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Sequential Numbering from Blank Document (k)
    ... Your master is a template? ... what you want cannot work with a template. ... Sub SequentialNumber() ... Dim existSN As Boolean ...
    (microsoft.public.mac.office.excel)
  • Re: Sequential Numbering from Blank Document (k)
    ... cell, not to a document. ... > When that master Excel template is opened by one of my coworkers(say the ... >> Sub SequentialNumber() ... >> Dim existSN As Boolean ...
    (microsoft.public.mac.office.excel)