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]

Insert that MsgBox line immediately above the Open reportFile line, run the code and post what the MsgBox displays.

The MsgBox line displays the contents of the reportFile variable, which is the full name of the report file (.txt file), including folder path. If the full file name is invalid that would explain the error.

 

[John_w]

I'm getting a "Runtime error '52': Bad file name or number" and debug shows the attached screenshot.

Researching this, it seems the maximum length of a file name (including extension) on a Mac is 32 characters. I've therefore reduced the length of the file name.

Replace:

    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

with:

    If alphaNumericFormat <> "" Then alphaNumericFormat = Escape_Date_Symbols(alphaNumericFormat)
    reportFile = folder & "Report " & Format(Now, "YYYYMMDD HHMMSS") & ".txt"

 

[cbutts]

Works like a charm. Many thanks for helping me with this John!