Desperately Searching for Macros to Combine Excel Files - Googler Beware!
I've come across more than macro on the web that was designed to merge data in multiple Excel files. Some of them work, others don't. It's important to keep in mind that such macros often have unadvertised limitations. See the Access based macro described here:
and copied here:
Option Compare Database
Option Explicit
Function DoImport()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True
' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Documents and Settings\myName\My Documents\Access Test\"
' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames
' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile
strFile = Dir()
Loop
End Function
On one project I got this macro to work only by changing the blnHasFieldNames setting to 'False' and then creating a table with columns named, "F1"; "F2"; "F3"; and so forth - several more columns than had data in the source Excel files. Each of these Access columns was set to 'Memo'. (and not Text which has a 255 character limit.). I primed the first Excel file in my review set with a row with more than 255 characters in each cell. Sure enough, the macro imported data from my set of 600 Excel files into the Access table. However when I exported the data to Excel, and ran LEN formulas to search for instances of cells with exactly 255 characters, and I found quite a few, and I found cut-off text. The problem was traced back to the Access table. Changing the text format of memo field to rich text format did not solve the problem when I reimported with the macro. I discovered online confirmation for my problems: "when appending an Excel file to an existing table, even when columns are formatted and saved as memo fields, that if all 8 of the first rows in the excel file are less than 256 chars, Access assumes you actually meant to specify text, thus truncating the remaining rows after 255 chars." See: http://stackoverflow.com/questions/15249753/truncated-data-when-importing-from-excel-to-an-access-memo-field
I overcame this problem by turning to a different macro, which worked well but also had its limitations. See the code and instructions from http://www.oaultimate.com/office/merge-multiple-excel-files-into-a-single-spreadsheet-ms-excel-2007.html
Close all working excel files so you can focus only on merging files.
•On MS Excel, create new spreadsheet by simply pressing CTRL+N.
•And open Microsoft Visual Basic editor by pressing ALT+F11, you’ll see a blank text editor.
•Now open by doubleclicking ThisWorkBook on the left sheet menu.
•Paste the following macros code:
Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("D:\change\to\excel\files\path\here")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub
Change the folder as mentioned on comment on the macros code
•Change also column start reference to suit your need (usually first row used by column header, so i used A2 as start point).
•For example to start merging all files from column "B" row "1".
•Change "IV" only if you have files using column wider than column "IV". Basically, it will try to copy values on all available columns. If you notice the latest column on new worksheet is "IV", it is the default available column on until your columns growth more than that.
Range("B1:IV" & Range("B65536").End(xlUp).Row).Copy
•If everything configured already, press "F5″ or click on play icon to run the code (RunSub). You’ll see working progress on left sheet menu.
•If all done, you can now switch to worksheet to see the result.
The trouble with this macro is that crashes Excel when I try to use it if with more than 300 files. Or is it when the amount of total data in a set of Excel files is as little as 10 MB? In any event, macro Googler beware! Macros posted to message boards and random sites do not always work as advertised.