Link to home
Start Free TrialLog in
Avatar of GPSPOW
GPSPOWFlag for United States of America

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\PMC\NHSN_Data.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
Avatar of Steve
Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

Firstly:

Open ThisWorkbook.Path & "C:\Users\Glen\Documents\PMC\NHSN_Data.csv" For Output As 

Open in new window


This will read as "C:\Users\Glen\Documents\PMCC:\Users\Glen\Documents\PMC\NHSN_Data.csv" for the file path... this should be either:
Open ThisWorkbook.Path & "\NHSN_Data.csv" For Output As

Open in new window

or
Open "C:\Users\Glen\Documents\PMC\NHSN_Data.csv" For Output As

Open in new window

Then you have the issue of the looping:
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

Open in new window

Avatar of GPSPOW

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\PMC\NHSN_Data.csv" For Output As #1
    'Open ThisWorkbook.Path & "C:\Users\Glen\Documents\PMC\NHSN_Data.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
ASKER CERTIFIED SOLUTION
Avatar of Steve
Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GPSPOW

ASKER

Thank you

It worked perfectly.
You are welcome, anytime.