VBA passing cell values between workbooks

[luso]

Does anyone know is it possible to pass cell values between different workbooks, and using VBA resides in one workbook to manipulate and perform calculations in other workbook?
When I say pass value that means not to use the COPY AND PASTE methods.

ie. the active book is called mybook.xls and all the VBA codes are in it. And I would like all the calculation result to be stored on a file yourbook.xls

Workbook("yourbook.xls").Worksheets("there").Cells(i, j) = Workbook("mybook.xls").Worksheet("here").Cells(i, j)

I tried it won't work.
You can use that method to pass cell values in different sheets within the same book.
But I never see any book mentioned about between different books.

Thanks a lot.

 

[Andy Nguyen]

there are several ways to do this. I don't think it's as simple as the one you used. I read that you can do a vlookup, use ADO, etc...it depends on the version of Excel you use.

While not exactly what you try to do, here is a variant which i just test.
Put these 2 files under C:\ folder

The mybook.xls has no vba code. I put the code in yourbook.xls. The vba code will copy data from Sheet1 in mybook.xls and copy it into yourbook.xls Sheet 1. The example below will copy from A1: D10, you can change it to whatever.

Sub TestCopyFromClosedWB()
    CopyFromClosedWB "C:\mybook.xls", _
        "Sheet1", "A1:D10", Range("A1")
End Sub

Play a bit with it and change it accordingly. Your purpose is to get the data from an active sheet and write to a closed workbook. If I have time to play with it a bit, I'll update my method.

 

[luso]

Thanks Andy.
I see what are you doing, the code is in yourbook.xls, and it is also the destination file. It copies data from the original file, mybook.xls. Actually, I tried this before too.
You are doing it in a way of COPY FROM.

However, I have to do it other way around, COPY TO. My code has to be in the original file, and all the calculation must be done in it, mybook.xls. After the calculation is done, the data will be pass into the destination file, yourbook.xls.

In the original file, for each loop being executed I will get a result, then I will pull a cell value from the destination file, yourbook.xls, and add the result onto that cell value. Finally, I will pass the new result back to the same cell in the destination file. So it is not just simply COPY and PASTE. It is like within mybook.xls use VBA code to manipulate and preform calculation in the target file.

Thanks

 

[Andy Nguyen]

There are plenty of examples online if you google. Here is one example
http://www.excelkb.com/article.aspx?id=10107&cNode=1J5L2H