Excel VBA: how to format all as text in "text to columns" function

[Js Smith]

Hi all, I'm chasing my tail on this one.
I can use

Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True

and it works for a collection of CSV files.

If folks entered their data correctly on the CSV file there'd be 19 columns and

FieldInfo :=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2))

would work.

Folks rarely enter their data (text or numbers) correctly so I need the FieldInfo part to be dynamic, in case we end up with more than 19 columns.

The goal is to have all the columns formatted as text, in case they put a number where text belongs. I can't install programs on this work computer to upload a worksheet but there's a picture to illustrate what I'm talking about.

How would you accomplish this?

Thanks!

 

[Peter_SSs]

Not sure if there is a simpler, more direct way, but could this work for you?
Make the 'Const' line bigger than the number of columns that you would ever expect.

Sub TTC()
  Dim ColAry()
  Dim i As Long
 
  Const MaxNum As Long = 40
 
  ReDim ColAry(1 To MaxNum)
  For i = 1 To MaxNum
    ColAry(i) = Array(i, 2)
  Next i
  Columns("A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True, FieldInfo:=ColAry
End Sub

 

[Js Smith]

Not sure if there is a simpler, more direct way, but could this work for you.
Make the 'Const' line bigger than the number of columns that you would ever expect.

Sub TTC()
  Dim ColAry()
  Dim i As Long
 
  Const MaxNum As Long = 40
 
  ReDim ColAry(1 To MaxNum)
  For i = 1 To MaxNum
    ColAry(i) = Array(i, 2)
  Next i
  Columns("A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True, FieldInfo:=ColAry
End Sub

Thanks Peter, that's what I was trying to do exactly. That Const MaxNum was my missing concept in my attempt.
Hope you have a wonderful day!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.