Oracle Objects for OLE and Excel

[JCScoobyRS]

I installed the Oracle Objects for OLE so that I could pull information from my Oracle database and insert it into my spreadsheet. The samples that come with it provide a way to get info from the database and insert it into the spreadsheet and I copied it verbatim and made changes only where needed. It worked but now I want to insert the information into column AK1 instead of A1. Can someone look at my code and tell me where to make changes to get what I need? Thanks, Jeremy

Sub EmpData()

'Declare variables as objects
Dim OraSession As Object
Dim OraDatabase As Object
Dim EmpDynaset As Object

Dim flds() As Object
Dim fldcount As Integer

Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("PKDEMO", "pkdemo/pkdemo", 0&)
Set EmpDynaset = OraDatabase.CreateDynaset("select CU_NAME from CU", 0&)

Range("AK1:AK100").Select
Selection.ClearContents

'Declare and create an object for each column.
'This will reduce objects references and speed
'up your application.
fldcount = EmpDynaset.Fields.Count
ReDim flds(0 To fldcount - 1)
For Colnum = 0 To fldcount - 1
Set flds(Colnum) = EmpDynaset.Fields(Colnum)
Next


'Insert Column Headings
For Colnum = 0 To EmpDynaset.Fields.Count - 1
ActiveSheet.Cells(1, Colnum + 1) = flds(Colnum).Name
Next

'Display Data
For Rownum = 2 To EmpDynaset.RecordCount + 1
For Colnum = 0 To fldcount - 1
ActiveSheet.Cells(Rownum, Colnum + 1) = flds(Colnum).Value
Next
EmpDynaset.DbMoveNext
Next

Range("AK1:AK1").Select
End Sub

 

[JCScoobyRS]

Nevermind, I got it. Where you see Colnum + 1, you will just change the 1 to the number to the number of the column you want to insert into. If you wanted to insert into column E, it would be Colnum + 5. Thanks, Jeremy

 

[XL-Dennis]

Jeremy,

Glad it worked out and that You was kindly to provide the whole procedure, especially
this part:

<pre>
'Declare and create an object for each column.
'This will reduce objects references and speed
'up your application.
fldcount = EmpDynaset.Fields.Count
ReDim flds(0 To fldcount - 1)
For Colnum = 0 To fldcount - 1
Set flds(Colnum) = EmpDynaset.Fields(Colnum)
Next
</pre>

Kind regards,
Dennis

 

[JCScoobyRS]

No problem Dennis. Do you know how I can name all of the information that I just brought in from Oracle so that I can create a dynamic validation list from the information? Thanks, Jeremy

 

[XL-Dennis]

This message was edited by XL-Dennis on 2002-11-14 17:29