Importing specific sheet of multiple XLS files into one

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Importing specific sheet of multiple XLS files into one

  1. #1
    New Member
    Join Date
    Nov 2008
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

User Tag List

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