Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Importing specific sheet of multiple XLS files into one

This is a discussion on Importing specific sheet of multiple XLS files into one within the Excel Questions forums, part of the Question Forums category; Hello, I know similar questions have been asked before, but I couldnt work out how it is done. Basically I ...

  1. #1
    New Member
    Join Date
    Nov 2008
    Posts
    24

    Default Importing specific sheet of multiple XLS files into one

    Hello,

    I know similar questions have been asked before, but I couldnt work out how it is done.
    Basically I have several xls files with the same name, eg. "test" with an alternating number at the end. 1 - ....
    each of these workbooks contain several sheets but all books are the same just with different information on it. each workbook has got a summary sheet in it, i want to import all summary sheets into a single workbook called summary. but each sheet should be imported as in individual sheet, if that makes sense?
    Can anyone help me?
    thanks

  2. #2

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Importing specific sheet of multiple XLS files into one

    Hi,

    Try,

    Code:
    Sub test()
    Dim FilePath    As String, fName As String
    Dim aWB As Workbook, sWB As Workbook
    
    Set aWB = ActiveWorkbook
    FilePath = "C:\test\" 'change to suit
    fName = Dir(FilePath & "*.xls")
    Application.ScreenUpdating = 0
    Do While fName <> ""
        If fName <> aWB.Name Then
            Set sWB = Workbooks.Open(FileName:=FilePath & fName, UpdateLinks:=0)
            sWB.Sheets("Summary").Move after:=aWB.Sheets(aWB.Sheets.Count)
            sWB.Close False
            aWB.Sheets(aWB.Sheets.Count).Name = fName
        End If
        fName = Dir
    Loop
    Set sWB = Nothing: Set aWB = Nothing
    Application.ScreenUpdating = 1
    End Sub
    HTH

  3. #3
    New Member
    Join Date
    Nov 2008
    Posts
    24

    Default Re: Importing specific sheet of multiple XLS files into one

    This is the code I am using at the moment. This allows me to select individual files, but I would like to have all files automatically selected from specified folder.

    Sub Test()
    Dim wkbBook As Workbook
    Dim varFile As Variant
    On Error GoTo Fin
    varFile = Application.GetOpenFilename("All files,*.xls", 1, "Select", , False)
    If Not varFile <> False Then Exit Sub
    Set wkbBook = Workbooks.Open(varFile)
    Application.ScreenUpdating = False
    wkbBook.Sheets(Array("Summary")).Copy _
    Before:=ThisWorkbook.Worksheets(1)
    wkbBook.Close False
    Fin:
    Application.ScreenUpdating = True
    End Sub

  4. #4
    New Member
    Join Date
    Nov 2008
    Posts
    24

    Default Re: Importing specific sheet of multiple XLS files into one

    works...
    perfect... thank you very much.
    Regards

  5. #5
    New Member
    Join Date
    Nov 2008
    Posts
    24

    Default Re: Importing specific sheet of multiple XLS files into one

    sorry to ask again, i came across another problem. The sheet import works fine, but as i found out this morning, i cant seem to use the same summary spreadsheet because it tries to import existing files again and than complains about the name existing already. anyone know how i can only newly added files to the directory instead of importing all of them again?

  6. #6

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Importing specific sheet of multiple XLS files into one

    Hi,

    Try,

    Code:
    Sub test()
    Dim FilePath    As String, fName As String
    Dim aWB As Workbook, sWB As Workbook
    Dim FileList(), i   As Long, x, n   As Long
    Set aWB = ActiveWorkbook
    FilePath = "C:\test\" 'change to suit
    fName = Dir(FilePath & "*.xls")
    Application.ScreenUpdating = 0
    n = aWB.Sheets.Count
    ReDim FileList(1 To n)
    For i = 1 To n
        FileList(i) = aWB.Sheets(i).Name
    Next
    Do While fName <> ""
        x = Application.Match(fName, FileList, 0)
        If IsError(x) Then
            Set sWB = Workbooks.Open(Filename:=FilePath & fName, UpdateLinks:=0)
            sWB.Sheets("Summary").Move after:=aWB.Sheets(aWB.Sheets.Count)
            sWB.Close False
            aWB.Sheets(aWB.Sheets.Count).Name = fName
        End If
        fName = Dir
    Loop
    Set sWB = Nothing: Set aWB = Nothing
    Application.ScreenUpdating = 1
    End Sub
    HTH

  7. #7
    New Member
    Join Date
    Nov 2008
    Posts
    24

    Default Re: Importing specific sheet of multiple XLS files into one

    not importing any sheets now...
    what am I doing wrong?

  8. #8

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Importing specific sheet of multiple XLS files into one

    Hi,

    You don't have any new files.

  9. #9
    New Member
    Join Date
    Nov 2008
    Posts
    24

    Default Re: Importing specific sheet of multiple XLS files into one

    I do have a new file. but it is not importing it.
    and if i delete the old once it does not import them again

  10. #10
    New Member
    Join Date
    Nov 2008
    Posts
    24

    Default Re: Importing specific sheet of multiple XLS files into one

    thanks again... your code works perfect. was my own stupidity...
    i forgot a '\' in my filepath... stupid me...
    things happen if you been working for too long...
    thanks again for the help... absolutly superb...

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com