Import Multiple Text Files at once.

Hello all, Does anyone know how I can import multiple fixed width text files into excel at the same time? The following script works well but only allows me to import 1 file at a time. Thanks so much!!
Sub OpenTextFile()
 
Dim strFilename As String
    'Show the open dialog and pass the path to the selected
    'file to the String variable "strFilename"
    strFilename = Excel.Application.GetOpenFilename
    'If the user cancels, exit the routine.
    If strFilename = "False" Then Exit Sub
    'Display a message box to let us see what the variable was set to.
    MsgBox strFilename
 
Const xlFixedWidth = 2
 
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.OpenText _
    strFilename, , , xlFixedWidth, , , , , , , , , Array(Array(0, 1), Array(4, 1), _
 Array(9, 1), Array(23, 1), Array(30, 1), Array(33, 1), Array(42, 1), Array(48, 1),  _
Array(51, 1), Array(54, 1), Array(64, 1), Array(70, 1), Array(80, 1), Array(87, 1))
 
End Sub

Do you really need to select them?

The way I've done this in the past is have a directory setup to import from. Then use the DIR command in VBA to get each filenames and load them in turn.

If the structure of the files is known and not likely to change I tend to build an array to store it in and do any manipulation on it before writing it to the sheet

Nick's picture

take a look at

take a look at this:

excelexperts.com/VBA-Tips-List-Files-In-A-Folder

Nick's picture

Looping through files in a folder

Added some lines:
Sub OpenTextFile()
 
dim ContinueOpening as Boolean ' added

ContinueOpening = TRUE  ' added

while ContinueOpening  ' added

Dim strFilename As String
    'Show the open dialog and pass the path to the selected
    'file to the String variable "strFilename"
    strFilename = Excel.Application.GetOpenFilename
    'If the user cancels, exit the routine.
    If strFilename = "False" Then Exit Sub
    'Display a message box to let us see what the variable was set to.
    MsgBox strFilename
 
Const xlFixedWidth = 2
 
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.OpenText _
    strFilename, , , xlFixedWidth, , , , , , , , ,  _
Array(Array(0, 1), Array(4, 1), Array(9, 1), Array(23, 1),  _
Array(30, 1), Array(33, 1), Array(42, 1), Array(48, 1),  _
Array(51, 1), Array(54, 1), Array(64, 1), Array(70, 1),  _
Array(80, 1), Array(87, 1))
 
wend  ' added

End Sub