Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Batch number (Excel 2000)

    I need ideas on adding a batch number to a file to be saved. The batch number should be from 1 to 99 999 and sequencial. Where will I be saving the last used batch number in order to add 1 to it. The batches will be run on an infrequent schedule during the month.

    Thanks for the help

    Mario

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Batch number (Excel 2000)

    I guess having a simple text file in the same directory that contains the files would be simplest. Here is code to open, increment and close:
    Dim lCounter as Long
    Open "CataCounter.txt" For Input As #1
    Input #1, lCounter
    lCounter = lCounter + 1
    Close #1
    DoEvents
    Open "CataCounter.txt" For Output As #1
    Write #1, lCounter
    Close #1
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Batch number (Excel 2000)

    Thanks it works great

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Reading, Berkshire, England
    Posts
    199
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Batch number (Excel 2000)

    Hope you don't think I'm snooping, but am very intrigued with this thread. I have written the code and created a .txt file in my Excel directory, run as a Macro from an Excel Worksheet. It works. Every time I rum the Macro it increments the txt file by 1. I have to open the text file to view this number. How do I attach this number, or insert it, into the spreadsheet.
    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Batch number (Excel 2000)

    The code below will get the number from the file, increment it, write it back, and then put the incremented value in cell A1 on worksheet Sheet1:

    <pre>Public Sub GetCounter()
    Dim lCounter As Long
    Dim iFileNum As Integer
    iFileNum = FreeFile()
    Open "CataCounter.txt" For Input As #iFileNum
    Input #iFileNum, lCounter
    Close #iFileNum
    lCounter = lCounter + 1
    Open "CataCounter.txt" For Output As #iFileNum
    Write #iFileNum, lCounter
    Close #iFileNum
    Worksheets("Sheet1").Range("A1").Value = lCounter
    End Sub
    </pre>

    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Reading, Berkshire, England
    Posts
    199
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Batch number (Excel 2000)

    Yippee! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    I love it when a plan comes together. Many thanks.

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Batch number (Excel 2000)

    Another thought on this although the question seems fully covered, you could also append a number to the end of the actual file itself, such as something like the following (with Book1, in C:My Documents):

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim bNum As Integer
    bNum = ThisWorkbook.Sheets(1).Range("a1").Value + 1
    ThisWorkbook.Sheets(1).Range("a1") = bNum
    ThisWorkbook.SaveAs ("C:My DocumentsBook1" & "Batch " & bNum)
    End Sub

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Batch number (Excel 2000)

    That works as long as you don't try to save a workbook that is not the most recent.
    Legare Coleman

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Batch number (Excel 2000)

    True, just an idea I chopped in there without fully coding it further. Although, you could check the filename, say if it is not the master book, then just save without changing the value in A and without changing the filename (without adding the 1 to it). I seem to do this regularly with some other books I have, do one action if the workbook name is so-and-so, do another if it is something else.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •