Sequential numbering and collecting data in excel dadabase

22868

New Member
Joined
Apr 4, 2002
Messages
13
Hi all,

This is what I am trying to create (failed hopelessly so far :confused: ):

A template Purchase order form, available for multiple users from a network containing a unique sequential number and variable input data.

something like this:

Unique Sequential nr|data 1|data2|data 3| .... |data n

The sheets need to be saved as a document and, here is the tricky bit, the data must be transferred to data base.

The template available in excel (purchase order.xlt) does not meet my requirements and I am unable to change it :cry:

Any help is highly appreciated!!!

Thanks in advance!

Pieter
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

pfarmer

Well-known Member
Joined
Jul 6, 2005
Messages
550
Re: Sequential numbering and collecting data in excel dadaba

22868 said:
Hi all,

This is what I am trying to create (failed hopelessly so far :confused: ):

A template Purchase order form, available for multiple users from a network containing a unique sequential number and variable input data.

something like this:

Unique Sequential nr|data 1|data2|data 3| .... |data n

The sheets need to be saved as a document and, here is the tricky bit, the data must be transferred to data base.

The template available in excel (purchase order.xlt) does not meet my requirements and I am unable to change it :cry:

Any help is highly appreciated!!!

Thanks in advance!

Pieter

One method is simply using now() as a serial number. You could limit it to whatever precision desired.

Using VBA:

Below is a formula that I use that creates a new file everyday based on the date. Within the day the sequence starts at one. The start day is picked up from a datepicker on a userform which allows the user to select dates in advance, but you could simply sub in whatever date method you want for this. If you simply want to use a continuous sequence you could bypass my use of the date and just go with the second solution after this one.

It creates a file called new.txt




Code:
Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long
        
        Const sDEFAULT_Path As String = "C:\test\Number\"
        Dim Today
        Dim dayofyear
        Dim StartDate
        Dim StartYear
        Today = Now
        
        StartDate = UserForm1.DTPicker1.Value
        On Error GoTo GeneralError
        If StartDate = "Enter Date" Then GoTo GeneralError
        
        dayofyear = DatePart("y", StartDate)
        StartYear = Year(StartDate)
        Dim nFileNumber As Long

        nFileNumber = FreeFile
        If sFileName = "" Then sFileName = StartYear & dayofyear
                
        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 GeneralError
        Open sFileName For Output As nFileNumber
        On Error GoTo 0
        Print #nFileNumber, nSeqNumber
        Close nFileNumber
        NextSeqNumber = nSeqNumber
        Exit Function
GeneralError:
        NextSeqNumber = -1&
    End Function


Code:
Public Function NewSeqNumber(Optional NewsFileName As String, Optional NewnSeqNumber As Long = -1) As Long
        
        Const sDEFAULT_Path As String = "C:\Test\Number\"
        
        On Error GoTo GeneralError
        
        Dim NewnFileNumber As Long

        NewnFileNumber = FreeFile
        If NewsFileName = "" Then NewsFileName = "New.Txt"
                
        If InStr(NewsFileName, Application.PathSeparator) = 0 Then _
            NewsFileName = sDEFAULT_Path & Application.PathSeparator & NewsFileName
        If NewnSeqNumber = -1& Then
            If Dir(NewsFileName) <> "" Then
                Open NewsFileName For Input As NewnFileNumber
                Input #NewnFileNumber, NewnSeqNumber
                NewnSeqNumber = NewnSeqNumber + 1&
                Close NewnFileNumber
            Else
                NewnSeqNumber = 1&
            End If
        End If
        On Error GoTo GeneralError
        Open NewsFileName For Output As NewnFileNumber
        On Error GoTo 0
        Print #NewnFileNumber, NewnSeqNumber
        Close NewnFileNumber
        NewSeqNumber = NewnSeqNumber
        Exit Function
GeneralError:
        NewSeqNumber = -1&
    End Function

Perry
 

Watch MrExcel Video

Forum statistics

Threads
1,118,915
Messages
5,575,018
Members
412,634
Latest member
Sumanmathew
Top