Home » Forums » Open Source Learning Center » Question and Answer

Import Multiple Text Files at once.

Submitted by kat50 on 16 August, 2009 - 13:30
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
‹ VBA Code to prevent changes in an Excel Workbook Auto Copy cells from one sheet to another workbook sheet ›
»

Do you really need to select them?

Submitted by Anonymous on 15 January, 2010 - 12:19.

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

Submitted by Nick on 28 December, 2011 - 22:09.

take a look at this:

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

»
Nick's picture

Looping through files in a folder

Submitted by Nick on 16 August, 2009 - 20:27.
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
»