Assign recordset to variable

[BBert]

Good evening (european time!), Excellers,

One of my user-defined functions connects to excel with ADO to retrieve
some data (from a closed file). Can I assign the retrieved recordset to
a variable for further editing in another sub? I get an error message at
the end of the function.
******************************
Function Get_The_Dates(sFile)
Dim sConnStr As String, sQuery As String, sAddress As String
Dim oRst As ADODB.Recordset
Dim oCon As ADODB.Connection

sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sFile
& ";Extended Properties=Excel 8.0;"
sQuery = "SELECT * FROM [Sheet1$A9:B37]"

'Make Connection
Set oCon = New ADODB.Connection
oCon.Open sConnStr
'Retrieve the Data
Set oRst = New ADODB.Recordset
oRst.Open sQuery, oCon, adOpenForwardOnly, adLockReadOnly, adCmdText

Get_The_Dates = oRst
End Function
********************************
Another question I have is,
Can I use multiple ranges to compose the SELECT * FROM statement?
Something like
sQuery = "SELECT" & range("A1:B10") & "," & range("F1:G10") & " * FROM
[Sheet1$A1:J20]"

Thanks in advance,
Bert

 

[RB Smissaert]

Declare the Function like this:

Function Get_The_Dates(sFile) As ADODB.Recordset

and at the end of the function do:

Set Get_The_Dates = oRst


RBS

 

[BBert]

On Mon, 21 May 2007 20:44:46 +0100, RB Smissaert wrote...

Declare the Function like this:

Function Get_The_Dates(sFile) As ADODB.Recordset

and at the end of the function do:

Set Get_The_Dates = oRst

Oke, that did the trick. Thanks

But now a next question:
How can I dump (paste) those values (the recordset) stored in the
variabele directly into a sheet?

 

[RB Smissaert]

Cells(1).CopyFromRecordset oRst


RBS

On Mon, 21 May 2007 20:44:46 +0100, RB Smissaert wrote...


Oke, that did the trick. Thanks

But now a next question:
How can I dump (paste) those values (the recordset) stored in the
variabele directly into a sheet?

 

[BBert]

On Mon, 21 May 2007 22:34:31 +0100, RB Smissaert wrote...

Cells(1).CopyFromRecordset oRst

That's a NoNo, your syntaxis is correct. However my code doesn't
recognize it as a recordset. I think i messed up my function. It gives
an error when i use it in the sub. That's obvious because a function
just gives one value back to a single cell. Let me first give you the
code:

****************************
Option Explicit
Option Base 1

Const sPath = "F:\Excel_Files\test.xls"

Sub Get_The_Info()
Dim oDates
Dim y As Byte, z As Byte
Dim sC As String, sQ As String, sAddress As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

oDates = Get_The_Dates(sPath)
sC = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & _
";Extended Properties=Excel 8.0;"

sQ = "SELECT * FROM [Sheet1$C120]"

'Connect
Set cn = New ADODB.Connection
cn.Open sC
'Retrieve
Set rs = New ADODB.Recordset
rs.Open sQ, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
'Write
Application.ScreenUpdating = False
Range("A1").CopyFromRecordset oDates
Range("A1").Offset(0, 2).CopyFromRecordset rs
Application.ScreenUpdating = True
End Sub

Function Get_The_Dates(sFile) As ADODB.Recordset
Dim sConnStr As String, sQuery As String, sAddress As String
Dim oRst As ADODB.Recordset
Dim oCon As ADODB.Connection

sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sFile
& ";Extended Properties=Excel 8.0;"
sQuery = "SELECT * FROM [Sheet1$A1:B20]"

'Make Connection
Set oCon = New ADODB.Connection
oCon.Open sConnStr
'Retrieve the Data
Set oRst = New ADODB.Recordset
oRst.Open sQuery, oCon, adOpenForwardOnly, adLockReadOnly, adCmdText
Set Get_The_Dates = oRst

End Function
****************************

What i wanted to do is store the values of the recordset in the variable
oDates. Pass it through the sub and dump the result in the sheet. I know
the syntax to accomplish that is:
Cells(1).CopyFromRecordset.oRst. as you already pointed out. But that
gives an eeror message in the sub. There must be something wrong with
passing through the result of the Function to the Sub.

Thanks in Advance,
Bert