Re: Excel to Oracle, need a help

From: Craig Love <cdl_at_crosslink.net>
Date: Thu, 01 Apr 1999 22:40:57 -0500
Message-ID: <37043C49.AF2E1BD_at_crosslink.net>

I've connected to Excel spreadsheets in Visual Basic using DAO. It's actually very simple to access. Normally, you use DAO to access databases but you can use it to open an Excel spreadsheet too. The rows are records and the columns are fields.

At the risk of sending stuff you don't need, here's an except from the VB Online Books:

The Microsoft Jet IISAMs support the following single-sheet worksheet and multiple-sheet workbook versions of Microsoft Excel: Excel 3.0 and Excel 4.0 for single-sheet worksheets, and Excel 5.0 (for Microsoft Excel 5.0 and 7.0) and Excel 8.0 for multiple-sheet workbooks. There are a few operations that you can't perform on Microsoft Excel worksheets or workbooks through the Microsoft Excel IISAM:

·	You can't delete rows from Microsoft Excel worksheets or workbooks.
·	You can clear data from individual cells in a worksheet, but you can't
modify or clear cells that contain formulas.
·	You can't create indexes on Microsoft Excel worksheets or workbooks.
·	You can't read encrypted data through the Microsoft Excel IISAM. You

can't use the PWD parameter in the connection string to open an encrypted worksheet or workbook, even if you supply the correct password. You must decrypt all Microsoft Excel worksheets or workbooks through the Microsoft Excel user interface if you plan to link or open them in your Microsoft Jet database.         

Specifying Sheets and Ranges in Microsoft Excel Worksheet and Workbook Files
You can specify a subset of the available data when you open a Microsoft Excel worksheet or workbook. In worksheet files, you can open the entire sheet, a named range of cells, or an unnamed range of cells. In a workbook file, you can open a single worksheet, a named range anywhere in the workbook, or an unnamed range in a single worksheet. The following table lists the conventions for the DATABASE and source arguments you must supply in the connection string to access each of these objects.
To access this object In this version of Microsoft Excel Use this syntax

Entire sheet in a worksheet file 3.0 and 4.0 Use the DATABASE parameter to specify the fully qualified network or directory path to the worksheet file; use the source argument to specify the sheet as filename#xls, where filename is the name of the worksheet. Entire worksheet in a workbook file 5.0, 7.0, and 8.0 Use the DATABASE parameter to specify the fully qualified network or directory path to the workbook file, including the workbook file name; use the source argument to specify the sheet as sheetname$, where sheetname is the name of the worksheet. Important You must follow the worksheet name with a dollar sign ($).

Named range of cells in a worksheet or workbook file	3.0, 4.0, 5.0, 7.0,
and 8.0	Use the DATABASE parameter to specify the fully qualified

network or directory path to the worksheet or workbook file, including the worksheet or workbook file name; use the source argument to specify the named range as NamedRange, where NamedRange is the name you assigned to the range in Microsoft Excel.Important You must name the range in Microsoft Excel before attempting to open or link it. Unnamed range of cells in a worksheet file 3.0 and 4.0 Use the DATABASE parameter to specify the fully qualified network or directory path to the worksheet file, including the worksheet file name; use the source argument to specify the range as A1:Z256. Replace A1:Z256 with the range of cells you want to access.

Unnamed range of cells in a single worksheet in a workbook file	5.0 and
7.0	Use the DATABASE parameter to specify the fully qualified network or

directory path to the workbook file, including the workbook file name; use the source argument to specify the sheet you want to link or open as sheetname$ and the range as A1:Z256. For example, to access cells A1 through Z256 in worksheet SheetName, you would use the following in the source argument SheetName$A1:Z256.         

Note You can't specify a value in a range that exceeds the maximum number of rows, columns, or sheets for the worksheet or workbook. See your Microsoft Excel documentation for these values.         

Linking Microsoft Excel Spreadsheets
To link a Microsoft Excel spreadsheet to your Microsoft Jet database, use the OpenDatabase method to open the local Microsoft Jet database, create a TableDef object, and set the Connect and SourceTableName properties of the TableDef object to indicate the Microsoft Excel spreadsheet that you want to link.
The following example links an external Microsoft Excel spreadsheet: Public Sub LinkExcelSheet()

	Dim dbsJet As Database
	Dim rstSales As Recordset
	Dim tdfExcelSheet As TableDef

	Set dbsJet = OpenDatabase("C:\Jet_Samp.mdb")



	' Create a TableDef object.


	Set tdfExcelSheet = dbsJet.CreateTableDef _
		("Linked Excel Worksheet")



	' Set connection information.


	tdfExcelSheet.Connect = "Excel 5.0;" _
		& "Database=C:\Sales\Q1Sales.xls"
	tdfExcelSheet.SourceTableName = "January Sales"



	' Append the TableDef object to create a link.


	dbsJet.TableDefs.Append tdfExcelSheet



	' Open a Recordset object on the Microsoft Excel


	' worksheet


	Set rstSales = dbsJet.OpenRecordset _
		("Linked Excel Worksheet")

End Sub         

Opening Microsoft Excel Spreadsheets
The following code examples illustrate how to specify an entire worksheet, a named range, and an unnamed range of cells in a Microsoft Excel 4.0 worksheet and a Microsoft Excel 7.0 workbook. This first example opens a Microsoft Excel 4.0 worksheet from a Microsoft Jet database three times to demonstrate how to open an entire worksheet, a named range in a worksheet, and an unnamed range in a worksheet. The example tests the ranges by counting the number of records that appear in the Recordset.
Because the HDR parameter in the connection string is set to No, the first row of the spreadsheet is treated as a record in the table, not as a header containing column names. If the HDR parameter was set to Yes in this example, the total records in the Recordset wouldn't include the first record, and the variable intNumRecords would be one less than its value when HDR is set to No.
Public Sub Excel4SheetAndRangeTest()

	Dim dbs As Database
	Dim rstSales As Recordset
	Dim qdfNumOrders As QueryDef
	Dim intNumRecords As Integer



	' Open the Microsoft Excel spreadsheet. 


	' Suppress headers (HDR=NO).


	Set dbs = OpenDatabase("C:\Samples", _
		False, False, "Excel 4.0;HDR=NO;")


	' Create a Recordset for the entire worksheet


	' WSSample.xls.


	Set rstSales = dbs.OpenRecordset("WSSample#XLS")


	' Execute a MoveLast and count the records.


	rstSales.MoveLast
	intNumRecords = rstSales.RecordCount
	MsgBox "There are " & intNumRecords & _
		" rows in this worksheet."


	' If headers aren't suppressed, 


	' intNumRecords would be one larger.


	rstSales.Close

	Set dbs = _
		OpenDatabase("C:\Samples\WSSample.xls", _
		False, False, "Excel 4.0;HDR=NO;")


	' Create a Recordset for the named range 


	' "FirstTenRows" in the worksheet WSSample.xls.


	Set rstSales = dbs.OpenRecordset("FirstTenRows")


	' Execute a MoveLast and count the records.


	rstSales.MoveLast
	intNumRecords = rstSales.RecordCount
	MsgBox "There are " & intNumRecords & _
		" rows in this named range."
	rstSales.Close

	Set dbs = _ 
		OpenDatabase("C:\Samples\WSSample.xls", _
		False, False, "Excel 4.0;HDR=NO;")


	' Create a Recordset for the unnamed range 


	' A1 through G5 in the worksheet WSSample.xls.


	Set rstSales = dbs.OpenRecordset("A1:G5")


	' Execute a MoveLast and count the records.


	rstSales.MoveLast
	intNumRecords = rstSales.RecordCount
	MsgBox "There are " & intNumRecords & _
		" rows in this range."

	rstSales.Close
	dbs.Close

End Sub         

This next example opens a worksheet in a Microsoft Excel 7.0 workbook from a Microsoft Jet database three times to demonstrate how to open a worksheet in a workbook, a named range in a workbook, and an unnamed range in a worksheet in the workbook. The code tests the ranges by counting the number of records that appear in the Recordset. Note Because the formats of Microsoft Excel 5.0 and Microsoft Excel 7.0 are essentially the same, use the database type Excel 5.0 in the connection string for both Microsoft Excel 5.0 and Microsoft Excel 7.0. The specifier Excel 7.0 isn't valid.         

Public Sub Excel7SheetAndRangeTest()

	Dim dbs As Database
	Dim rstSales As Recordset
	Dim qdfNumOrders As QueryDef
	Dim intNumRecords As Integer



	' Open WBSample.xls.


	Set dbs = OpenDatabase _
		("C:\Samples\WBSample.xls", _
		False, False, "Excel 5.0;HDR=NO;")


	' Create a Recordset for the worksheet 

	' SampleSheet in the workbook WBSample.xls.

	' The dollar sign character following the 

	' sheet name tells the IISAM that 


	' the code is referencing the entire sheet.


	Set rstSales = dbs.OpenRecordset("SampleSheet$")


	' Execute a MoveLast and count the records.


	rstSales.MoveLast
	intNumRecords = rstSales.RecordCount
	MsgBox "There are " & intNumRecords & _
		" rows in this worksheet."
	rstSales.Close

	Set dbs = OpenDatabase _
		("C:\Samples\WBSample.xls", _
		False, False, "Excel 5.0;HDR=NO;")


	' Create a Recordset for the named range 


	' "SecondTenRows" in the workbook WBSample.xls.


	Set rstSales = dbs.OpenRecordset("SecondTenRows")


	' Execute a MoveLast and count the records.


	rstSales.MoveLast
	intNumRecords = rstSales.RecordCount
	MsgBox "There are " & intNumRecords & _
		" rows in this named range."
	rstSales.Close

	Set dbs = OpenDatabase _
		("C:\Samples\WBSample.xls", _
		False, False, "Excel 5.0;HDR=NO;")


	' Create a Recordset for the range 

	' A1 through G5 in the worksheet 


	' SampleSheet in the workbook WBSample.xls. 


	Set rstSales =dbs.OpenRecordset _
		("SampleSheet$A1:G5")


	' Execute a MoveLast and count the records.


	rstSales.MoveLast
	intNumRecords = rstSales.RecordCount
	MsgBox "There are " & intNumRecords & _
		" rows in this range."

	rstSales.Close
	dbs.Close

End Sub         

Note Microsoft Excel 8.0 supports the ability to create files that contain formatting information for both Excel 7.0 and Excel 8.0. This enables the creation of files that can be opened by either Excel 7.0 and Excel 8.0 without having to convert the file format. You can access these files only in read-only mode when you use the Microsoft Excel IISAM.          Gennady wrote:

> 
> Hello,
[Quoted] [Quoted] > I need to create a procedure to insert and update data in Oracle database
> table
> from Excel spreadsheet. I use OO4O to connect Oracle.
> Could you give me a hint or reference to a sample, please?
> 
> Gennady

Received on Fri Apr 02 1999 - 05:40:57 CEST