XL2K: How to reference workbooks that aren't open?
 

I can't seem to find any functions I can use to retrieve data from cells in
workbooks that aren't open.

I'm familiar with links, DDE, OLE, etc. But, I only know how to link with
spreadsheet formulas rather than VBA code.

Can someone point me in the right direction?

TIA!
--
-Mike Mertes
Airtron, Tampa Bay

How to reference workbooks that aren't open?
 

Hi mike

Open the workbooks is also fast and you have more control.
http://www.rondebruin.nl/copy3.htm

See also the ADO page
http://www.rondebruin.nl/ado.htm

John Walkenbach have some information on this page
http://www.j-walk.com/ss/excel/tips/tip82.htm




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


"Mike Mertes" wrote in message ...
I can't seem to find any functions I can use to retrieve data from cells in
workbooks that aren't open.

I'm familiar with links, DDE, OLE, etc. But, I only know how to link with
spreadsheet formulas rather than VBA code.

Can someone point me in the right direction?

TIA!
--
-Mike Mertes
Airtron, Tampa Bay

How to reference workbooks that aren't open?
 

Thanks for your quick response, Ron.

I'm not able to visit your page from work because of Websense: "Reason: The
Websense category "Freeware and Software Download" is filtered." (I guess
the admins at HQ don't want their technology specialists to be able to
download anything useful :P )

So I'll quickly elaborate he
In actuality, the only reason I wanted to reference data in unopened
workbooks is because I was under the impression that opening each (while
easily codable,) would be very slow. Can I just turn off screen updating to
speed the process?

Also, I'll bookmark the links you gave me for future study. (As I always
do.)

Thanks again. :)
--
-Mike Mertes
Airtron, Tampa Bay
"Ron de Bruin" wrote in message
...
Hi mike

Open the workbooks is also fast and you have more control.
http://www.rondebruin.nl/copy3.htm

See also the ADO page
http://www.rondebruin.nl/ado.htm

John Walkenbach have some information on this page
http://www.j-walk.com/ss/excel/tips/tip82.htm




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


"Mike Mertes" wrote in message
...
I can't seem to find any functions I can use to retrieve data from cells
in
workbooks that aren't open.

I'm familiar with links, DDE, OLE, etc. But, I only know how to link
with
spreadsheet formulas rather than VBA code.

Can someone point me in the right direction?

TIA!
--
-Mike Mertes
Airtron, Tampa Bay

How to reference workbooks that aren't open?
 

Ron..

re the Array_Sort procedure on your ado page ...

far as i can tell it isn't called from any of the other procs..

BUT when you post a sort routine... wouldn't it make more
sense to post a QuickSort rather than this ugly/slow "Shell" sort?

AND when you'd need to sort it would even make more sense
to let SQL do the sorting...

<grin



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ron de Bruin wrote :

See also the ADO page
http://www.rondebruin.nl/ado.htm

How to reference workbooks that aren't open?
 

Hi Keepitcool

far as i can tell it isn't called from any of the other procs..
This sub use it
Sub GetData_Example3()


BUT when you post a sort routine... wouldn't it make more
sense to post a QuickSort rather than this ugly/slow "Shell" sort?

AND when you'd need to sort it would even make more sense
to let SQL do the sorting...

It is working and that's important to me.
I learn everyday try to understand the things i see and read.

My Webpage is my Help file<g
I now more about growing flowers then about SQL


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


"keepITcool" wrote in message ft.com...

Ron..

re the Array_Sort procedure on your ado page ...

far as i can tell it isn't called from any of the other procs..

BUT when you post a sort routine... wouldn't it make more
sense to post a QuickSort rather than this ugly/slow "Shell" sort?

AND when you'd need to sort it would even make more sense
to let SQL do the sorting...

<grin



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ron de Bruin wrote :

See also the ADO page
http://www.rondebruin.nl/ado.htm

XL2K: How to reference workbooks that aren't open?
 

"Mike Mertes" wrote:

I can't seem to find any functions I can use to retrieve data from cells in
workbooks that aren't open.


Hi Mike:

You can connect with the following ADO Procedure.


Private Sub CommandButton2_Click()

On Error GoTo BadTrip

Dim DB_Name As String
Dim DB_CONNECT_STRING As String

'To use ADO objects in an application add a reference
'to the ADO component. From the VBA window select
'Tools/References< check the box
' "Microsoft ActiveX Data Objects 2.x Library"

'You should fully quality the path to your file

DB_Name = ("C:\Documents and Settings\") _
& ("The Cat Man\My Documents\Test.xls")

DB_CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0" _
& ";Data Source=" & DB_Name _
& ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"

'Create the connection
Dim Cnn As New ADODB.Connection
Set Cnn = New Connection
Cnn.Open DB_CONNECT_STRING

'Test to see if we are connected
If Cnn.State = adStateOpen Then
MsgBox "Welcome to! " & DB_Name, vbInformation, _
"Good Luck TK"
Else
MsgBox "Sorry. No Data today."
End If

'Create the recordset
Dim Rs As ADODB.Recordset
Set Rs = New Recordset

'Determines what records to show
Dim strSQL As String
strSQL = "Select * from [Sheet1$]"

'Retreive the records
Rs.CursorLocation = adUseClient
Rs.Open strSQL, Cnn, adOpenStatic, adLockBatchOptimistic

'Copy the records to the worksheet
Worksheets("Sheet2").Range("A1").CopyFromRecordset Rs

'Close the connection
Cnn.Close
Set Cnn = Nothing

'Destroy the Recordset
Set Rs = Nothing

Exit Sub

BadTrip:
MsgBox "Procedure Failed"
Cnn.Close
Set Cnn = Nothing

End Sub


Good Luck
TK

How to reference workbooks that aren't open?
 

"Mike Mertes" wrote ...

I'm not able to visit your page from work because of Websense: "Reason: The
Websense category "Freeware and Software Download" is filtered." (I guess
the admins at HQ don't want their technology specialists to be able to
download anything useful :P )

Hopefully MSDN is not forbidden:

http://support.microsoft.com/default...;en-us;Q257819

Jamie.

--