Importing specific sheet of multiple XLS files into one

teffstein

New Member
Joined
Nov 17, 2008
Messages
24
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,728
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top