Vista - Search Sample for Oracle-to-Excel extraction with CopyFromRecordset

I would like to extract all records from an Oracle database to an Excelsheet.
How do I do this in detail?

After googling around I found so far the following code snippet (see bottom).
However the "core" statement is still not clear to me.
How do I assign the current recordset to the next free line ?
How do I write the line indicated below with "***" ?

Shouldn't be there a loop to iterate through all records?


Function TotalAmount()
Dim objConn As ADODB.Connection
Dim objRec As ADODB.Recordset
strQuery = "SELECT * FROM mytab WHERE field1=123;"
If ThisMap.MySQL_connection(objConn, "localhost", "3306", "root", "admin") Then
Set objRec = New ADODB.Recordset
objRec.Open strQuery, objConn, adOpenForwardOnly, adLockReadOnly
"nextfreeline" = CopyFromRecordset objRec (***)
Else
MsgBox "Error Connection-String"
End If
objRec.Close
Set objRec = Nothing
End Function

Keith

Keith Clark schrieb:The .CopyFromRecordset method

"Copies the contents of an ADO or DAO Recordset object onto
a worksheet, beginning at the upper-left corner of the specified range."

This range is the object the method is to be called on. I *think*
"nextfreeline" is meant as placeholder for the intended target range
(the = assignment? looks fishy though - given a suitable oRange
object the replacement should result in

oRange.CopyFromRecordset objRec

The CopyFromRecordset method copies all records (and the Field names, I
recall) into the worksheet.

My preferred method is to loop, simply because I can analyse the incoming
data and transform or format on the fly:


---------------------------------------------------------------
SUB Extract()
Dim objConn As ADODB.Connection
Dim objRec As ADODB.Recordset
strQuery = "SELECT * FROM mytab WHERE field1=123;"
If ThisMap.MySQL_connection(objConn, "localhost", "3306", "root",
"admin") Then
Set objRec = New ADODB.Recordset
objRec.Open strQuery, objConn, adOpenForwardOnly, adLockReadOnly
'-------------------
application.screenupdating=false 'Turn off screen activity -
improves performance
r=2 'initialise the row variable
RstToWs(objRec,0)
'-------------------
Else
MsgBox "Error Connection-String"
End If
objRec.Close
Set objRec = Nothing
End SUB


Sub RstToWs(Rs As ADODB.Recordset, colIdx As Long)
Dim lcounter As Long, rc As Long
Application.ScreenUpdating = False
rc = Rs.RecordCount
If r = 2 Then 'Start of the extract - put field names in Row1
For c = colIdx To colIdx + Rs.Fields.Count - 1
Cells(1, c + 1) = Rs.Fields(c - colIdx).Name
Next c
End If
lcounter = 1
Do Until Rs.EOF
Application.StatusBar = lcounter & "/" & rc
For c = colIdx To colIdx + Rs.Fields.Count - 1
' Debug.Print Rs.Fields(c).Name
Cells(r, c + 1) = Trim(Rs.Fields(c - colIdx).Value)
Next c
r = r + 1
Rs.MoveNext
lcounter = lcounter + 1
Loop
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

I've jemmied my code into your sample - I hope it works!

HTH
Bob



"Keith Clark" <k.clark@xxxxxx> wrote in message
news:4978c983$0$30233$9b4e6d93@xxxxxx-online.net...-------------------
r=2 'initialise the row variable
rsttows(objRec,0)
-------------------