The other day I was perusing the various search strings that people use to get to these pages.
And it turns out that there are a lot of questions on macros that merge several files into a single Excel workbook.
My Visual Basic (for Applications) is a bit rusty, but using the invaluable Register Macro functionality in Excel,
it's pretty easy to figure out how such a macro should look
(BTW: if you're looking for this in Excel 2007, go to the View tab - the Macros button is the last one on the right;
click on the down arrow - there you'll find the Register Macro menu).
Option Explicit
Sub OpenAll()
Dim LDir, LFile, LDest As String
Dim LCount, LIndex As Integer
LDir = "c:\_test\"
LFile = Dir(LDir & "*.xls")
LDest = "Zeszyt0.xls"
LCount = Workbooks(LDest).Sheets.Count
While LFile <> ""
If UCase(LFile) <> UCase(LDest) Then
Workbooks.Open Filename:=(LDir & LFile)
For LIndex = 1 To Workbooks(LFile).Sheets.Count
Workbooks(LFile).Sheets(1).Move After:=Workbooks(LDest).Sheets(LCount)
LCount = LCount + 1
Next LIndex
End If
LFile = Dir()
Wend
End Sub
Now, the above code has embedded strings for the source folder and file mask, and the destination workbook name -
I guess I'll leave it to the reader, as an exercise, to read those strings from input boxes
or to initialize them any other way. :-)
And a couple words of explanation:
-
In the above example, we're assuming that the destination workbook is already open
(otherwise, Workbooks(LDest) will generate an error;
though you can use Workbooks.Open to open the destination workbook).
-
The first call to Dir() returns the first file that fits the mask given as the parameter;
consecutive calls to Dir(), without any parameters, return consecutive files that fit the original mask;
if no more files are present, Dir() returns an empty string.
-
Workbooks.Open opens the file and tries to guess the format the file is in.
If you're having problems with this, register a macro when opening the problematic file manually
and see what the actual parameters Excel defines there; also, you may want to look at the other file functions
in place of Open, for instance OpenText (that has additional parameters for opening all kinds of text files).
-
Before Workbooks.Open we check whether we're not trying to reopen the destination workbook
(if that was saved earlier to the source directory).
We're using the UCase function (change letters to upper case) to correctly compare file names
that differ only in case (where, for instance, LFile <> LDest would fail).
-
Sheets(1).Move moves the first sheet of the opened file to the destination workbook after the specified sheet
(by using LCount, sheets are added to the end of the destination workbook in the same order that Dir() returns their respective files).
Obviously, you may want to loop through all sheets of the source file or change the destination position.
-
We call Sheets(1).Move as many times as there are sheets in the source workbook.
Thanks to that, all sheets are moved to the destination workbook and the source workbooks
is closed automatically by Excel.
If we wouldn't move all sheets (or, for instance, copied sheets),
the source workbook would stay open -
then you can use the Close function to close it:
Workbooks(LFile).Close false
false above tells Excel to close the file without saving changes
(thus with all original sheets) and without asking the user to save changes.
HTH
Top
|
Texts
