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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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,122,529
Messages
5,596,694
Members
414,088
Latest member
rodriboraun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top