How can I Merge Excel Files?
Jamie
I have already posted this query on the worksheet section
and I have been directed to here.
Does anyone have any ideas how I could merge a number of
excel files (approx 60) into one spreadsheet? I would need
to do this once or twice a month so cutting and pasting or
dragging and dropping would be too time consuming. I
haven't really got much VB knowledge. I am using Excel 97.
When posting on the worksheets section I was advised to
answer the following when posting in this section so here
goes.
Q) Do the files exist in one folder--with no other excel
files in that folder or do they share their location (so
human interaction (to select them) would be required).
A)The files are all in one folder. The folder contains
only the files that I need merged.
Q) Does merging just mean copying the worksheets into a
new workbook so that that workbook has lots of sheets? Or
does merging mean copying each worksheet in each of the 60
workbooks into one giant worksheet?
A)I would need the files copied into a workbook with lots
of different sheets.
Q)Or is there one worksheet in each workbook that needs to
be copied. If yes to this, does it have a special name? Or
is it just the left most in the workbook?
A)There is one worksheet in every file. Each worksheet is
simply sheet 1.
Q)Is there some order of merging that must be followed?
(or can you sort it afterwards??)
A)I would like to have the files in the order that they
are listed in the folder.
Any Help would be greatly appreciated.
Thanks in advance.
Jamie
Steve
workbook? Are the individual workbooks to be updated in some way at
the end of the process, or discarded?
Ajay Askoolum
contrived and lengthy - best done as a VB project.
For your problem, it is not clear whether you want to
bring in all the data into one sheet or each sheet of data
from the source files into a separatesheet in the same
workbook.
You might try this:
1. Open a new workbook.
2. Click Data + Get External Data + New DataBase Query and
select Excel Files* from the available list.
3. Select a workbook. If your Table options have System
Tables checked, then you'll see a list of the sheets in
the workbook you have selected. If you don't see this,
check in the System Tables option in Table Options.
4. Select Return Data To Excel, select a New Worksheet.
That's the first sheet done!
5. Repeat 3 - 4 until you've got all worksheets in the
same workbook.
Repeat 3 - 5 for every workbook.
>.
>
Jamie
out to a number of people and sending and viewing 60
different files is not very user friendly.
There are to be no updates carried out.
>.
>
Dave Peterson
is changeable. Just click on a title when you're viewing details. (But since
it was a wish, not a requirement....)
This usually works for me:
Option Explicit
Sub testme()
Application.ScreenUpdating = False
Dim myFiles() As String
Dim i As Integer
Dim myFile As String
Dim myFolder As String
Dim newWks As Worksheet
Dim oRow As Long
Dim tempWkbk As Workbook
myFolder = "c:\my documents\excel"
If Right(myFolder, 1) <> "\" Then
myFolder = myFolder & "\"
End If
myFile = Dir(myFolder & "*.xls")
If myFile = "" Then
MsgBox "no excel files found"
Exit Sub
End If
Do While myFile <> ""
i = i + 1
ReDim Preserve myFiles(1 To i)
myFiles(i) = myFile
myFile = Dir()
Loop
Set newWks = Workbooks.Add(1).Worksheets(1)
newWks.Name = "GiantIndex " & Format(Now, "yyyy_mm_dd__hh_mm_ss")
oRow = 0
For i = LBound(myFiles) To UBound(myFiles)
oRow = oRow + 1
newWks.Cells(oRow, 1).Value = "'" & myFiles(i)
With Application
.StatusBar = "Processing: " & i & " : " & myFiles(i)
.DisplayAlerts = False
.EnableEvents = False
End With
Set tempWkbk = Workbooks.Open(Filename:=myFiles(i), _
ReadOnly:=True)
tempWkbk.Worksheets(1).Copy _
after:=newWks.Parent.Worksheets(newWks.Parent.Worksheets.Count)
newWks.Cells(oRow, 2).Value = "'" & ActiveSheet.Name
tempWkbk.Close savechanges:=False
With Application
.EnableEvents = True
.DisplayAlerts = True
End With
Next i
With newWks
.UsedRange.Columns.AutoFit
.Select
End With
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub
Change the folder name to match yours (in fact, I'd create a test folder with
10-15 files in it to see if/how it works!)
The only time I've seen stuff like this fail is when there are too many
different formats in the workbook. Do you do that kind of stuff?
To try this out:
start a new workbook
Hit alt-f11 to see the VBE (where macros live)
then hit ctrl-R to see the project explorer
right click on your project (should look like: VBAProject (book1))
select insert module.
paste it there
Then hit alt-F11 to get back to excel.
Then tools|macro|macros...
select the macro (rename it to something more meaningful than Testme.)
click on Run.
David McRitchie has some notes for getting started with macros at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
--
Dave Peterson
ec3...@msn.com
Steve
go here: http://www.imagemakerslongisland.com/excel/jamie.htm
I recommend using the add-in, but the workbook will work just as well.
If you want to, you can take the source code on the site and create
your own add-in.
Let me know if you get it!
Steve