Skip Headers

Oracle® Objects for OLE Developer's Guide
10g Release 1 (10.1)

Part Number B10118-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Master Index
Master Index
Go to Feedback page

Using OO4O Automation with Excel

To use OLE Automation with MS Excel to insert Oracle data into a worksheet, perform the following steps:

  1. Start Excel and create a new worksheet.
  2. Use the Macro options in the Tools menu to create and edit new macros for manipulating the Oracle data.

    Text description of the illustration exmacros.gif follows

    Text description of the illustration exmacros.gif

  3. Enter the Visual Basic code for the macros to create and access an Oracle dynaset. For example, see the EmpData() and ClearData() procedures (macros) that follow:
Sub EmpData()

'Declare variables

Dim OraSession As OraSession

Dim OraDatabase As OraDatabase

Dim EmpDynaset As OraDynaset

Dim flds() As OraField

Dim fldcount As Integer

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

Set OraDatabase = OraSession.OpenDatabase("ExampleDB",

"scott/tiger", 0&)

Set EmpDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)



'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)


'Insert Column Headings

For Colnum = 0 To EmpDynaset.Fields.Count - 1

ActiveSheet.Cells(1, Colnum + 1) = flds(Colnum).Name


'Display Data

For Rownum = 2 To EmpDynaset.RecordCount + 1

For Colnum = 0 To fldcount - 1

ActiveSheet.Cells(Rownum, Colnum + 1) = flds(Colnum).Value





End Sub

Sub ClearData()




End Sub

4. Assign the procedures (macros) that were created, such as EmpData() and ClearData(), to command buttons in the Worksheet for easy access. When you select the buttons, you can clear and refresh the data in the worksheet.

Text description of the illustration exsheet.gif follows

Text description of the illustration exsheet.gif