CODEWITHSUNDEEP
vbaexcel
inquest
inquest

Reputation: 15

Excel Macro throws error if the file exceeds certain number of rows

I have created a excel macro vba script to create text files for each customer code from a file that has total of 32000 rows. It creates the text files exactly as required but throws a run time error after it crosses certain number of rows, about 8000-9000. It works fine when I split the file into 4 files or so. How to make the script work with one file that has around 32000 rows. Please see the script below and suggest what changes I can do?

Option Explicit

Sub CreateForEachLine()
Dim myPathTo As String
myPathTo = "C:\Users\9418\Desktop\Work Files\Vending"
Dim myFileSystemObject As Object
Set myFileSystemObject = CreateObject("Scripting.FileSystemObject")
Dim fileOut As Object
Dim myFileName As String

Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Dim i As Long

    For i = 1 To lastRow
        If Not IsEmpty(Cells(i, 1)) Then
            myFileName = Cells(i, 1) & ".txt"
            Set fileOut = myFileSystemObject.OpenTextFile(myFileName, 8, True)
            fileOut.write Cells(i, 2) & "   " & Cells(i, 3) & "   " & Cells(i, 4) & "   " & Cells(i, 5) & vbNewLine
            fileOut.Close
        End If
    Next

Set myFileSystemObject = Nothing
Set fileOut = Nothing
End Sub

Upvotes: 0

Views: 171

Answers (1)

RazorKillBen
RazorKillBen

Reputation: 573

Type mismatch error is suggesting that the datatype in the cells is not compatible or expected. I would imagine that this is because there is some incorrect 'data type' data in some of your rows, such as formula #N/A values or strings that exceed a certain length etc, that doesn't match with the memory declarations. Try searching through your data for blank rows, formula errors, or lengthy strings that may cause a type mismatch error and try the code again.

Upvotes: 0

Related Questions