Referencing data from another workbook
 

I need to reference cells from another workbook in VBA code. I don't want to
open the workbook, just reference the data from it. Any help is greatly
appreciated. Thanks.

Jim B

Referencing data from another workbook
 

Hi Jim

Do you want to create formula link

Use this then
='C:\Data\[ron.xls]Sheet1'!$A$1

If there are more workbooks you can use a macro
http://www.rondebruin.nl/summary2.htm

Or copy only the values
http://www.rondebruin.nl/copy7.htm

Or ADO
http://www.rondebruin.nl/ado.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jim Burke in Novi" wrote in message
...
I need to reference cells from another workbook in VBA code. I don't want to
open the workbook, just reference the data from it. Any help is greatly
appreciated. Thanks.

Jim B

Referencing data from another workbook
 

I used the following code:

Dim wkbk As Excel.Workbook
dim wksData As Excel.Worksheet

Set wkbk = Workbooks.Open(myFileName)
Set wksData = wkbk.Worksheets("DataSheet")
....other code that references the remote worksheet data...

As soon as the Open method is executed, it opens the workbook in Excel, and
the code that comes after it doesn't get executed. I tried using
Application.ScreenUpdating = false, but this had no effect. My code works
fine as long as I'm referncing a local worksheet. As soon as I do the above
to reference another workbook, the other workbook just opens up.


"Ron de Bruin" wrote:

Hi Jim

Do you want to create formula link

Use this then
='C:\Data\[ron.xls]Sheet1'!$A$1

If there are more workbooks you can use a macro
http://www.rondebruin.nl/summary2.htm

Or copy only the values
http://www.rondebruin.nl/copy7.htm

Or ADO
http://www.rondebruin.nl/ado.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jim Burke in Novi" wrote in message
...
I need to reference cells from another workbook in VBA code. I don't want to
open the workbook, just reference the data from it. Any help is greatly
appreciated. Thanks.

Jim B

Referencing data from another workbook
 

Give this link a try:

» Getting Values From A Closed Workbook using VBA in Microsoft Excel

https://www.exceltip.com/st/Getting_...Excel/357.html


--
Mark Ivey
"Jim Burke in Novi" wrote in message ...
I need to reference cells from another workbook in VBA code. I don't want to
open the workbook, just reference the data from it. Any help is greatly
appreciated. Thanks.

Jim B

Referencing data from another workbook
 

Hi Jim

I don't want to open the workbook
You do it in your code ?

Have you try the example code I posted ?

Show your complete code

If you want to open the workbooks see
http://www.rondebruin.nl/copy3.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jim Burke in Novi" wrote in message
...
I used the following code:

Dim wkbk As Excel.Workbook
dim wksData As Excel.Worksheet

Set wkbk = Workbooks.Open(myFileName)
Set wksData = wkbk.Worksheets("DataSheet")
...other code that references the remote worksheet data...

As soon as the Open method is executed, it opens the workbook in Excel, and
the code that comes after it doesn't get executed. I tried using
Application.ScreenUpdating = false, but this had no effect. My code works
fine as long as I'm referncing a local worksheet. As soon as I do the above
to reference another workbook, the other workbook just opens up.


"Ron de Bruin" wrote:

Hi Jim

Do you want to create formula link

Use this then
='C:\Data\[ron.xls]Sheet1'!$A$1

If there are more workbooks you can use a macro
http://www.rondebruin.nl/summary2.htm

Or copy only the values
http://www.rondebruin.nl/copy7.htm

Or ADO
http://www.rondebruin.nl/ado.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jim Burke in Novi" wrote in message
...
I need to reference cells from another workbook in VBA code. I don't want to
open the workbook, just reference the data from it. Any help is greatly
appreciated. Thanks.

Jim B

Referencing data from another workbook
 

WHoops - programming error on my part. I fixed my logic error and everythings
working fine, and would have been right from the start without my error.
Thanks.

"Ron de Bruin" wrote:

Hi Jim

I don't want to open the workbook
You do it in your code ?

Have you try the example code I posted ?

Show your complete code

If you want to open the workbooks see
http://www.rondebruin.nl/copy3.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jim Burke in Novi" wrote in message
...
I used the following code:

Dim wkbk As Excel.Workbook
dim wksData As Excel.Worksheet

Set wkbk = Workbooks.Open(myFileName)
Set wksData = wkbk.Worksheets("DataSheet")
...other code that references the remote worksheet data...

As soon as the Open method is executed, it opens the workbook in Excel, and
the code that comes after it doesn't get executed. I tried using
Application.ScreenUpdating = false, but this had no effect. My code works
fine as long as I'm referncing a local worksheet. As soon as I do the above
to reference another workbook, the other workbook just opens up.


"Ron de Bruin" wrote:

Hi Jim

Do you want to create formula link

Use this then
='C:\Data\[ron.xls]Sheet1'!$A$1

If there are more workbooks you can use a macro
http://www.rondebruin.nl/summary2.htm

Or copy only the values
http://www.rondebruin.nl/copy7.htm

Or ADO
http://www.rondebruin.nl/ado.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jim Burke in Novi" wrote in message
...
I need to reference cells from another workbook in VBA code. I don't want to
open the workbook, just reference the data from it. Any help is greatly
appreciated. Thanks.

Jim B