In the current workbook this code imports a selected text file in to an existing named worksheet “Import” using a File Dialog Object.

It imports smaller text files without error but the larger text files cause a Run-time ‘6’: Overflow error on the counter line “counter = counter + 1”.

The counter is important as it increments the counter at each iteration of the loop to paste each line of the text file into subsequent rows of the Worksheet.

The counter really needs to be unlimited as I have no control of how big each text file will be and some could have thousands of rows to import.

Also, once I have got the single file import working correctly, the intention is to allow multiple file imports, so the potential is thousands of thousands rows to import.

Thanks.

Sub Single_Sheet_ImportTextFile()
    Dim fd As fileDialog
    Dim selectedFile As String
    Dim textLine As String
    Dim counter As Integer
    Dim arr() As String
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Import ")
    
'Create the FileDialog object as a File Picker (Single file only)
Set fd = Application.fileDialog(msoFileDialogFilePicker)
With fd
    .Title = "Select a Text File"
    .Filters.Clear
    .Filters.Add "Text Files", "*.txt"
    .AllowMultiSelect = False
    
    If .Show = -1 Then
       selectedFile = .SelectedItems(1)
   Else
       Exit Sub
    End If
End With

'Read the text file and import it into the worksheet "Import"
Open selectedFile For Input As #1
counter = 1 ' Initialize the counter
Do Until EOF(1)
    Line Input #1, textLine
    arr = Split(textLine, ",")
    For i = LBound(arr) To UBound(arr)
        ws.Cells(counter, i + 1).Value = arr(i)
    Next i
    counter = counter + 1
Loop
Close #1

'pop-up message to confirm the file has been imported.
MsgBox "Text file imported successfully.", vbInformation
End Sub

I have set the ‘counter’ data type to ‘Long’ which should allow the counter to grow larger and avoid overflow compared to using an ‘Integer’ but it does not seem to work.

Googling or reading Microsoft’s pages on this error have not provided any help.