Another combining multiple workbooks to a single one!

calljj · Jan 30th 2004

I have 12 workbooks, and in each workbook there are five sheets of data (say sheets called alpha, bravo, charlie e.t.c), all data in sheets have common column titles. Is there a fast way of collating all this into 5 sheets in a single workbook? i.e listing all Alpha data on a single sheet, all bravo data on a single sheet ...etc etc.

Thanks in advance

James

yjoshi · May 31st 2004

You can do this by using New Database Query, using excel file.

I tried it with 6 sheets Jan -> Jun and it did work for me.

I have recorded the macro of the procedure...

The key here is to change the query to "SELECT * from ...... Union All Select * from .... "

see the code below......

Code

Sub Macro1()
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=D:\Yogendra\Excel\Excel_Database\consolidate\Apr.xls;DefaultDir=D:\Yogendra\Excel\Excel_Database\consolidat" _
        ), Array("e;DriverId=790;MaxBufferSize=2048;PageTimeout=5;")), Destination:= _
        Range("A1"))
        .CommandText = Array( _
        "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `D:\Yogendra\Excel\Excel_Database\consolidate\Jan`.`C$` `C$`" & Chr(13) & "" & Chr(10) & "union all" & Chr(13) & "" & Chr(10) & "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `D:\Yogendra\Excel\Excel_Database\consolidate\Feb`.`C$` `C$`" & Chr(13) & "" & Chr(10) & "union all" & Chr(13) & "" & Chr(10) & "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `D:\Yogen" _
        , _
        "dra\Excel\Excel_Database\consolidate\Mar`.`C$` `C$`" & Chr(13) & "" & Chr(10) & "union all" & Chr(13) & "" & Chr(10) & "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `D:\Yogendra\Excel\Excel_Database\consolidate\Apr`.`C$` `C$`" & Chr(13) & "" & Chr(10) & "union all" & Chr(13) & "" & Chr(10) & "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `D:\Yogendra\Excel\Excel_Database" _
        , _
        "\consolidate\May`.`C$` `C$`" & Chr(13) & "" & Chr(10) & "union all" & Chr(13) & "" & Chr(10) & "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `D:\Yogendra\Excel\Excel_Database\consolidate\Jun`.`C$` `C$`" _
        )
        .Name = "Query from Excel Files"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With


End Sub

Display More

As told earlier, this is the code generated automatically.... if you want you can also try recording one and then modify as you wish

Hope this helps. Let me know if this does not work.

Another combining multiple workbooks to a single one!

Participate now!

Share