Using a text file to hold sequential numbers

**spanker** · 10-14-2009, 07:17 PM

Hi All
i found a macro that im trying to get working from the site below to generate a number everytime my template is opened.

ive put the code in the thisworkbook module as directed and changed the relevant path but it doesnt do anything i think im missing something but cant see what.
any help would be appreciated
this is the code ive placed in thisworkbook module of my template

Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long
        Const sDEFAULT_PATH As String = "<your path here>"
        Const sDEFAULT_FNAME As String = "defaultseq.txt"
        Dim nFileNumber As Long
        
        nFileNumber = FreeFile
        If sFileName = "" Then sFileName = sDEFAULT_FNAME
        If InStr(sFileName, Application.PathSeparator) = 0 Then _
            sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName
        If nSeqNumber = -1& Then
            If Dir(sFileName) <> "" Then
                Open sFileName For Input As nFileNumber
                Input #nFileNumber, nSeqNumber
                nSeqNumber = nSeqNumber + 1&
                Close nFileNumber
            Else
                nSeqNumber = 1&
            End If
        End If
        On Error GoTo PathError
        Open sFileName For Output As nFileNumber
        On Error GoTo 0
        Print #nFileNumber, nSeqNumber
        Close nFileNumber
        NextSeqNumber = nSeqNumber
        Exit Function
    PathError:
        NextSeqNumber = -1&
    End Function

ive also placed the code below in in a macro

Public Sub Workbook_Open()
        ThisWorkbook.Sheets(1).Range("B2").Value = NextSeqNumber
    End Sub

the original code came from

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

thanks

**Leith Ross** · 10-14-2009, 10:26 PM

Hello spanker,

Is an error being generated? What was the cell value before the workbook was opened and afterwards?

**spanker** · 10-15-2009, 12:06 PM

Hi Leith

no theres no error generated
the cell value was 000000019 which was entered manually ive also tried it with the cell value blank

have i placed the code in the corredct places?

**royUK** · 10-15-2009, 12:13 PM

Bit overcomplicated,are you using a *.xlt workbook?

**spanker** · 10-15-2009, 12:20 PM

Hi Royuk

Bit overcomplicated,are you using a *.xlt workbook?

yes its an axcel template file .xlt

to be honest im pretty new to this so i dont know how it does it (or doesnt in this case)

is there an easier way?

thanks

**royUK** · 10-15-2009, 12:38 PM

You could store it in the Registry.

If it wasn't a *.xlt I would store it in a Named Range.

The code that yo posted works fine, have you put the code in the correct place & made the required changes?

Attach your workbook so that we can check

**spanker** · 10-15-2009, 02:33 PM

i did get the registry version working but, i need to put the workbook on another machine thats why i wanted to use the text file version,

the template is going to be on a shared drive so every time its opened it creates a new file and saves it.

**spanker** · 10-15-2009, 02:59 PM

ive attached the file
thanks

**Leith Ross** · 10-15-2009, 03:03 PM

Hello spanker,

The problem is the Workbook_Open event. Worksheet formula and VBA functions are not calculated (run) until after the workbook has opened. Instead you can use the Auto_Open() macro method to execute the code. I wrote this macro to save the serial number in a text file and run the macro using the Auto_Open() method. Copy all this code into a separate VBA module and change the default file path and file name to what you want to use. This macro works with Excel 2000 - 2007.

'Written: October 15, 2009
'Author:  Leith Ross
'Sumamry: Creates a text file using the file path and name specfied or the defaults
'         if the file doesn't exist. This file holds a serial number starting at one
'         and is incremented and saved each time the function is called.


Function GetSerialNumber(Optional FilePath As String, Optional FileName As String) As String

  Dim DefaultName As String
  Dim DefaultPath As String
  Dim FSO As Object
  Dim SeqNum As Variant
  Dim TxtFile As Object
  
    DefaultPath = "C:\Documents and Settings\Admin.ADMINS\My Documents"
    DefaultName = "Invoice"
    
      FilePath = IIf(FilePath = "", DefaultPath, FilePath)
      FilePath = IIf(Right(FilePath, 1) <> "\", FilePath & "\", FilePath)
    
      FileName = IIf(FileName = "", DefaultName, FileName)
      FileName = FilePath & IIf(InStr(1, FileName, ".") = 0, FileName & ".txt", FileName)
    
      Set FSO = CreateObject("Scripting.FileSystemObject")
    
       'Open the file for Reading and Create the file if it doesn't exists
        Set TxtFile = FSO.OpenTextFile(FileName, 1, True, 0)
      
       'Read the serial number
        If Not TxtFile.AtEndOfStream Then SeqNum = TxtFile.ReadLine
        TxtFile.Close
      
       'Update the serial number
        Set TxtFile = FSO.OpenTextFile(FileName, 2, False, 0)
        SeqNum = Format(IIf(SeqNum = "", "1", Val(SeqNum) + 1), "0000")
        TxtFile.WriteLine SeqNum
      
   TxtFile.Close
   GetSerialNumber = SeqNum
      
   Set FSO = Nothing
   Set TxtFile = Nothing
   
End Function

Sub Auto_Open()
  Sheets(1).Range("B2") = GetSerialNumber()
End Sub

**spanker** · 10-15-2009, 03:58 PM

Thanks for all the help guys
leith the above code did the trick

Excellent!

Using a text file to hold sequential numbers

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Using a text file to hold sequential numbers

Re: Using a text file to hold sequential numbers

Re: Using a text file to hold sequential numbers

Re: Using a text file to hold sequential numbers

Re: Using a text file to hold sequential numbers

Re: Using a text file to hold sequential numbers

Re: Using a text file to hold sequential numbers

Re: Using a text file to hold sequential numbers

Re: Using a text file to hold sequential numbers

Re: Using a text file to hold sequential numbers

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions