Import Multiple Text Files (Excel 2003)

[Steve Kearley]

I have "borrowed" some code that will allow me to import one txt file and do some formatting on it. Normally, when I doing this process, I have to run it 5 or 6 times to import 5 or 6 different files. Is there a way to modify this code so that I can select multiple files and then have the code process each txt file one at a time? When I download the files from the mainframe, I save them all to the same directory. In other words, my thinking is that when this code prompts me to pick a single file, that I could just pick multiple files, then continue with the importing.


Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename("All files (*.*), *.*", , "Select the Check Register Report you want to open.")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Workbooks.OpenText Filename:=FName, _
Origin:=xlWindows, StartRow:=8, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(11, 1), Array(22, 1), Array(53, 1), Array(65, 1), Array(74, 1), _
Array(79, 1))
ActiveCell.SpecialCells(xlLastCell).Select
Range(Selection, Cells(1)).Select
(etc.)

Any help or guidance is appreciated

[HansV]

GetOpenFilename has a multiselect option. If it is True, GetOpenFilename returns a variant array.

Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String
Dim i As Integer

FName = Application.GetOpenFilename("All files (*.*), *.*", , _
"Select the Check Register Report you want to open.", , True)
If VarType(FName) = vbBoolean Then
MsgBox "You didn't select a file"
Exit Sub
End If

For i = LBound(FName) To UBound(FName)
Workbooks.OpenText Filename:=FName(i), _
Origin:=xlWindows, StartRow:=8, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(11, 1), Array(22, 1), Array(53, 1), Array(65, 1), Array(74, 1), _
Array(79, 1))
' rest of the code goes here
...
Next i
End Sub

[Steve Kearley]

Hans,
Thanks for the quick response. Works like a charm!
Is it possible to add code (always more!) that would append these files together? These files are like 5 different check registers in a txt format that I open, clean up with this code, then I manually take the date from, say RegisterB and append it to RegisterA, and RegisterC to the bottom of that one--etc. I've some experience when the data is in the same file, but not from different files..

Anyway, this solution is great--Thanks again

[HansV]

The method will be the similar to the code needed to append ranges from the same workbook. Is there a column that is guaranteed to be filled for every used row?

[Steve Kearley]

Yes, once the existing code is complete, column A through F are all guaranteed to have data in them.

In this attempt to get an append process to work, I will have to add a column and put in a checkbook name (which may have to be manually handled either way). But what might work is that I insert a column A on each sheet and fill column A with the Sheet's name for all existing rows of data . . . Hmmm.....

[HansV]

Once you have inserted a blank column A, you can use column B to determine the last used row:

Dim lngLastRow As Long
lngLastRow = Range("B65536").End(xlUp).Row

sets lngLastRow to the row number of the last used row. You can then fill column A:

Range("A1:A" & lngLastRow).Value = ActiveSheet.Name

You can also use lngLastRow to determine how many rows to copy.