Import Multiple Text Files from Folder to Multiple Worksheets formatted as TEXT

[DarleneS]

Hi –

I’m a SQL coder and just started coding in VBA so I don’t have all the tricks I need yet. I used the code in the below link to create a macro to import multiple text files from a folder into an Excel workbook on multiple sheets. This code is excellent and does what I need, except I am having a small issue that I need help with. When I import my text files into Excel they are populating as "General" format causing my leading zeros in some fields to be dropped. I created a text based template to open upon Excel launch, but it didn't work with this macro. Is there something I can add to my code to have my text files import to Excel as all “Text” and not “General”? Thanks for any help anyone can offer.

Import Multiple Text Files from a folder to multiple worksheets

Sub Import_Text_To_Sheets()
Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Application.ScreenUpdating = False

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"
End If

x = 1
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
Cells(2, 1).Select
ActiveWindow.FreezePanes = True
Cells.EntireColumn.AutoFit
wkbTemp.Close (False)
x = x + 1

While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
Cells(2, 1).Select
ActiveWindow.FreezePanes = True
Cells.EntireColumn.AutoFit
End With
x = x + 1

Wend
Application.ScreenUpdating = True
End Sub

 

[hernantorres23]

Hi, please try to use Power Query to do it this job, put all data in the same place and then you can filter what you want

 

[DarleneS]

Hi, please try to use Power Query to do it this job, put all data in the same place and then you can filter what you want

I don't think Power Query will work for what I need. All the research I have done on Power Query appends all the files into the same worksheet, I need each file to be on a different worksheet.