CODEWITHSUNDEEP
vbaexcelcsvoutput
Chicken_Hawk
Chicken_Hawk

Reputation: 71

VBA Loop Print to CSV Output

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

Upvotes: 0

Views: 1524

Answers (2)

MatthewHagemann
MatthewHagemann

Reputation: 1195

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

Upvotes: 2

bmgh1985
bmgh1985

Reputation: 789

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.

Upvotes: 1

Related Questions