Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Multiple Text Files (Excel 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Multiple Text Files (Excel 2003)

    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

  3. #3
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Multiple Text Files (Excel 2003)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Multiple Text Files (Excel 2003)

    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?

  5. #5
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Multiple Text Files (Excel 2003)

    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.....

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Multiple Text Files (Excel 2003)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •