0

I have simple code that creates a csv from a table that works perfectly but I need to iterate through the data in the table and remove any 0's (any cell that contains the number 0 as the table has equations) before it writes to the output file. I can't simply modify my equations in the table with an if statement to return " " as the output file I'm creating will simply add a space in the output csv, which is just as bad. I'm using the output file as a input file for a script using Pascal and it would just skew the data it's pasting because of the " " (space).

I've tried using the below in my code and it does not work.

If csvVal = 0 Then
      csvVal.Replace What:=0, Replacement:=vbNullString, Lookat:=xlWhole
End If

Sub saveTableToCSV2()

    Dim tbl As ListObject
    Dim csvFilePath As String
    Dim fNum As Integer
    Dim tblArr
    Dim rowArr
    Dim csvVal
    Dim csvVal2 As Object
    Dim r As Range

    Set tbl = Worksheets("FDA TOOL").ListObjects("tblFDAinfo")
    csvFilePath = "F:\Scripting\Import\Wal-Mart\Zac\Audit_Tool2FDA.csv"
    tblArr = tbl.DataBodyRange.Value

    fNum = FreeFile()
    Open csvFilePath For Output As #fNum
    For i = 1 To UBound(tblArr)
        rowArr = Application.Index(tblArr, i, 0)
        csvVal = VBA.Join(rowArr, ",")
        If csvVal = 0 Then
            csvVal.Replace What:=0, Replacement:=vbNullString, Lookat:=xlWhole
        End If
        Print #1, csvVal
    Next

    Close #fNum
    Set tblArr = Nothing
    Set rowArr = Nothing
    Set csvVal = Nothing
End Sub

I'd like to remove any 0's before it writes to the output file.

Below is what it is returning for the first line of the table.

REP,RAD,130,4,CT,RB1,,,200,PCS,ACC,17389,96R--CR,,,0,,,,,0,

I would like it to return:

REP,RAD,130,4,CT,RB1,,,200,PCS,ACC,17389,96R--CR,,,,,,,,,
cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
tilsonz
  • 1
  • `" "` (a space) is not the same as `""` (an empty string). `""` *should* do what you want. Another option is to look for `",0,"` in the csv and replace with `",,"` – cybernetic.nomad Jan 09 '19 at 16:01
  • Right. My equal in the table (input file) is =IF(C2="", "", C11) however C11 contains the formula =IF(C2="", B11, INDEX(tblProductCode[AOC3],MATCH(C2,tblProductCode[Comodity],0))) – tilsonz Jan 09 '19 at 16:43
  • What if you use one of the solutions [here](https://stackoverflow.com/questions/10434335/text-file-in-vba-open-find-replace-saveas-close-file) to edit the csv as a text file, replacing `",0,"` with `",,"`? – cybernetic.nomad Jan 09 '19 at 17:04
  • That worked perfectly! Thank you so much! – tilsonz Jan 09 '19 at 17:38
  • If you haven't done so already, I suggest you go vote up the answer with the solution that worked for you – cybernetic.nomad Jan 09 '19 at 17:40

1 Answers1

0

Try replacing: csvVal.Replace What:=0, Replacement:=vbNullString, Lookat:=xlWhole with csvVal = Replace(csvVal, 0, "")

W-hit
  • 353
  • 3
  • 14
  • unfortunately that does not remove the 0's either. – tilsonz Jan 09 '19 at 16:44
  • It's a string, so use `csvVal = Replace(csvVal, ",0,", ",,")` You need the `,`'s to avoid turning `200` into `2`. Also if the last item is a 0 it won`t replace that one, handle it separately – chris neilsen Jan 09 '19 at 18:06