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

 

[ButtFace]

Maybe copy range to new workbook, save new workbook as csv?

 

[martinequilibrium]

Maybe copy range to new workbook, save new workbook as csv?

Thanks
I know I can do that. But I just want a range and would like to have it automated to avoid user interaction except clicking a Macro Button.

 

[Joe4]

Can provide sample table if needed.

That would be helpful, especially if it is from the file that is failing.

There are instructions on how you can post images found in the "Posting Aids" section of this link here: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html. There is also a "Test Here" forum on this site that you can use to test those tools before using them in your threads.

 

[martinequilibrium]

https://drive.google.com/file/d/0B9Pn6XscpTY7ZmhzMlo2NkhFb0k/view?usp=sharing

I'm attaching the file. There is a procedure identical to the one posted above called:
Sub CreateCSV()
If you run it, it will produce an incomplete file in the same folder of about 208 KB and 1232 lines.

Thanks

 

[martinequilibrium]

The actual number of records needed to extract are 3039

 

[Joe4]

I cannot download files from my current location (security protocol), and I know that many others are unable or unwilling to download files for security reasons as well. I may be able to download the file tonight when I am home.

 

[martinequilibrium]

I cannot download files from my current location (security protocol), and I know that many others are unable or unwilling to download files for security reasons as well. I may be able to download the file tonight when I am home.

Thanks

A low number of records seem to work fine with the code.
It is possible to try the above code with any long workbook with a similar number of rows like 300 or so.

Just change
Set ws = Worksheets("WMS") to Set ws = Worksheets(1)

Many thanks again for your time!

 

[Joe4]

OK. I downloaded your file and found two issues.

1. This is not a valid line of code:

MsgBox (ExportRange.Rows)

If you are trying to to return the total number of rows in the range, use this:

MsgBox (ExportRange.Rows.Count)

2. You have errors in column W of your WMS worksheet, starting on row 1431. You need to correct these errors, it cannot build the string with them in there.

 

[martinequilibrium]

Thank You very much Joe4!
That solved my issue! . Kudos

Now can export the needed data!

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

OK. I downloaded your file and found two issues.

1. This is not a valid line of code:

MsgBox (ExportRange.Rows)

If you are trying to to return the total number of rows in the range, use this:

MsgBox (ExportRange.Rows.Count)

2. You have errors in column W of your WMS worksheet, starting on row 1431. You need to correct these errors, it cannot build the string with them in there.