Mike Mertes said:
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
)