Automatically import .txt files into separate sheets in Excel

[jomaal318]

I have daily .txt reports that I would like to import into excel as separate sheets in the same workbook. My problem is that 3 of the 4 files have fields that are numbers that are 20 digit account numbers and import as scientific notation. I want them import as text. I am currently using the code below. Can it be modified?

If I use Power Query I cannot create separate sheets within one workbook.

Thank you in advance,
John

Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String

On Error GoTo ErrHandler
Application.ScreenUpdating = False

sDelimiter = "|"

FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt), *.txt", _
MultiSelect:=True, Title:="Text Files to Open")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

x = 1
Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close (False)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:="|"
x = x + 1

While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:=sDelimiter
End With
x = x + 1
Wend

ExitHandler:
Application.ScreenUpdating = True
Set wkbAll = Nothing
Set wkbTemp = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

 

[John_w]

My problem is that 3 of the 4 files have fields that are numbers that are 20 digit account numbers and import as scientific notation. I want them import as text.

Specify the TextToColumns FieldInfo argument:

    Dim fields As Variant
    
    fields = Array(Array(1, xlGeneralFormat), Array(2, xlGeneralFormat), Array(3, xlGeneralFormat), Array(4, xlTextFormat))

    wkbAll.Worksheets(x).Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        Other:=True, OtherChar:="|", FieldInfo:=fields

In the above example the fields array specifies the formats for 4 columns, with column 4 (20 digit account number) formatted as text.

You would need some code to identify the 3 files containing 20 digit account numbers and apply the extra FieldInfo:=fields argument shown above.