how to link two different tables in two different workbook

[Toyota331]

I'm stuck in my project to copy data from table which is in another workbook. The copy-paste phase is done already but I need a way to select a column to "link" the copying rows. This is almost done but when I select a range where the paste is done I need to check if this selected column is in this range.
If the range was always the same there were no problem but I want to change the paste selection via mouse.(that is also done)
How it can be done?
Thanks already

 

[tbl]

An elegant way to do this is to treat the first Book as a database and use Microsoft ADO to access the data without opening the Book. This works best if you can give your data in the first book a Name.
Just to clarify...

Step 1 - Give your data range in Book 1 a name
Step 2 - Use ADO script to access this data and write it into the second book.

If this interests you I will post the code I use.

Richard

 

[Toyota331]

Thank you very much Richard.
Yes it does interest me.
please post the code.
thanks again

 

[tbl]

Have Fun - any questions just post ! One thing that this does really well is to access SHARED excel Workbooks without adding extra users.Dont't forget the reference to ADO

Sub GetDataFromExcel()
'GET DATA FROM NAMED WORKBOOK OVEN RANGE
GetDataFromClosedWorkbook "\\Pc5525\C:\ Test.xls", "NamedRange", Range("B5"), False
End Sub

Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
TargetRange As Range, IncludeFieldNames As Boolean)
' requires a reference to the Microsoft ActiveX Data Objects library
' if SourceRange is a range reference:
' this will return data from the first worksheet in SourceFile
' if SourceRange is a defined name reference:
' this will return data from any worksheet in SourceFile
' SourceRange must include the range headers
'
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
Dim TargetCell As Range, i As Integer
dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
"ReadOnly=1;DBQ=" & SourceFile
Set dbConnection = New ADODB.Connection

dbConnection.Mode = adModeRead ' read only connection

On Error GoTo InvalidInput
dbConnection.Open dbConnectionString ' open the database connection
'Check to see if the target Workbook
On Error Resume Next
Workbooks(Filename).Activate
ErrorNr = Err.Number
If Err.Number = 0 Then Workbooks(Filename).Close False
Err.Number = 0

Set rs = dbConnection.Execute("[" & SourceRange & "]")
Set TargetCell = TargetRange.Cells(1, 1)
If IncludeFieldNames Then
For i = 0 To rs.Fields.Count - 1
TargetCell.Offset(0, i).Formula = rs.Fields(i).name
Next i
Set TargetCell = TargetCell.Offset(1, 0)
End If
TargetCell.CopyFromRecordset rs
rs.Close
dbConnection.Close ' close the database connection
Set TargetCell = Nothing
Set rs = Nothing
Set dbConnection = Nothing
On Error GoTo 0
Exit Sub
InvalidInput:
MsgBox "The source file cannot be accessed!", _
vbExclamation, "Get data from Excel workbook"
End Sub

 

[Toyota331]

umh...
thanks for posting, but I´m not sure if that does the trick for me. I need to see both of workbooks because the result of comparing the data will be in both of these tables. Like this:

if the data in cell (x) have been changed --> backroundcolor of cell (x) turns yellow in new workbook.
if some row have been removed--> bcrndcol of the deleted row turns red in old wb. and if row have beed added -->bcrndcol of added row turns green in new wb.

this comparing part I have almost finished but I can´t go further on my code whitout the link column. I just don´t get it...

 

[tbl]

I can't really understand what you are trying to achieve with this.
Are you trying to track the changes occurring in the book ?
Why do you need two books if they are on the same computer ?
If they are not on the same computer why don't you use one shared book and let Microsoft track the changes for you ?
What do you mean by "link column" ?

Richard

 

[Toyota331]

ok lets try again.
first of all the books are in the same computer but I need to collect the changes because I get a new workbook and I have to compare if there is some changes comparing the old one. There is about 7000 rows in that workbook so I do not want to compare these by manual.

I try to explain a little better. so
I have a workbook where different data is hold.(in this time there is some electricity planing data)
When I get the updated workbook I have to check what have been changed and i have to do this all the time.

Now I need a macro to do this. So I figured that I make a form where I can: -choose the updated file.
-pick the right sheet
-pick a link column
-pick a start row .....

I have now done first two of those above. I just need a way to solve this linking problem. I mean this"link" column that the "link column" links the same rows so they are like some keycells I don´t now how to describe them. I just mean that when there are these link columns the comparing starts in rigth position because these updates can change columns and rows addresses (for example if i had PARTS column in D3 in old version and PARTS column in E4 in new one) how it can be done? or can it?

 

[tbl]

OK, I'll concentrate on your column problem.
Is this just a question of detecting the position of a column called PARTS in both sheets ?

 

[Toyota331]

no it was just an example.
This macro is used in many different kind of workbooks so it have to in the form where user picks the link column so it can be just about anythin of course it have to be in both of the workbooks.

 

[SkipVought]

I'd do the JOIN using MS Query faq68-5829

I'd use a UNION ALL query, STACKING the data from the two tables, and then compare the results

Select Key, Val, 'A'
From [Sheet1$]
UNION ALL
Select Key, Val, 'B'
From [Sheet2$]

After this, pretty simple analysis.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[Toyota331]

thanks Skip I give it a try tomorrow and post the results

 

[Toyota331]

I have now tryed to use the ms query but I don´t quite understand how I can use it in my code. It seems very handy tool but I have never even seen that so it is quite hard to understand how to use it..
Could someone please post an example code
I would be very grateful for that.

thanks

 

[SkipVought]

I posted the UNION ALL example above. Here's what that would do, assuming that sheet1 & sheet2 has data like this...
[tt]
Sheet1
Key Val
AA 4
BB 5
CC 6

Sheet2
Key Val
BB 5
CC 4
DD 7

Sheet3 - QueryResults
Key Val Expr1002
AA 4 A
BB 5 A
BB 5 B
CC 6 A
CC 4 B
DD 7 B
[/tt]
So the analysis would indicate that AA was on sheet1 but not in sheet2, BB is unchanged, CC's value decreased and DD is new.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[vbap]

Toyota331,

I read your statements ... they need more clarity. From what I can gather you are trying to say:

You have two worksheets with data to compare and you want to highlight changed data on both worksheets where necessary and update values in resulting worksheet so that you see what has changed.

In trying to understand your task we have to assume that there are unique key cell data in each row of both worksheets that is used to match up the unique row from one worksheet to the other worksheet. It is then that you can compare the remaining data cells of the row to see if they have changed. However, the ranges selected from both work sheets may have there corresponding columns in different column positions? Except of course the unique key cell data (key always in same column(s)). So you're saying you need to make sure that the data being compared is from the same column header label for the matched up row on both worksheets?

If this is what you're trying to do after finding the matching row, you'll need to fetch and compare the column headers for each cell to see if you are comparing apples with apples and oranges with oranges. It can be done but you'll have to write up a few routines using the FIND Method on the row range containing the column headers. This is assuming that there are no duplicate column header labels on a given worksheet.

It might make coding easier if first you arrange the columns by column header in one-to-one corresponding positions on both worksheets first. This also can be done programatically prior to the selecting the ranges on each worksheet. This way you don't have to compare column headers for each compared cell value because you would have already arrange the apples with apples and the oranges with oranges.

I'm confused about using the words copy-paste as opposed to just selecting the ranges of both worksheets to compare, and thereby, replacing the value programatically where necessary after being compared....or perhaps you just want to highlight it...or both.

Did I hit the jackpot in my assumption of what you're trying to do?