Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Import multi text into one workbook (Excel 2002)

    Hi all,

    This basically does what I want it to do, it opens a group of selected files and opens the text files as workbooks. I am trying to copy all the data in the open workbooks and move the sheets into the base book that has the code. What I been able to do is save each file as a xls workbook, but I haven't been able to copy sheet(1) from each book into the same book that has the code. I could have as many as 15 notepad files to import, and would like it if I didn't have to have 15 notepad file and additional 15 workbooks in the same folder. Does some one know a thread that can point me in the right direction.?

    Thanks

    Public Sub UseFileDialogOpen()
    Dim sfilepath As String, i As Integer, snap As String
    sfilepath = "c:New folder"
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Set fd = Application.FileDialog(msoFileDialogOpen)
    With fd
    .AllowMultiSelect = True
    .InitialFileName = sfilepath
    .Filters.Clear
    .Filters.Add "LGV BATTERY REPORT", "*.btt"
    .Show
    End With
    For lngCount = 1 To fd.SelectedItems.Count
    snap = fd.SelectedItems(lngCount)
    Workbooks.OpenText Filename:=snap, _
    Origin:=437, _
    StartRow:=1, _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=False, Semicolon:=False, _
    Comma:=True, Space:=False, _
    Other:=False, OtherChar:="-", _
    FieldInfo:=Array( _
    Array(1, 1), _
    Array(2, 1), _
    Array(3, 1), _
    Array(4, 1), _
    Array(5, 1), _
    Array(6, 1), _
    Array(7, 1), _
    Array(8, 1), _
    Array(9, 1)), _
    TrailingMinusNumbers:=True

    Next lngCount
    err:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True

    End Sub

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

    Re: Import multi text into one workbook (Excel 2002)

    Try the attached version.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import multi text into one workbook (Excel 2002)

    When you import a text file it creates a new Workbook and imports the data into that. As part of your import code, you should insert a line that will move the worksheet containing the imported data into the original workbook (containing the code).

    The appropriate line of code will look similar to: Sheets("Sheet1").Move Before:=Workbooks("OriginalWBName").Sheets(1)
    You will need to modify the names or set variables as needed to generalize the line. This must be inserted into the loop after the import has taken place. eg:
    ...
    Array(6, 1), _
    Array(7, 1), _
    Array(8, 1), _
    Array(9, 1)), _
    TrailingMinusNumbers:=True
    Sheets("Sheet1").Move Before:=Workbooks("OriginalWBName").Sheets(1)
    ...
    Regards,
    Rudi

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Import multi text into one workbook (Excel 2002)

    Thanks Hans and Rudi.....works great!

Posting Permissions

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