Removing Excess Spaces in CSV Using VBA

Question

I am using a VBA script to compile data from an Excel document into a CSV file for use with a PowerCLI script. I am encountering issues related to having extra blank spaces in the column names and I would like to either determine a way to:

introduce a function to eliminate the extra spaces in my main VBA script or have the VBA TrimEText section relating to removing the extra spaces run simultaneously with the main VBA script

The 'main' VBA script is:

Function BuildValuesString(colIndex As String, rows As String) As String
    Dim val As Variant

    For Each val In Split(rows, ",")
        If Cells(val, colIndex) <> "" Then BuildValuesString = BuildValuesString & Cells(val, colIndex).Value & " , "
    Next val
End Function
Function BuildNullStrings(numNullStrings As Long) As String
    Dim iNullStrings As Long

    For iNullStrings = 1 To numNullStrings
        BuildNullStrings = BuildNullStrings & "" & " , "
    Next iNullStrings
End Function


Sub WriteCSVFile2()

    Dim My_filenumber As Integer
    Dim logSTR As String

    My_filenumber = FreeFile

    logSTR = logSTR & "Name" & " , "
    logSTR = logSTR & "VLAN" & " , "
    logSTR = logSTR & "NumCPU" & " , "
    logSTR = logSTR & "MemoryGB" & " , "
    logSTR = logSTR & "C" & " , "
    logSTR = logSTR & "D" & " , "
    logSTR = logSTR & "App" & " , "

    logSTR = logSTR & Chr(13)

    logSTR = logSTR & BuildValuesString("C", "18,19,20,21,22")
    logSTR = logSTR & BuildNullStrings(1)
    logSTR = logSTR & BuildValuesString("C", "26,27,28,29,30,31,32")

    logSTR = logSTR & Chr(13)
    logSTR = logSTR & BuildNullStrings(6)

    logSTR = logSTR & BuildValuesString("C", "36,37,38,39,40,41,42")

    logSTR = logSTR & Chr(13)
    logSTR = logSTR & BuildNullStrings(6)

    logSTR = logSTR & BuildValuesString("C", "46,47,48,49,50,51,52")


Open "Z:\Operational Env. Requests\2016\Requests(Test)\" & ThisWorkbook.Name & ".csv" For Append As #My_filenumber
    Print #My_filenumber, logSTR
Close #My_filenumber

End Sub

The section relating to remove the excess spaces is:

Sub TrimEText()
' This module will trim extra spaces from BOTH SIDES and excessive spaces from inside the text.
    Dim MyCell As Range
    On Error Resume Next
        Selection.Cells.SpecialCells(xlCellTypeConstants, 23).Select
        For Each MyCell In Selection.Cells
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "", " ")
        Next
    On Error GoTo 0
End Sub

Is there a way to have both run simultaneously without the need for selecting the cells in the exported CSV document? Like specify the cells within the CSV document itself in the VBA script?

Yes - don't use `Selection`. Check out [this thread on avoiding the use of `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). So, just set up a range that you know will have the data (looking for last row can help whittle it down to the used range), and then run the macro on that instead. — BruceWayne, Dec 01 '16 at 16:37
Also, try to avoid using `On Error Resume Next`, it just masks errors rather than dealing with them — CallumDA, Dec 01 '16 at 16:40
This would be much, much easier and less error prone using either a temporary worksheet that you save as a CSV or an ADODB connection with a text driver. — Comintern, Dec 01 '16 at 17:29

score 3 · Accepted Answer · answered Dec 01 '16 at 17:42

"I am encountering issues related to having extra blank spaces in the column names" Here is were you are adding spaces to the column names:

logSTR = logSTR & "Name" & " , "

On the last line you add an extra column with no header. This can be very problematic.

logSTR = logSTR & "App" & " , "

There are 7 columns not 6. BuildNullStrings is not needs `String(7,",") does the same thing.

logSTR = logSTR & BuildNullStrings(6)

This will cause your data to be misaligned and missing columns.

If Cells(val, colIndex) <> "" Then BuildValuesString = BuildValuesString & Cells(val, colIndex).Value & " , "

Refactored Code

Sub WriteCSVFile2()
    Dim logSTR As String
    Dim My_filenumber As Integer
    Dim data(6)
    Dim logSTR As String
    data(0) = Join(Array("Name", "VLAN", "NumCPU", "MemoryGB", "C", "D", "App"), ",")
    data(1) = BuildValuesString("C", 18, 19, 20, 21, 22) & String(1, ",")
    data(2) = BuildValuesString("C", 26, 27, 28, 29, 30, 31, 32)
    data(3) = String(7, ",")
    data(4) = BuildValuesString("C", 36, 37, 38, 39, 40, 41, 42)
    data(5) = String(7, ",")
    data(6) = BuildValuesString("C", 46, 47, 48, 49, 50, 51, 52)

    logSTR = Join(data, vbCrLf)

    My_filenumber = FreeFile
    Open "Z:\Operational Env. Requests\2016\Requests(Test)\" & ThisWorkbook.Name & ".csv" For Append As #My_filenumber
    Print #My_filenumber, logSTR
    Close #My_filenumber

End Sub

Function BuildValuesString(colIndex As String, ParamArray RowNumbers()) As String
    Dim x As Long
    Dim result As String
    For x = 0 To UBound(RowNumbers)
        RowNumbers(x) = Trim(Cells(clong(RowNumbers(x)), colIndex).Value)
        If Cells(val, colIndex) <> "" Then BuildValuesString = BuildValuesString & Cells(val, colIndex).Value & " , "
    Next
    BuildValuesString = Join(RowNumbers, ",")
End Function

Trim Used Range

Sub TrimData() Application.ScreenUpdating = False Dim c As Range

For Each c In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    c.Value = Trim(c.Value)
Next

Application.ScreenUpdating = True

End Sub

Removing Excess Spaces in CSV Using VBA

1 Answers1

Refactored Code

Trim Used Range