Modules are in ADODB_Merge_Sample workbook.
#HOW TO MERGE WORKBOOKS IN EXCEL 2013 HOW TO#
Sheet1.Cells(1, iCols + 1).Value = rst.Fields(iCols).NameĪpplication.Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic)ĪctiveSheet.DisplayPageBreaks = Not (isOn)Įnd SubIt should be pretty self explanatory how to use it (it uses FileDialog to pick a folder). 'Read through record set and return headers to row1 Set rst = CreateObject("ADODB.Recordset") StrQuery = strQuery & " UNION ALL SELECT * FROM IN '" & x(i) & "' 'Excel 12.0 Xml '" "Extended Properties=""Excel 12.0 Xml HDR=Yes """ ConnectionString = "Data Source=" & firstFile & " " & _ Just note that imported data will be in text format ' Or if you don't know if data type is consistent in a column ' Add IMEX=1 at end of connection string, if you know some column is alphanumeric mixed Set cn = CreateObject("ADODB.Connection") MsgBox "No file found or folder not selected!!", vbCritical In standard module following codes are used.įunction to return array of full file path for each file in folder.ĭim strQuery As String, fisrtFile As String This got me thinking on how to use single connection to query all workbooks in a folder.īy using "IN" clause you can UNION ALL different workbooks using connection to single workbook. Since I'm not too familiar with manipulating MS Query using VBA I went with ACE.OLEDBīut initial code required connection to be opened for each workbook and was bit messy. This left MS Query with parameter passed on using VBA or ADO using ACE.OLEDB. All the Excel files open on your PC will be displayed in the drop-down. On the Move or Copy pop-up, click the To book drop-down. Then, right-click the worksheet you want to merge and select Move or Copy.
Once all the files are open, maximize the first one you want to merge. PowerQuery wasn't available to all users of this workbook. You have to open all the Excel files to be able to combine them into one. However, in this instance environment had mix of Excel 2010, 20.
Normally, I'd use PowerQuery to merge data. It was requested that source workbooks should not be opened. Needed to merge Sheet1 of multiple workbooks (number of workbooks can vary). I recently did a project and thought I'd share the concept.
0 kommentar(er)
