Importing specific sheet of multiple XLS files into one

T

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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

A
  • Question Question
Macro to import the same sheets names are repeated across files.
Replies
0
Views
57
Amer Omar
A
E
  • Solved
Copy a certain set of data from one sheet to another depending on the date typed in
2
Replies
10
Views
497
myall_blues
myall_blues
R
  • Solved
VBA - Combine worksheets from multiple workbooks
Replies
5
Views
346
rachel06
R
L
  • Question Question
replace data for multiple sheets from source file to destination file based on back up(date,time)
Replies
1
Views
167
leap out
L
S
  • Question Question
Use formula to name table columns
Replies
4
Views
376
sjrpdx
S

Forum statistics

Threads
1,223,832
Messages
6,174,905
Members
452,590
Latest member
CraiginColorado

Share this page

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