VBA Loop Print to CSV Output

Question

I'm trying to create a CSV output file in VBA but I can't seem to get it. I need to loop through a spreadsheet and pull numbers from column 'I' based on whether column D has a "1" in it or not. Then I want to paste the contents of column 'I' into column 'A' of the CSV output file. Could someone please help me complete this? I'd like to incorporate all of the following:

Sub Test()

Dim FileNum, bOutputLine, bFile As String
Dim bOUTPUT, iRow As Integer

bOUTPUT = FreeFile                                   'Define bOUTPUT as a FreeFile
bFile = "C:\Desktop\Test.csv"                        'set the filepath equal to a string

For iRow = 2 To ActiveSheet.UsedRange.Rows.Count
If Trim(range("D" & iRow)) <> "" Then
FileNum = Trim(range("I" & iRow))
End If
Next

Open bFile For Output As bOUTPUT                     'Open the file
bOutputLine = FileNum                                
Print #bOUTPUT, bOutputLine                                                                            
Close #bOUTPUT                                       'Close the file

End Sub

MatthewHagemann · Accepted Answer · 2014-06-05 15:51:41Z

You either need to put the file interaction inside the for-next loop and open as append instead of output, or build a string variable in the loop that will print out at the bottom. Here's the two options:

Sub Test()

Dim FileNum, bOutputLine, bFile As String
Dim bOUTPUT, iRow As Integer

bOUTPUT = FreeFile                                   'Define bOUTPUT as a FreeFile
bFile = "C:\Users\HPUser\Desktop\Test.csv"                        'set the filepath equal to a string
Open bFile For Append As bOUTPUT                     'Open the file
For iRow = 2 To ActiveSheet.UsedRange.Rows.Count
    If Trim(range("D" & iRow)) <> "" Then
        FileNum = Trim(range("I" & iRow))

        bOutputLine = FileNum                                
        Print #bOUTPUT, bOutputLine                                                                            

    End If
Next
Close #bOUTPUT                                       'Close the file
End Sub

or

Sub Test()

Dim FileNum, bOutputLine, bFile As String
Dim bOUTPUT, iRow As Integer

bOUTPUT = FreeFile                                   'Define bOUTPUT as a FreeFile
bFile = "C:\Users\HPUser\Desktop\Test.csv"                        'set the filepath equal to a string

For iRow = 2 To ActiveSheet.UsedRange.Rows.Count
    If Trim(range("D" & iRow)) <> "" Then
        bOutputLine = bOutputLine & Trim(range("I" & iRow)) & vbcrlf
    End If
Next

Open bFile For Output As bOUTPUT                     'Open the file                              
Print #bOUTPUT, bOutputLine                                                                            
Close #bOUTPUT                                       'Close the file

End Sub

bmgh1985 · Accepted Answer · 2014-06-05 15:48:10Z

1

One way is to write to it directly in the loop:

Open bFile For Output As bOUTPUT
For iRow = 2 To ActiveSheet.UsedRange.Rows.Count
If InStr(1, Range("D" & iRow), "1") <> 0 Then
Print #bOUTPUT, Trim(Range("I" & iRow))
End If
Next
Close #bOUTPUT

The InStr will look for the value "1" in Column D's cell (as by the wording of the question, it seems it could be something like "AAA1A". It returns 0 if "1" is not found.

answered Jun 5, 2014 at 15:48

bmgh1985

7891 gold badge15 silver badges38 bronze badges

Collectives™ on Stack Overflow

VBA Loop Print to CSV Output

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related