martinequilibrium
New Member
- Joined
- Feb 26, 2016
- Messages
- 13
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.
Can provide sample table if needed.
Cheers
Martin
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.
Code:
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