How to loop via columns in excel VBA

Question

I'd like to output text file from some sample excel files.

So that I created following samples.

after opening text file , each rows are printed.

But when I try to loop over columns , these values are appended in one columns

Are there any good way to achieve row and column based loop ?

This text file uses comma separator.

Thanks.

 Sub Test_Open()
      Dim strFilePath As String
      Dim ws As Worksheet
      
      
      strFilePath = "C:\Users\test\text.txt" 
     
      Workbooks.Open "C:\Users\test.xlsx"
        
      Set ws = ActiveWorkbook.Worksheets("test")
      
      Open strFilePath For Output As #1
      
        Dim row As Integer
        Dim column As Integer
        
        row = 7
        Do Until ws.Cells(row, 2).Value = ""
           For column = 1 To 86
                Print #1, ws.Cells(row, column)
           Next
           row = row + 1
        Loop

      Close #1
     
    End Sub

What separator does the text file uses? Is it comma, Tab, something else? — FaneDuru
– FaneDuru, Commented Aug 11, 2021 at 7:15
Ok. Are there the same number of columns on each text file row? Does it have headers in its first line? If yes (or not), can be the first row/line considered like having the necessary number of columns? In fact, if not something confidential, can you share the used text file? — FaneDuru
– FaneDuru, Commented Aug 11, 2021 at 7:20
Then, please try the code I posted. It builds an array, working fast, only in memory and drops the result at once, at the end. Can you share such a file, even a dummy one? I would like to test the code... — FaneDuru
– FaneDuru, Commented Aug 11, 2021 at 10:23

doctorgu · Accepted Answer · 2021-08-11 07:21:19Z

1

You can add some variable to hold all your column information.

Change your code

For column = 1 To 86
    Print #1, ws.Cells(row, column)
Next

To this code.

Dim cols As String
' Add all column separated by comma(,)
For column = 1 To 86
    cols = cols & "," & ws.Cells(row, column)
Next
' Trim first comma(,)
cols = Mid(cols, 2)
' Write column to one line at last
Print #1, cols

answered Aug 11, 2021 at 7:21

doctorgu

6347 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Алексей Р · Accepted Answer · 2021-08-11 07:25:08Z

1

You can save it with Workbook.SaveAs as *.csv with requered options

Sub Test_Open()
    Dim strFilePath As String
    strFilePath = "c:\test\text.txt"
    
    With Workbooks.Open("c:\test\test.xlsx").Worksheets("test")
        .SaveAs strFilePath, xlCSV
        .Parent.Close False
    End With
End Sub

answered Aug 11, 2021 at 7:25

Алексей Р

7,6272 gold badges9 silver badges19 bronze badges

Comments

FaneDuru · Accepted Answer · 2021-08-11 07:44:04Z

1

Please, try the next code:

Sub Test_Open()
      Dim strFilePath As String, wb As Workbook, ws As Worksheet
      Dim i As Long, j As Long, txtArr, colArr, nrCol As Long, arrFin
      
      strFilePath = "C:\Users\test\text.txt"
     
      Set wb = Workbooks.Open("C:\Users\test.xlsx")
      Set ws = wb.Worksheets("test")
      txtArr = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(strFilePath, 1).ReadAll, vbCrLf)
      nrCol = UBound(Split(txtArr(0), ","))
      ReDim arrFin(1 To UBound(txtArr) - 6, 1 To nrCol)
      For i = 6 To UBound(txtArr)
            colArr = Split(txtArr(i), ",")
            For j = 0 To nrCol
                arrFin(i + 1, j + 1) = colArr(j)
            Next j
      Next
      ws.Range("A1").Resize(UBound(arrFin), UBound(arrFin, 2)).value = arrFin
End Sub

The code is not tested. If you would share the file you use, I will test it and eventually optimize something, if the case...

If something unclear, do not hesitate to ask for clarifications. I can comment the lines which look more difficult to be understood.

edited Aug 11, 2021 at 7:44

answered Aug 11, 2021 at 7:30

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

1 Comment

FaneDuru Over a year ago

@Heisenberg Didn't you find some time to test the received solutions? It is at least polite to send some feedback when somebody spends some time (only) in order to help you. I am not speaking only about my answer. It is good to test them and let us know what's wrong with the tested pieces of code...

Collectives™ on Stack Overflow

How to loop via columns in excel VBA

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related