CODEWITHSUNDEEP
excelvbacsv
Rivers31334
Rivers31334

Reputation: 654

Writing from .csv to txt file with differing spaces between columns

I have written the vba code below to take an excel file, and read/write it into a new text file. The main issue I had with doing this was that the spacing needed in the new text file was not uniformed. By that, I mean that if row 1 with four columns is [column a = 2 column b = 3, column c = 4, and column d = 5], the output in the text file will be:

2       3    4              5

There is a tab between the 2 and 3, four spaces between the 3 and 4, and 14 spaces between the 4 and 5. This is quite random, but formatting is due to previous files created.

I successfully completed one aspect of this problem, however, there are two new issues that come about.

In the third column of my excel file, not all the numbers are of the same length. Ex - Cells(3, 3).Value = 79.13 and Cells(4, 3).Value = 81.6

How do I get the following (which is what happens now):

"302277600       19940130    79.13              18.06"
"302277600       19940131    81.6              18.06"

to turn to this:

"302277600       19940130    79.13              18.06"
"302277600       19940131    81.6               18.06"

Basically, changing the spacing between values based upon the length. Further, is there a way to get rid of the quotation marks?

Sub excelToTxt()

Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long

LastCol = 4
LastRow = 19954

FilePath = Application.DefaultFilePath & "\test.txt"

Open FilePath For Output As #2

For i = 1 To LastRow

    CellData = ""

    For j = 1 To LastCol
        If j = 1 Then
            CellData = CellData + Trim(ActiveCell(i, j).Value) & "       "
        ElseIf j = 2 Then
            CellData = CellData + Trim(ActiveCell(i, j).Value) & "    "
        ElseIf j = 3 Then
            CellData = CellData + Trim(ActiveCell(i, j).Value) & "              "
        ElseIf j = LastCol Then
            CellData = CellData & Trim(ActiveCell(i, j).Value)
        End If

Next j

Write #2, CellData

Next i

Close #2

End Sub

Is anyone able to help with this problem?

Upvotes: 1

Views: 1086

Answers (1)

MatthewD
MatthewD

Reputation: 6761

Combine this with you if statements.

Public Function PadSpace(nMaxSpace As Integer, nNumSpace As Integer) As String
    If nMaxSpace < nNumSpace Then
        PadSpace = ""
    Else
        PadSpace = Space(nMaxSpace - nNumSpace)
    End If
End Function

You call it with the Column width and the length of the value. It returns a string with the number of spaces to put between the value and the next value. The "Pad"

    If j = 1 Then
        CellData = CellData + Trim(ActiveCell(i, j).Value) & PadSpace(16, Len(Trim(ActiveCell(i, j).Value)))
    ElseIf j = 2 Then
        CellData = CellData + Trim(ActiveCell(i, j).Value) & PadSpace(12, Len(Trim(ActiveCell(i, j).Value)))
    ElseIf j = 3 Then
        CellData = CellData + Trim(ActiveCell(i, j).Value) & PadSpace(19, Len(Trim(ActiveCell(i, j).Value)))
    ElseIf j = LastCol Then
        CellData = CellData & Trim(ActiveCell(i, j).Value)
    End If

Upvotes: 1

Related Questions