Merge 2 excel sheets into 1

[dlusion]

Hello,

I am currently working on this project where I have to take 2 workbooks and merge 2 sheets together( Sheet1 on both workbooks are populated only). I have figured out a way to open both the workbook sheets and put info on one master excel sheet (This is a separate workbook that stores the info along with the vba code). What I'm running into is that it populates the info from workbook1 on the top then populates info from workbook2 underneath it. I have attached the way the sheets are populated below along with the code that I have so far. If anyone can let me know exactly what I should be doing that be grate help. I have windows2003 and the client might have win2002, I'm thinking it should be ok.

Workbook1.sheet1 (nameage.xls)
EE ID Name Age Reason Amt
2 B 12 123
1 A 23 54
3 C 55 785
5 E 56 45
4 D 29 477
6 F 45 456
7 G 44 2323

Workbook2.sheet2 (namejob.xls)
EE ID Name Job Reason
1 A Marketing 10
2 B Sales 20
3 C Data Entry 30
4 D Timer 50
5 E Banker 80


Expected Output in Master.xls after macro runs
EE ID Name Age Reason Amt Job
2 B 12 20 123 Sales
1 A 23 10 54 Marketing
3 C 55 30 785 Data Entry
5 E 56 80 45 Banker
4 D 29 50 477 Timer
6 F 45 456
7 G 44 2323



Sub merge()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant

SaveDriveDir = CurDir
MyPath = "C:\excel\"
ChDrive MyPath
ChDir MyPath


FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _
MultiSelect:=True)
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A110")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "A")


With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "A"). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Next
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

 

[SkipVought]

Hi,

Start with one of the workbooks on a new sheet.

The simplest way, without VBA, would be MS Query via Data/Get External Data/New database Query -- Excel Files

I have an FAQ explaining using MS Query to query nay Excel workbook including the activeworkbook.

An experienced user could get your combined data in less than 3 minutes.

Skip,

_[red][/red]

 

[dlusion]

Skip,

Thanks, I do have couple questions regarding if I do use MS Query since I have never used that before. The client I have is a bit difficult and wants everything done, all they want to do is to be able to view the output. 1) If I create the MS Query in new excel book and send that excel book (lets call it master.xls) to the client will they be able to take nameage.xls and namejob.xls put it in the same folder with master.xls and preview the results without doing anything? Or will they have to recreate the query on their end? 2) This client will be generating nameage.xls and namejob.xls every month will they have to redo the ms query (master.xls)?

The other problem I can run into would be that the client might not have ms query installed on their end, I will have to confirm with them. Once again thanks for you help

 

[SkipVought]

Ms Query is part of Excel, but it may not have been included in their installation. It can be added by reinstalling Office/Excel.

Now it becomes a bit more complex. You will need some code, but most of the code can be generated using the macro recorder.

Here's the workbook create flow...
[tt]
In the new worbook, saveas master.xls
query from Sheet1 to nameage.xls
query from Sheet2 to namejob.xls
query from Sheet3 to master.xls to join the data from Sheet1 & Sheet2
for each sheet, turn on the macro recorder (Store macro in This Worbook) and record editing each query - select in the resultset, Data/Get External Data/Edit Query/in the QBE Editor File/Return data to Excel
alt+F11 toggle between the VB Editor and the ActiveSheet.
If the Project Explorere is not open, ctr+R
Open the Module and observe the 3 recorded macros
modify the macros.
[/tt]
Your recorded code will look something like this...

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/21/2006 by Skip
'

'
    With Selection.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=d:\My Documents\30-60-90_mach_427_429_XSMN_2006-06-05.xls;DefaultDir=d:\My Documents;DriverId=790;MaxBuffer" _
        ), Array("Size=2048;PageTimeout=5;"))
        .CommandText = Array( _
        "SELECT `'0-30 pivot$'`.`1=Layup_2=All_3=Post`, `'0-30 pivot$'`.BC, `'0-30 pivot$'`.Description, `'0-30 pivot$'`.Earliest_Avail" & Chr(13) & "" & Chr(10) & "FROM `d:\My Documents\30-60-90_mach_427_429_XSMN_2006-06-05`.`'0-30 pivo" _
        , "t$'` `'0-30 pivot$'`")
        .Refresh BackgroundQuery:=False
    End With
End Sub

Clean it up to look like this...

Sub [b]GetNameData[/b]()
'
' Macro1 Macro
' Macro recorded 10/21/2006 by Skip
'

'[b]
    Dim sPath As String, sDB As String, sConn As Sting, sSQL As String
    
    sPath = ThisWorkbook.Path
    
    sDB = Split(ThisWorkbook.Name, ".")(0)
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=[red]" & sPath & "\" & sDB & "[/red].xls;"
    sConn = sConn & "DefaultDir=[red]" & sPath & "[/red];"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "SELECT "
    sSQL = sSQL & "  `'0-30 pivot$'`.`1=Layup_2=All_3=Post`"
    sSQL = sSQL & ", `'0-30 pivot$'`.BC"
    sSQL = sSQL & ", `'0-30 pivot$'`.Description"
    sSQL = sSQL & ", `'0-30 pivot$'`.Earliest_Avail"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM `[red]" & sPath & "\" & sDB & "[/red]`.`'0-30 pivot$'` `'0-30 pivot$'`"
[/b]    
    With [b]Sheet1[/b].QueryTable[b]s(1)[/b]
        .Connection = [b]sConn[/b]
        .CommandText = [b]sSQL[/b]
        .Refresh BackgroundQuery:=False
    End With
End Sub

After you get this done, we can talk about how you want the queries triggered.



Skip,

_[red][/red]

 

[dlusion]

Skip,

Thanks, let me give this a try. I will let you know of the outcome

 

[dlusion]

Skip,

Thanks, it works great with the MS Query!!!