How to use Excel with an Oracle Database

	The first thing you want to do is to find out which database you want to
connect to.  When you installed Oracle you probably also installed the Oracle Net8
Easy Config program.  This program keeps track of all of the known Oracle database
connections you have.  Typically a default service is provided for your local database
system.  This default is usually listed as 'BEQ-LOCAL.WORLD'.  You can use this service
or create a new one.  To create a new one simply ADD a sevice using Easy Config.  The
type of connection you want to make it is 'Bequeath(local database)'.  Then when preparing
your Excel connection use whatever name you called your new connection.



	Once you have the name to be used for the connection open Excel(version 5.0 or up).
You will see a new "Workbook" open in front of you.  If not just create one.  Go to the 
following menu links:
	Tools ==> Macro ==> Visual Basic Editor


Or type in "alt+f11".  This will start up the Visual Basic Editor.  Once in the Editor
you should see a project listing on the left hand side containing you new workbook and 
the "Sheet" objects it contains.



The next thing you will want to do is to design a form that will interact with your Excel
workbook.  To do this go to the following menu link
	Insert ==> UserForm
On the left the focus will change to your new UserForm and a new "toolbox" window will
open.  In the toolbox will be the different items you can add to your form.  Just Select
what options you want to build and click and drag over the form to create them.  When you
create a new item, such as a button, the panel on your left will change at the bottom to
show the properties of the new item.  I would recommend renaming each item to a descriptive
name, like retreiveButton.  You can also change the caption of the button to read something
like, "Get Data".



Once you have designed your form you then want that form to do something.  For each button
you want to specify a new query to your database.  I did this by Inserting a new "Module,"
and typed all of my code for each button there.  Then to assign that macro to a button,
right click on the button and select "view code".  This will bring up the coding section 
for the form. and you will see a "Sub" called buttonName_Clicked().  This is where you type
the name of the macro you defined to work with that button.  So it might look like this:
	Sub retreiveButton_Clicked()
		retreive
	End Sub

You can place the macro definition right in the button code if you want to.  But by using
a module you make your program a little more reusable.  So when you are done it might look
like this:



Into Macro programming.  A few things you will want to know:
	You will need to instantiate your Oracle session, database, and dynaset as Objects.
		This is done as follows:  

					  Dim OraSession As Object
					  Dim OraDatabase As Object
					  Dim GradeDynaset As Object

	You will then need to instantiate any other items you will use.  I used a PLSQL
		statement that needed to be a string so I called:

					  Dim PLSQLStmt1 As String

		I also used an Object to hold the DataField Names of my table:

					  Dim ColNames As Object

	Next you need to open your session, create your connection(using the TNS name you
		got previously), and use your SQL statement to get your data:

				Set OraSession = CreateObject("OracleInProcServer.XOraSession")
  				Set OraDatabase = OraSession.OpenDatabase("beq-local.world", "name/password", 0&)
  				Set GradeDynaset = OraDatabase.DbCreateDynaset("SQL string", 0&)

		Note the "SQL string" can be replaced by a String variable like PLSQLStmt1.
	Now that you have your data in GradeDynaset you can output the FieldNames using
		a loop mechanism that accesses differing cells of a worksheet:

			Set ColNames = GradeDynaset.Fields
  
 			 For icols = 1 To ColNames.Count
 			   Worksheets("Sheet1").Cells(1, (icols + 1)).Value = ColNames(icols - 1).Name
			  Next

	Then all that is left for you to do is copy the data from GradeDynaset to the ClipBoard
		(which requires an Object type) and then paste that to a specified cell.

			GradeDynaset.CopyToClipboard -1
 			Sheets("newsheet").Select
			Range("B2").Select
			ActiveSheet.Paste

	Another macro you will want to create is called Auto_Open().  This macro will run
		as soon as the workbook is opened in Excel.  I would recommend the following:

			Sub Auto_Open()
				UserForm1.Show
			End Sub

The following links will take you to the code of the example I gave in class:
	GetName()
	Retreive()

If you have any questions or problems please contact me by e-mail or by phone.  I will
hold office hours next week as usual plus on Thursday at the same time.  You can also
contact me and make an appointment.

Return to main page