Creating a .txt file with VBA. How do you create variable spaced columns?-CodePudding

I have managed to use piece of code which will export data from an Excel spreadsheet and into a .txt document.

Due to the nature of the task however the data has to be certain spaces wide, i.e the first column in the .txt folder has to be 8 spaces wide, second column 20, etc. I know how to create fixed length columns whilst importing to a .txt using VBA (as below), but how is variable spacing done.

Code so far is as follows:

    Public Sub CompileMacro()

    Dim lRow As Long
    Dim lCol As Long
    Dim strRow As String
    Dim ws As Excel.Worksheet
    Dim ts As TextStream
    Dim fs As FileSystemObject


    Set fs = New FileSystemObject
    Set ts = fs.CreateTextFile(" C: . . . ", True, False)

    Set ws = Application.ActiveSheet

    Row = 1
    Do While Row <= ws.UsedRange.Rows.Count

       strRow = ""

       Col = 1
       Do While Col <= ws.UsedRange.Columns.Count
           strRow = strRow & ws.Cells(Row, Col) & PadSpace(8, Len(ws.Cells(Row, Col)))
    
           Col = Col   1

       Loop

       ts.WriteLine strRow

       Row = Row   1
       ws.Range("A" & Row).Activate
    Loop

    ts.Close: Set ts = Nothing
    Set fs = Nothing

    End Sub



    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

CodePudding user response：

Create an array to hold your padding settings:

padding = Array(8, 20, 10, 10, 20, 8, 10, 10)

Change your string-builder to use the value for the given column:

strRow = strRow & ws.Cells(Row, Col) & PadSpace(padding(Col - 1), Len(ws.Cells(Row, Col)))

So you're loop would look something like this:

Row = 1

padding = Array(8, 20, 10, 10, 20, 8, 10, 10)

Do While Row <= ws.UsedRange.Rows.Count

   strRow = ""

   Col = 1
   Do While Col <= ws.UsedRange.Columns.Count
       strRow = strRow & ws.Cells(Row, Col) & PadSpace(padding(Col - 1), Len(ws.Cells(Row, Col)))

       Col = Col   1

   Loop

   ts.WriteLine strRow

   Row = Row   1
   ws.Range("A" & Row).Activate
   
Loop

CodePudding user response：

Maybe try using Space$ in combination with unusual Mid$ on the left of assignment operator like the following

strRow = Space$(padding(col))
Mid$(strRow, 1, Len(ws.Cells(Row, col))) = ws.Cells(Row, col)

Also, you need padding array for example defined like padding = Array(0, 8, 20) .

Mid$(strRow, 1, Len(ws.Cells(Row, col))) rewrites space characters inside strRow string with ws.Cells(Row, col)