VBA Match Function Syntax Issue

I have workbook A with Table A and Workbook B with Table B. I am looping through the rows of Table A in workbook A, selecting a cell value whose row number in Table B of Workbook B is what I want to find ( I need to pick out other columns from that row in Table B). The Match function works fine if I enter it in a cell as a Formula in Table A :

=MATCH("FindThis",'[WorkbookB.xlsx]Sheet1'!A1:A1000, 0)

However if I try this in VBA as:

Please Login or Register  to view this content.

I get a syntax error, "Expected List Separator or )". I have tried several variations of this , but continue to get syntax errors. Any ideas?

I previously defined wkbkB as a Workbook set to WorkBook B's path, and activated Workbook B

Thanks in advance for any help or time spent helping.

Perhaps using your wkbkB variable:

Please Login or Register  to view this content.

Another approach:

Please Login or Register  to view this content.