Excel VBA Importing text file into excel and convert a specific column as text-CodePudding

I am currently working on a task and that is just simply importing a text file into excel via VBA.

The problem is that i would like to write a VBA code which convert the variable "Number" as text format only and no change for the others variables when importing the text file, and therefore, the first digit "0" would be remained after importing into excel.

Below is the example, but the text file actually contains more than 100 variables, recording Macro seems not working for that many variables.

The text file:

2021-02-02|Investor|Approved|2020-09-25 15:54:58|0312475|asdfasfsdf

2021-02-02|Investor|Approved|2020-04-23 23:01:30|0312475|asdfasfsdf

2021-02-02|Medium|Approved|2020-09-28 16:49:48|0312475|asdfasfsdf

Please kindly have a look and advise. Many Thanks.

As i would like to have the code which importing a text file with more than 100 variables but only converting the variable "Number" as text. Thanks.

Workbooks.OpenText Filename:=fdata & intxt1, _
    Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, _
    Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 2), Array(6, 1)),
    TrailingMinusNumbers:=True, _
    Local:=True

CodePudding user response：

If you need to open as text only a specific column, it should be enough to create an array up to that specific column, like FieldInfo. If that specific column is, let us say, the 100 one, it should be more difficult to write such an array of arrays. In such a case you can use the next code, automatically building the necessary array:

Sub importTextFormatOneCol()
   Dim fileToOpen As String, arr(), i As Long, colsNo As Long, colString As Long
   
   colsNo = 100: colString = 5
   ReDim arr(colsNo - 1)
   For i = 0 To UBound(arr)
        Select Case i
            Case colString - 1 'here it can be an enumeration of cases (-1 because of base zero array)
            'Case 4, 10, 21 ' in such a chase, the columns 5, 11, 22 will be formatted As Text
                arr(i) = Array(i   1, 2)
            Case Else
                arr(i) = Array(i   1, 1)
        End Select
   Next i
   Workbooks.OpenText fileName:=fdata & intxt1, _
          origin:=932, startRow:=1, DataType:=xlDelimited, _
         TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="|", FieldInfo:=arr()
End Sub

The code can be improved to automatically determine the real number of columns in the text file to be open. Not complicated, but it does not make the object of the question.