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

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" <JimBurkeinNovi@discussions.microsoft.com> wrote in message
news:94E8FB39-65F5-4FA5-BEA1-731202D89FCA@microsoft.com...
>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

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" <JimBurkeinNovi@discussions.microsoft.com> wrote in message
> news:94E8FB39-65F5-4FA5-BEA1-731202D89FCA@microsoft.com...
> >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
>
>
>

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" <JimBurkeinNovi@discussions.microsoft.com> wrote in message
news:4ADC32EE-940E-456E-8ECB-69E0EC6D638E@microsoft.com...
>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" <JimBurkeinNovi@discussions.microsoft.com> wrote in message
>> news:94E8FB39-65F5-4FA5-BEA1-731202D89FCA@microsoft.com...
>> >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
>>
>>
>>

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" <JimBurkeinNovi@discussions.microsoft.com> wrote in message
> news:4ADC32EE-940E-456E-8ECB-69E0EC6D638E@microsoft.com...
> >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" <JimBurkeinNovi@discussions.microsoft.com> wrote in message
> >> news:94E8FB39-65F5-4FA5-BEA1-731202D89FCA@microsoft.com...
> >> >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
> >>
> >>
> >>
>
>
>

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" <JimBurkeinNovi@discussions.microsoft.com> wrote in message news:94E8FB39-65F5-4FA5-BEA1-731202D89FCA@microsoft.com...
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