VBA for exporting a Range of cells in a csv or txt

[martinequilibrium]

Dear Users,

I'm having trouble to export a selection via the following code in VBA.

Some of the rows actually do get exported but I get
Runtime error 13 Type mismatch.
Actually rCell.Value gives me error 2023 (xlErrRef 2023 #REF!)

I have played with the starting row of Set ExportRange = ws.Range("A200:W" & lastrow)
Smaller ranges do work. Ranges with 3000 rows do not seem to work. Maybe a memory issue

I want to export all the rows of ExportedRange.

Sub CreateCSV()

    Dim rCell As Range
    Dim rRow As Range
    Dim ExportRange As Range
    'Dim sOutput As Variant
    Dim sFname As String, lFnum As Long
    
    Const sDELIM As String = ";"
    
    'path = CurDir()
    Path = ActiveWorkbook.Path
    Name = udfWBFilename(ThisWorkbook.FullName)
        
    'Open a text file to write
    sFname = Path & "\" & Name & ".txt"
    lFnum = FreeFile
    Open sFname For Output As lFnum
    
    Set ws = Worksheets("WMS")
    lastrow = ws.Cells(Rows.Count, "E").End(xlUp).Row
    
    Set ExportRange = ws.Range("A200:W" & lastrow)
    MsgBox (ExportRange.Rows)
    'Loop through the rows
    For Each rRow In ExportRange.Rows
        'Loop through the cells in the rows
        For Each rCell In rRow.Cells
            sOutput = sOutput & rCell.Value & sDELIM
        Next rCell
         
        'remove the last comma
        'sOutput = Left(sOutput, Len(sOutput) – Len(sDELIM))
       
        'write to the file and reinitialize the variables
        Print #lFnum, sOutput
        sOutput = vbNullString
    Next rRow
    
   
    'Close the file
    Close lFnum
   
    
End Sub

Can provide sample table if needed.

Cheers
Martin

 

[Joe4]

Curious question.

A blank line gets inserted at the end of the file.
Why is that?

That really isn't a method that I typically use to create CSV files (typically, I just get the data the way I want and save as CSV as opposed to writing it out line-by-line), so I cannot say for sure.
Does the lastrow calculation look like it is correctly identifying the last row of data? You could always temporarily insert a MsgBox statement to check.