PDA

View Full Version : Solved: Working with two workbooks



londresw
09-18-2008, 01:34 PM
Hi,

I'm trying to use information in a second workbook, "C:\test.xls". So for example, I want to check what info is in cell A1 of the first workbook, "C:\workbook1.xls" put that string into a variable and send it to the second worbook, 'test', and then return a value depending on what that string returns through code in the second workbook.

So for example,

Workbook 1
A1 = sausages
take 'sausages' to test workbook,
search column A for 'sausages' and then, when found in say.. A22, return the value in B22 of test workbook, e.g. 'germany' then return 'germany' to workbook 1 to be used as part of the program, in a variable.

Any ideas?

I'm confident I can right the look-up code but am struggling in the moving of variables from one workbook to another.

Thanks in advance for any help..

Scott

:banghead:

RonMcK
09-18-2008, 02:44 PM
Is the second workbook is the data containing 'sausages' part of a table? Do you want to do this using worksheet fomulas or using VBA code? Might there be multiple instances of 'sausages' in that other worksheet and what do you want to do if there are?

Thanks,

londresw
09-18-2008, 02:57 PM
I want to do this using VBA code and there wouldn't be multiple instances of sausages - all unique values only. i just want to pass the strings from one workbook to another - i'm sure the lookup bit i can write reasonably easily, it's scuppering me getting the info from one book to another?

Bob Phillips
09-19-2008, 02:18 AM
Set variables to eaxh workbook and use them



Set wb1 = Workbooks.Open("C:\workbook1.xls")
Set ws1 = wb1.Worksheets(1)

Set wb2 = Workbooks.Open("C:\test.xls")
Set ws2 = wb2.Worksheets(1)