GPSPOW
asked on
Save Excel 2007 Data Range to a CSV file
I have the following VBA code.
Sub ExportRange()
Dim FirstCol As Integer
Dim LastCol As Integer
Dim C As Integer
Dim FirstRow
Dim LastRow
Dim R
Dim data
Dim ExpRng As Range
Sheets("AddrFilt").Select
Range("L1").Select
Set ExpRng = ActiveCell.CurrentRegion
FirstCol = ExpRng.Columns(1).Column
LastCol = FirstCol + ExpRng.Columns.Count - 1
FirstRow = ExpRng.Rows(1).Row
LastRow = FirstRow + ExpRng.Rows.Count - 1
Open ThisWorkbook.Path & "C:\Users\Glen\Documents\P MC\NHSN_Da ta.csv" For Output As #1 ' csv file
'''''''Open ThisWorkbook.Path & "\textfile.txt" For Output As #1 '''''or txt file
For R = FirstRow To LastRow
For C = FirstCol To LastCol
data = ExpRng.Cells(R, C).Value
If data = "" Then data = ""
If IsNumeric(data) Then data = Val(data)
If C <> LastCol Then
Write #1, data;
Else
Write #1, data
End If
Next C
Next R
Close #1
End Sub
The goal is to export the data in Col L through Col R. Row 1 has headers, It should stop copying data when it reaches a record with:
#VALUE! #VALUE! #N/A #N/A #N/A #N/A #VALUE!
First how can I write a Do While Not loop to stop at the first invalid record.
Second, I get a Bad File Name or Number error for the Open ThisWorkbook.Path statement..
Thanks
Glen
Sub ExportRange()
Dim FirstCol As Integer
Dim LastCol As Integer
Dim C As Integer
Dim FirstRow
Dim LastRow
Dim R
Dim data
Dim ExpRng As Range
Sheets("AddrFilt").Select
Range("L1").Select
Set ExpRng = ActiveCell.CurrentRegion
FirstCol = ExpRng.Columns(1).Column
LastCol = FirstCol + ExpRng.Columns.Count - 1
FirstRow = ExpRng.Rows(1).Row
LastRow = FirstRow + ExpRng.Rows.Count - 1
Open ThisWorkbook.Path & "C:\Users\Glen\Documents\P
'''''''Open ThisWorkbook.Path & "\textfile.txt" For Output As #1 '''''or txt file
For R = FirstRow To LastRow
For C = FirstCol To LastCol
data = ExpRng.Cells(R, C).Value
If data = "" Then data = ""
If IsNumeric(data) Then data = Val(data)
If C <> LastCol Then
Write #1, data;
Else
Write #1, data
End If
Next C
Next R
Close #1
End Sub
The goal is to export the data in Col L through Col R. Row 1 has headers, It should stop copying data when it reaches a record with:
#VALUE! #VALUE! #N/A #N/A #N/A #N/A #VALUE!
First how can I write a Do While Not loop to stop at the first invalid record.
Second, I get a Bad File Name or Number error for the Open ThisWorkbook.Path statement..
Thanks
Glen
Then you have the issue of the looping:
testing the first Column for each Row for ISERROR should do it.
testing the first Column for each Row for ISERROR should do it.
For R = FirstRow To LastRow
if iserror(ExpRng.Cells(R, FirstCol).Value) then exit for 'added error test
For C = FirstCol To LastCol
data = ExpRng.Cells(R, C).Value
If data = "" Then data = ""
If IsNumeric(data) Then data = Val(data)
If C <> LastCol Then
Write #1, data;
Else
Write #1, data
End If
Next C
Next R
ASKER
Here is code after I made the above suggestions:
Sub ExportRange()
Dim FirstCol As Integer
Dim LastCol As Integer
Dim C As Integer
Dim FirstRow
Dim LastRow
Dim R
Dim data
Dim ExpRng As Range
Sheets("AddrFilt").Select
Range("L1").Select
Set ExpRng = ActiveCell.CurrentRegion
FirstCol = ExpRng.Columns(1).Column
LastCol = FirstCol + ExpRng.Columns.Count - 1
FirstRow = ExpRng.Rows(1).Row
LastRow = FirstRow + ExpRng.Rows.Count - 1
Open "C:\Users\Glen\Documents\P MC\NHSN_Da ta.csv" For Output As #1
'Open ThisWorkbook.Path & "C:\Users\Glen\Documents\P MC\NHSN_Da ta.csv" For Output As #1 ' csv file
'''''''Open ThisWorkbook.Path & "\textfile.txt" For Output As #1 '''''or txt file
For R = FirstRow To LastRow
If IsError(ExpRng.Cells(R, 1).Value) Then Exit For 'added error test
For C = FirstCol To LastCol
data = ExpRng.Cells(R, C).Value
If data = "" Then data = ""
If IsNumeric(data) Then data = Val(data)
If C <> LastCol Then
Write #1, data;
Else
Write #1, data
End If
Next C
Next R
Close #1
End Sub
Here is a sample of my data with the headers:
FirstName Last Name Address City State PostalCode Title
LARRY ALEXANDER 5125 FAIRMONT PKWY PASADENA TX 77505 MD
NATARAJAN BALA 444 FM 1959 HOUSTON TX 77034 MD
BERNARD BRIAN BRADLEY 4003 WOODLAWN AVE PASADENA TX 77504 MD
SATHISH CAYENNE 4004 WOODLAWN AVE PASADENA TX 77504 MD
NIZAR CHARAFEDDINE 2813 SMITH RANCH ROAD PEARLAND TX 77584 MD
#VALUE! #VALUE! #N/A #N/A #N/A #N/A #VALUE!
I getting a Data Type Mismatch at this line:
If data = "" Then data = ""
Any suggestions?
Thanks
Glen
Sub ExportRange()
Dim FirstCol As Integer
Dim LastCol As Integer
Dim C As Integer
Dim FirstRow
Dim LastRow
Dim R
Dim data
Dim ExpRng As Range
Sheets("AddrFilt").Select
Range("L1").Select
Set ExpRng = ActiveCell.CurrentRegion
FirstCol = ExpRng.Columns(1).Column
LastCol = FirstCol + ExpRng.Columns.Count - 1
FirstRow = ExpRng.Rows(1).Row
LastRow = FirstRow + ExpRng.Rows.Count - 1
Open "C:\Users\Glen\Documents\P
'Open ThisWorkbook.Path & "C:\Users\Glen\Documents\P
'''''''Open ThisWorkbook.Path & "\textfile.txt" For Output As #1 '''''or txt file
For R = FirstRow To LastRow
If IsError(ExpRng.Cells(R, 1).Value) Then Exit For 'added error test
For C = FirstCol To LastCol
data = ExpRng.Cells(R, C).Value
If data = "" Then data = ""
If IsNumeric(data) Then data = Val(data)
If C <> LastCol Then
Write #1, data;
Else
Write #1, data
End If
Next C
Next R
Close #1
End Sub
Here is a sample of my data with the headers:
FirstName Last Name Address City State PostalCode Title
LARRY ALEXANDER 5125 FAIRMONT PKWY PASADENA TX 77505 MD
NATARAJAN BALA 444 FM 1959 HOUSTON TX 77034 MD
BERNARD BRIAN BRADLEY 4003 WOODLAWN AVE PASADENA TX 77504 MD
SATHISH CAYENNE 4004 WOODLAWN AVE PASADENA TX 77504 MD
NIZAR CHARAFEDDINE 2813 SMITH RANCH ROAD PEARLAND TX 77584 MD
#VALUE! #VALUE! #N/A #N/A #N/A #N/A #VALUE!
I getting a Data Type Mismatch at this line:
If data = "" Then data = ""
Any suggestions?
Thanks
Glen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you
It worked perfectly.
It worked perfectly.
You are welcome, anytime.
Open in new window
This will read as "C:\Users\Glen\Documents\P
Open in new window
orOpen in new window