Creating Links

[visible2you]

Hi,

I am working to link data between two excel work books, say BookM.xls, BookC1.xls, BookC2.xls, BookC3.xls

If Column A, C, E of BookM is changed or added, then

Column B, D, F of BookC1,
Column B, D, F of BookC2,
Column B, D, F of BookC3

gets same "values" in their every respective Cells through a Refresh button available in a sheet of BookC1, BookC2, BookC3.

But not reverse because Column A, C, E of BookM will be manually inputted.

Excel version: 2003 max
Macro preferred: No
VBA Preferred: Yes

[Kenneth Hobs]

It is working as designed seems to me. What did you expect?

Your BookM is the master so why would it be a slave?

[sukumar.vb]

[vba]
Private Sub Workbook_Open()
Range("A1").Select
ActiveCell.FormulaR1C1 = "=[BOOKM.xls]Sheet1!RC"
Selection.Copy
Range("B:B").Select
ActiveSheet.Paste
Range("D").Select
ActiveSheet.Paste
Range("F:F").Select
ActiveSheet.Paste
End Sub
[/vba]
Please copy above code into Bookc1, Bookc2 and Bookc3.
Please mark this thread as solved, if you were looking for this code.

[Aussiebear]

Hi sukumar.vb, I have removed your statement indicating that Ken could not answer this one, as it was factually incorrect. In post #2 Ken has answered the OP.

[visible2you]

It is working as designed seems to me. What did you expect?

Your BookM is the master so why would it be a slave?

BookM is master for only few columns as aforesaid.

Therefore, depending on requirement, BookM has to be Slave.

Sukumar.vb : Can you please help me understand your response? I was looking to sync few columns of worksheet of BookC1, C2 and C3.

[Kenneth Hobs]

Why not just create the links manually? Then you won't need a "refresh" button.

IF you just want the values at Open or running the routine via a button, that can be done using sukumar.vb's method.

[visible2you]

I am looking for some more response(s).
There already exists data in rows of BookC1, C2 and C3.
Say, A2, C2 and E2 of BookM contain 1, 2 and 3 respectively.
Then, B2, D2 and C2 of BookC1 should also contain 1,2, and 3 respectively, if it did not exist in Bookc1.
So, before upating it should search for values in respective cells and under "AND Operator", so that if all aforesaid cell values exist altogether in certain row of either Bookc1 or c2 or c3, it will "match" next row.

For Next or similar loop is preferred.
I don't want to use R1C1 notation.

[visible2you]

I have used following function in Sheet1 of Bookc2.xls :-

=OR(ISNA(MATCH(B2,'C:\Users\Hello\Documents\[BOOKM.xls]Sheet1'!B:B,0)),ISNA(MATCH(D2,'C:\Users\Hello\Documents\[BOOKM.xls]Sheet1'!D,0)),ISNA(MATCH(F2,'C:\Users\Hello\Documents\[BOOKM.xls]Sheet1'!F:F,0)))

Even if I use AND operator, I find cell value to be TRUE, which means values don't match there.

Please refer attachment of BookC2.xls.

Could you help me with VBA equivalent?

[visible2you]

Where are you, friend? Can someone please inform him about this?

[sukumar.vb]

You can refer to this code: -

[VBA]

GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "A1:B21", ActiveCell, False
GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "MyDataRange", Range ("B3"), True

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
On Error GoTo InvalidInput
dbConnection.Open dbConnectionString ' open the database connection
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 or source range is invalid!", _
vbExclamation, "Get data from closed workbook"
End Sub

[/VBA]

[visible2you]

Thanks, but, I got an error on following: -

dbConnection As ADODB.Connection

Error:

User defined type not defined.

[sukumar.vb]

The macro example assumes that your VBA project has added a reference to the ADO object library.

You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft ActiveX Data Objects x.x Object Library.

Use ADO if you can choose between ADO and DAO for data import or export.

[sukumar.vb]

I would request for this thread to be closed, as I had answered your question.

[visible2you]

My question was not answered completely. I think User 2139: XLD and my real LORD can only answer this. I was delighted to read his appraisal.

I was not able to see such glory anywhere else. That is my opinion.

[Aussiebear]

My question was not answered completely.

I have no doubt that you will enjoying talking to yourself elsewhere rather than continuing to mislead the members of this forum. Multiple identities are a clear breech of the rules and for that you are suspended until the Admin staff see fit to change this.