-1

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:

DataDate|Model|Status|Status Timestamp |Number |xxx

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
kkkakaho
  • 1
  • 4
  • Does this answer your question? [How to change Format of a Cell to Text using VBA](https://stackoverflow.com/questions/8265350/how-to-change-format-of-a-cell-to-text-using-vba) – phooBarred Oct 20 '21 at 05:42
  • Just `Application.Workbooks.OpenText "filepath", DataType:=xlDelimited, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 2), Array(6, 1))` works for me. – Raymond Wu Oct 20 '21 at 06:45
  • Thanks, but how should i write the code with the file contains more than 100 variables? – kkkakaho Oct 20 '21 at 06:52
  • Please see FaneDuru's answer and incorporate it for your purpose. Note: To ping someone in your comment, use @name so that the person will receive a notification of your comment. – Raymond Wu Oct 20 '21 at 07:23

1 Answers1

1

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.

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • OMG. That is exactly what i want to do, cuz i was wondering how to solve the array part of the FieldInfo. Thank you so much. This helps me a lots. Wish you all the best. – kkkakaho Oct 20 '21 at 07:40