Help with automating large sequential series.

[cbutts]

Hi everyone,
I'm new here. I'm also relatively new to Excel but part of my daily life these days is creating sequentially numbered .csv files (using Fill>Series) to be used for printing variable data barcode labels.
As the quantities get larger, frankly I'm having issues keeping up. I'm looking for a solution in the form of a macro or User form that can help ease the burden and I would appreciate any help you guy and girls could give me.

In looking through this forum and searching different criteria I found this piece of code from "L. Howard" in 2017

Option Explicit

Sub FillASeries()


Dim MyCol As Long, myRowS As Long, MyRowE As Long, myNum As Long


MyCol = Application.InputBox("Enter the column number", "MyColum No.", , , , , , 1)
myRowS = Application.InputBox("Enter start ROW.", "The Starting Row", , , , , , 1)
MyRowE = Application.InputBox("Enter end ROW.", "The Ending Row", , , , , , 1)
myNum = Application.InputBox("Enter start NUM.", "First No. of the Series", , , , , , 1)

Cells(myRowS, MyCol) = myNum

Cells(myRowS, MyCol).AutoFill Destination:=Range(Cells(myRowS, MyCol), Cells(MyRowE, MyCol)), Type:=xlFillSeries

End Sub

I works well but I need a little more functionality and things like Start ROW and Start column will always be the same for me.
(Start Column/Row will always be A2 with the header "number" always being A1)

So, what I'm wanting to do is have the ability to give a starting number and an ending number. (for instance, starting number 1,000,000..... ending number 1,999,999 for a total of 1 million unique numbers)
AND set the parameters to have those numbers broken down into smaller chunks. Meaning.... Say I need 1 million labels, but I want my given numbers to be broken into ten 100,000 record workbooks named "A-May 2022" through "whatever-May 2022" (I know A through Z limits me to 26 files but I seriously doubt I'll exceed that in one job)

The physical size of the labels dictates how the files will need to be broken down. For instance, the next job may be 400,000 labels that I want to break down into 5 files with 80,000 records each.

I hope that makes sense and some of you people who are way smarter than me can give me a hand.

 

[John_w]

You don't really need to AutoFill a series in sheet cells to do this. This generates each series in a dynamic array and saves that in csv files in the same folder as the workbook.

Public Sub Create_Series_CSVs()

    Dim startNumber As Long, endNumber As Long
    Dim chunkSize As Long
    Dim series() As String
    Dim i As Long, n As Long
    Dim fileSequenceNumber As Long
    Dim folder As String, csvFile As String
    Dim fileNum As Integer
    
    startNumber = InputBox("Enter the start number")
    endNumber = InputBox("Enter the end number")
    chunkSize = InputBox("Enter the chunk size")
        
    folder = ThisWorkbook.Path & "\"
    If Right(folder, 1) <> "\" Then folder = folder & "\"
    
    fileSequenceNumber = 0
    For i = startNumber To endNumber Step chunkSize
        ReDim series(0 To Application.WorksheetFunction.Min(chunkSize, endNumber - i + 1) - 1)
        For n = 0 To Application.WorksheetFunction.Min(chunkSize, endNumber - i + 1) - 1
            series(n) = i + n
        Next
        csvFile = folder & Chr(Asc("A") + fileSequenceNumber) & Format(Date, "-Mmmm yyyy") & ".csv"
        fileNum = FreeFile
        Open csvFile For Output As fileNum
        Print #fileNum, "number"
        Print #fileNum, Join(series, vbCrLf)
        Close #fileNum
        fileSequenceNumber = fileSequenceNumber + 1
    Next
    
End Sub

 

[cbutts]

Hi John_w,
Thanks for your reply. When I run the code I'm getting a "Runtime error '52': Bad file name or number" and debug show the attached screenshot.

 

[John_w]

Sorry, I've just noticed you're using MacOS. I don't know anything about VBA on Mac, but see if this modified macro fixes the error. The only change is Application.PathSeparator instead of "\".

Public Sub Create_Series_Csvs()

    Dim startNumber As Long, endNumber As Long
    Dim chunkSize As Long
    Dim i As Long, n As Long
    Dim series() As String
    Dim fileSequenceNumber As Long
    Dim folder As String, csvFile As String
    Dim fileNum As Integer
   
    startNumber = InputBox("Enter the start number")
    endNumber = InputBox("Enter the end number")
    chunkSize = InputBox("Enter the chunk size")
       
    folder = ThisWorkbook.Path & Application.PathSeparator
    If Right(folder, 1) <> Application.PathSeparator Then folder = folder & Application.PathSeparator
   
    fileSequenceNumber = 0
    For i = startNumber To endNumber Step chunkSize
        ReDim series(0 To Application.WorksheetFunction.Min(chunkSize, endNumber - i + 1) - 1)
        For n = 0 To Application.WorksheetFunction.Min(chunkSize, endNumber - i + 1) - 1
            series(n) = i + n
        Next
        csvFile = folder & Chr(Asc("A") + fileSequenceNumber) & Format(Date, "-Mmmm yyyy") & ".csv"
        fileNum = FreeFile
        Open csvFile For Output As #fileNum
        Print #fileNum, "number"
        Print #fileNum, Join(series, vbCrLf)
        Close #fileNum
        fileSequenceNumber = fileSequenceNumber + 1
    Next
   
End Sub

 

[cbutts]

Thank you so much! It works perfectly and will take the possibility of human error out of the picture. Seriously, I can't thank you enough.

 

[cbutts]

Sorry, I've just noticed you're using MacOS. I don't know anything about VBA on Mac, but see if this modified macro fixes the error. The only change is Application.PathSeparator instead of "\".

Public Sub Create_Series_Csvs()

    Dim startNumber As Long, endNumber As Long
    Dim chunkSize As Long
    Dim i As Long, n As Long
    Dim series() As String
    Dim fileSequenceNumber As Long
    Dim folder As String, csvFile As String
    Dim fileNum As Integer
  
    startNumber = InputBox("Enter the start number")
    endNumber = InputBox("Enter the end number")
    chunkSize = InputBox("Enter the chunk size")
      
    folder = ThisWorkbook.Path & Application.PathSeparator
    If Right(folder, 1) <> Application.PathSeparator Then folder = folder & Application.PathSeparator
  
    fileSequenceNumber = 0
    For i = startNumber To endNumber Step chunkSize
        ReDim series(0 To Application.WorksheetFunction.Min(chunkSize, endNumber - i + 1) - 1)
        For n = 0 To Application.WorksheetFunction.Min(chunkSize, endNumber - i + 1) - 1
            series(n) = i + n
        Next
        csvFile = folder & Chr(Asc("A") + fileSequenceNumber) & Format(Date, "-Mmmm yyyy") & ".csv"
        fileNum = FreeFile
        Open csvFile For Output As #fileNum
        Print #fileNum, "number"
        Print #fileNum, Join(series, vbCrLf)
        Close #fileNum
        fileSequenceNumber = fileSequenceNumber + 1
    Next
  
End Sub

I've used this on several projects in the past day and a half and it is wonderful! I did however find a couple of things I wish I had thought about.

1) Can it be amended to generate a report to the same folder? Just a simple text file that lists the START and END numbers of each file it produced?
Example:
A-May 2022 - 200000 > 219999
B-May 2022 - 220000 > 239999
C-May 2022 - 240000 > 259999

2) Periodically I have projects that require alpha numeric series that also have leading zeros. (Example: PL0000900001 through PL0001000000)
Could the code be made to accommodate forcing a format on the column via input box? So if the project requires it you could input a format like the above example. A few alpha characters and however many zeros. Like you would when using custom formatting in excel. Then move to the 3 input boxes it has now to generate the series.
IF the project doesn't require that, you can move past that formatting input box and use the functionality the code has now.

Honestly, the code you made has already taken such a burden off me, I feel bad about asking for help adding more functionality. I'm actually a graphic artist with a very limited knowledge of excel but with the recent lack of workforce I've had this dropped in my lap. Any help is truly appreciated.

 

[John_w]

1) Can it be amended to generate a report to the same folder? Just a simple text file that lists the START and END numbers of each file it produced?
Example:
A-May 2022 - 200000 > 219999
B-May 2022 - 220000 > 239999
C-May 2022 - 240000 > 259999

2) Periodically I have projects that require alpha numeric series that also have leading zeros. (Example: PL0000900001 through PL0001000000)
Could the code be made to accommodate forcing a format on the column via input box? So if the project requires it you could input a format like the above example. A few alpha characters and however many zeros. Like you would when using custom formatting in excel. Then move to the 3 input boxes it has now to generate the series.
IF the project doesn't require that, you can move past that formatting input box and use the functionality the code has now.

Both changes done in this modified macro.

Public Sub Create_Series_Csvs()

    Dim inputText As String
    Dim alphaNumericFormat As String
    Dim startNumber As Long, endNumber As Long, chunkSize As Long
    Dim size As Long
    Dim i As Long, n As Long
    Dim series() As String
    Dim fileSequenceNumber As Long
    Dim folder As String, csvFile As String, reportFile As String
    Dim csvFileNum As Integer, reportFileNum As Integer
   
    inputText = InputBox("Enter the alphanumeric format (or blank if none)", Default:="AA000000000")
    If StrPtr(inputText) = 0 Then Exit Sub 'Cancel clicked
    alphaNumericFormat = inputText

    inputText = InputBox("Enter the start number")
    If inputText = "" Then Exit Sub
    startNumber = inputText
    inputText = InputBox("Enter the end number")
    If inputText = "" Then Exit Sub
    endNumber = inputText
    inputText = InputBox("Enter the chunk size")
    If inputText = "" Then Exit Sub
    chunkSize = inputText

    folder = ThisWorkbook.Path & Application.PathSeparator
    If Right(folder, 1) <> Application.PathSeparator Then folder = folder & Application.PathSeparator
   
    If alphaNumericFormat = "" Then
        reportFile = folder & "Report" & Format(Date, "-Mmmm yyyy") & " Start " & startNumber & " End " & endNumber & " Chunk Size " & chunkSize & ".txt"
    Else
        alphaNumericFormat = Escape_Date_Symbols(alphaNumericFormat)
        reportFile = folder & "Report" & Format(Date, "-Mmmm yyyy") & " Start " & Format(startNumber, alphaNumericFormat) & " End " & Format(endNumber, alphaNumericFormat) & " Chunk Size " & chunkSize & ".txt"
    End If
   
    reportFileNum = FreeFile
    Open reportFile For Output As #reportFileNum
   
    fileSequenceNumber = 0
    For i = startNumber To endNumber Step chunkSize
        size = Application.WorksheetFunction.Min(chunkSize, endNumber - i + 1)
        ReDim series(0 To size - 1)
        For n = 0 To size - 1
            If alphaNumericFormat = "" Then
                series(n) = i + n
            Else
                series(n) = Format(i + n, alphaNumericFormat)
            End If
        Next
        csvFile = Chr(Asc("A") + fileSequenceNumber) & Format(Date, "-Mmmm yyyy")
        Print #reportFileNum, csvFile & " - " & series(0) & " > " & series(size - 1)
        csvFile = folder & csvFile & ".csv"
        csvFileNum = FreeFile
        Open csvFile For Output As #csvFileNum
        Print #csvFileNum, "number"
        Print #csvFileNum, Join(series, vbCrLf)
        Close #csvFileNum
        fileSequenceNumber = fileSequenceNumber + 1
    Next
   
    Close #reportFileNum
   
End Sub


'https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/format-function-visual-basic-for-applications
'Use date and time symbols in formatString as literal characters by preceding them with a backslash

Private Function Escape_Date_Symbols(formatString As String) As String

    Dim i As Long, DateChars As String

    Escape_Date_Symbols = formatString
    DateChars = "acdhmnpqstwy"
    For i = 1 To Len(DateChars)
        Escape_Date_Symbols = Replace(Escape_Date_Symbols, Mid(DateChars, i, 1), "\" & Mid(DateChars, i, 1))
        Escape_Date_Symbols = Replace(Escape_Date_Symbols, UCase(Mid(DateChars, i, 1)), "\" & UCase(Mid(DateChars, i, 1)))
    Next
   
End Function

Note - If an alphanumeric format is specified, the code uses the VBA Format function to generate the formatted number - series(n) = Format(i + n, alphaNumericFormat). If the alphanumeric format contains date or time symbols (e.g. D, M, Y, H, S, T, etc.) the Format function would normally convert the i + n value to the specified date or time. To avoid this, the code precedes these symbols with a backslash so that the literal characters (e.g. D, M, Y, H, S, T, etc.) are used in the formatted result.

 

[cbutts]

Both changes done in this modified macro.

I'm getting a "Runtime error '52': Bad file name or number" and debug shows the attached screenshot.
I guess it doesn't like my report.

 

[John_w]

Look at the reportFile variable - is it a valid file name?

MsgBox ">" & reportFile & "<"

 

[cbutts]

Look at the reportFile variable - is it a valid file name?

MsgBox ">" & reportFile & "<"

Please excuse my ignorance, but I don't have any idea what that means or how to answer the question.