A VBA to Get Value from a Closed Workbook based on criteria

[jansjane]

Hello Masters of VBA,

After an extensive research and failed to find anything similar on Google, I finally came here to ask you this question.

I would like to copy a value from workbook A to B, while A is closed.
In workbook A, the name of Column A is FID, Column B is ASSETID.
In Workbook B, Column K's name is NEAR_FID.
I would like a VBA to look into values in Column K (NEAR_FID) of workbook B and find the matching value in Column A (FID) of workbook A, and then copy the value (ASSETID - next cell to the right) of Column B of workbook A into Workbook B.

Your time on this is greatly appreciated!
Ken

 

[Marc L]

Hi !

For a beginner easier is to open workbook A …

Advanced way is to use ADODB to reach closed workbook,
see within this forum or all over the web …
​

 

[jansjane]

Sure Marc. Please. I can open Workbook A every time.

 

[Marc L]

Result in column M of workbook B attachment seems irrelevant !

As within K2:K10 there is the same value,
I can not understand why in column M it's not the same result …

Notice on my side an easy VLOOKUP worksheet formula works
for a closed workbook (as well using this formula in a code) …
​

 

[jansjane]

Hi Marc, it might be a mistake as I manually entered them. They should be same if col M is same.

I tried to use ADODB and came up with the following code in my Workbook B. but nothing happens. Would you please help?

Sub test()
'Add reference for Microsoft Activex Data Objects Library-Microsoft Activex Data Objects 6.1 Library before running the macro
Application.ScreenUpdating = False
Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'You need to update the below line with your desired path
cpath$ = ThisWorkbook.Path & "C:\Users\Ken\Desktop\Pothole Process\A.xls"
rsconn$ = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & cpath & "';" & _
            "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"";"
Conn.Open rsconn
 
With Sheet1
' You need to update the field names in the below line as per your original file
                i& = .Cells(Rows.Count, 2).End(xlUp).Row + 1
              strSQL$ = "SELECT [FID] FROM [Sheet1$]"
                On Error Resume Next
                rs.Open strSQL, Conn, adOpenStatic, adLockOptimistic, adCmdText
                ActiveSheet.Range("C" & i).CopyFromRecordset rs
              
End With
Set rs = Nothing
Set Conn = Nothing
Application.ScreenUpdating = True
End Sub

 

[Marc L]

Check your cpath variable ! Remove ThisWorkbook.Path & …

As I wrote, easier with just a VLOOKUP formula,
even by code with less than ten codelines !
​

 

[jansjane]

Would you please kindly help with the vlookup by code? I really don't know what that is. Thank you so much,

 

[Chihiro]

http://www.exceltrick.com/formulas_macros/vlookup-in-vba/

 

[Marc L]

Would you please kindly help with the vlookup by code?

It's exactly the same worksheet formula
as every Excel beginner must know ‼

In next demonstration, whatever if workbook A is closed or
opened but in same path as workbook B (where is code) :​

Sub Demo1()
    With ThisWorkbook.Worksheets(1).Cells(1).CurrentRegion.Rows
        With .Range("M2:M" & .Count)
             .Formula = "=VLOOKUP($K2,'" & ThisWorkbook.Path & "\[A.xls]Road_Segment_20160928'!$A$2:$B$150,2,FALSE)"
             .Formula = .Value
        End With
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Nebu]

Hi:

This is the correct code for what you are looking for, if you are going with ADODB option.

Sub test()
'Add reference for Microsoft Activex Data Objects Library-Microsoft Activex Data Objects 6.1 Library before running the macro
Application.ScreenUpdating = False
Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'You need to update the below line with your desired path
cpath$ = ThisWorkbook.Path & "\A" & ".xls"
rsconn$ = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & cpath & "';" & _
            "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"";"
Conn.Open rsconn
With Sheet1
' You need to update the field names in the below line as per your original file
              i& = .Cells(Rows.Count, 1).End(xlUp).Row
              For k& = 2 To i
                    strSQL$ = "SELECT [ASSETID] FROM [Road_Segment_20160928$] Where [FID]=" & Sheet1.Range("K" & k)
                    On Error Resume Next
                    rs.Open strSQL, Conn, adOpenStatic, adLockOptimistic, adCmdText
                    ActiveSheet.Range("N" & k).CopyFromRecordset rs
                    Set rs = Nothing
              Next
            
End With

Set Conn = Nothing
Application.ScreenUpdating = True
End Sub

Note:

I am not sure how you have come up with different Asset Ids for same FID.
Thanks

 

[jansjane]

Thank you so much Marc. It worked except the error message came up with "Code execution has been interrupted", when I click on debug. ".Formula=.value" highlighte. any idea?

Thank you!

 

[Marc L]

No issue on my side with your attachment : did you try it ?!
Any error in column M of workbook B ?

On my side Nebu's code works too, on yours ?
​

 

[jansjane]

Yes Marc. I used it. I can live with the error. There is no error in M. I am not quite sure.

 

[Chihiro]

Try solution in the thread.
http://stackoverflow.com/questions/...y-with-message-code-execution-has-been-halted

 

[shiva_reshs]

It's exactly the same worksheet formula
as every Excel beginner must know ‼

In next demonstration, whatever if workbook A is closed or
opened but in same path as workbook B (where is code) :​

Sub Demo1()
    With ThisWorkbook.Worksheets(1).Cells(1).CurrentRegion.Rows
        With .Range("M2:M" & .Count)
             .Formula = "=VLOOKUP($K2,'" & ThisWorkbook.Path & "\[A.xls]Road_Segment_20160928'!$A$2:$B$150,2,FALSE)"
             .Formula = .Value
        End With
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

I tried using this, but when i execute, it ask me to choose the file. cant we do it without promt?

 

[Marc L]

Maybe try first the formula manually in order to create a link …​