VBA to export to CSV, ignoring cells with value of "null"

[AGrayson84]

Hi everyone, I'm working on a final piece to my puzzle, for now, and that is to certain data from cells in one worksheet to format another worksheet for a CSV file that will be used for creating DHCP reservations.

What I'd like is to export only data from my worksheet called, "DHCP", that doesn't contain "null" in the the cells. Only columns A thru H are being used, and rows 1 thru 200. Each row may vary-- any given row may end up containing either the word "null" across all 8 columns, or all 8 cells in each row will contain other text that is not equal to "null". The word "null" is either all the way across each row, or it doesn't exist in a row at all-- a cell containing the word "null" is never mixed in a row with other cells that don't contain the word "null".

More specifically, I would like any row that contains the word "null" to just be completely ignored on the export file. I'm trying to avoid having 7 commas side-by-side in the export file, for each row that contained Null. Here is an example of the data I'm working with, and how I'd like the CSV to end up looking:

Data before exported to CSV:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple[/TD]
[TD]Banana[/TD]
[TD]Orange[/TD]
[TD]Grape[/TD]
[TD]Watermelon[/TD]
[TD]Cantaloupe[/TD]
[TD]Strawberry[/TD]
[TD]Cherry[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null[/TD]
[/TR]
</tbody>[/TABLE]


Data after exported to CSV (viewed in Excel):
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apple[/TD]
[TD]Banana[/TD]
[TD]Orange[/TD]
[TD]Grape[/TD]
[TD]Watermelon[/TD]
[TD]Cantaloupe[/TD]
[TD]Strawberry[/TD]
[TD]Cherry[/TD]
[/TR]
</tbody>[/TABLE]

Data after exported to CSV (viewed In Notepad):

Apple,Banana,Orange,Grape,Watermelon,Cantaloupe,Strawberry,Cherry


Here is a simple export-to-CSV VBA script that I think I could use, I just can't figure out how to right the IF statement so that it does nothing with the rows that contain the word "null":

  Dim ColNum As Integer
  Dim Line As String
  Dim LineValues() As Variant
  Dim OutputFileNum As Integer
  Dim PathName As String
  Dim RowNum As Integer
  Dim SheetValues() As Variant

  PathName = Application.ActiveWorkbook.Path
  OutputFileNum = FreeFile

  Open PathName & "\Test.csv" For Output Lock Write As #OutputFileNum


  SheetValues = Sheets("DHCP").Range("A1:H200").Value
  ReDim LineValues(1 To 8)

  For RowNum = 1 To 200
    For ColNum = 1 To 8
      LineValues(ColNum) = SheetValues(RowNum, ColNum)
    Next
    Line = Join(LineValues, ",")
    Print #OutputFileNum, Line
    End If
    
  Next

  Close OutputFileNum

If someone wouldn't mind helping out with this I'd very greatly appreciate it! I have a whole slew of other VBA scripts doing various things and this is the last thing that I have been having a hard time with and can't seem to find any similar requests on anywhere else on the Internet. Thanks everyone!

-Andrew

 

[rlv01]

Another approach would be to use autofilter to remove the NULL rows and save it directly as a .csv file.

One example:

Sub NoNullSaveCSV()
    Dim WB As Workbook
    Dim WS As Worksheet


    Application.Workbooks.Add xlWBATWorksheet
    Set WB = ActiveWorkbook
    Set WS = ActiveSheet


    With ThisWorkbook.Worksheets("DHCP")
        .Range("A1:H200").Copy
        WS.Range("A2").PasteSpecial xlPasteValues
    End With
    
    WS.Range("A1").Value = "FilterCol"
    WS.Columns.AutoFilter Field:=1, Criteria1:="NULL"
    WS.UsedRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    WS.AutoFilterMode = False
    
    WB.SaveAs Filename:=ThisWorkbook.Path & "\Test.csv", FileFormat:=xlCSV
    WB.Close False
End Sub

 

[AGrayson84]

THANK YOU SO MUCH!!! What I've spent hours, beating my head against my keyboard, trying to find a way to achieve this.... you've provided a solution that will save both my keyboard and my head!

It worked like a charm, and I really do appreciate you taking the time to help with that!!

The only thing that I found not to work as I hope, and my apologies for not bringing this up in my original post, is that this is how I want to build my filename:

"Z:\4_DHCP_Reservation\" & Worksheets("Input").Range("A8").Value & "_" & Worksheets("Input").Range("B8").Value & "_" & Format$(Date, "mmddyyyy") & "_" & "DHCP_Reservation" & ".csv",

I am replacing the below text from your example with the above:

ThisWorkbook.Path & "\Test.csv",

I've used the same string to build filenames (using a very different VBA script) from data on the "Input" worksheet when exporting to a .ps1 file, and it has worked just fine. I get a "Run-time error: '9': Subscript out of range". Any idea how to implement the referencing of cell values with your script to build the file name? I only seem to get the error the moment I try to add the call to pull the data from a cell to form the file name.

I did also just notice that when I run your un-altered script it opens the data to be exported to the CSV in a new file. Is there any way to eliminate that? Thanks again..... I appreciate it a ton!!!

 

[rlv01]

There is nothing wrong with the statement that you are using to build your output file name.

    OutputFileName = "Z:\4_DHCP_Reservation\" & _
        Worksheets("Input").Range("A8").Value & "_" & _
        Worksheets("Input").Range("B8").Value & "_" & Format$(Date, "mmddyyyy") _
        & "_" & "DHCP_Reservation" & ".csv"

However it requires two things. 1)A worksheet named "Input" 2)String data in cells A8 and B8. So for example, if the cell contents were

A8 = "Cisco"
B8 = "Router"

And the date was today, then your file name would be:

"Z:\4_DHCP_Reservation\Cisco_Router_05162017_DHCP_Reservation.csv"

The runtime error 9 is because you were likely trying to run it with some other workbook active. The answer is to be more explicit about your workbook definitions.

Sub NoNullSaveCSV()
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim OutputFileName As String

    Application.Workbooks.Add xlWBATWorksheet
    Set WB = ActiveWorkbook
    Set WS = ActiveSheet

    With ThisWorkbook.Worksheets("DHCP")
        .Range("A1:H200").Copy
        WS.Range("A2").PasteSpecial xlPasteValues
    End With

    WS.Range("A1").Value = "FilterCol"
    WS.Columns.AutoFilter Field:=1, Criteria1:="NULL"
    WS.UsedRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    WS.AutoFilterMode = False

    With ThisWorkbook
        OutputFileName = "Z:\4_DHCP_Reservation\" & .Worksheets("Input").Range("A8").Value & "_" & .Worksheets("Input").Range("B8").Value & "_" & Format$(Date, "mmddyyyy") & "_" & "DHCP_Reservation" & ".csv"
    End With

    On Error Resume Next
    WB.SaveAs Filename:=OutputFileName, FileFormat:=xlCSV
    WB.Close False
End Sub

I did also just notice that when I run your un-altered script it opens the data to be exported to the CSV in a new file. Is there any way to eliminate that?

That would kind of defeat the purpose since the intent is to create a new CSV file with your non-NULL data. Doing it via the excel save-as function is more efficient than looping through cells to write to a text file. Btw, it does not 'open the data in a new file'. It creates a new blank workbook, copies the non-NULL data into it, then saves it as a CSV file. You will never see a trace of that new workbook after you run the macro under normal condition because only the CSV file is saved to disk. However if you are using the VBA editor/debugger to single-step through the code and halt execution half way through, then you may have an open workbook named "SheetX" (where X is some number) that you'll have to manually close/delete. That's just the nature of playing around in the debugger.