Ian’s Blog

Everytime I see some code that has to work with data in Excel files it always uses OLE automation. The worst case of this is on servers to process uploaded files, Microsoft doesn’t support Office on backend servers. I think a much better method is to use the OLEDB JET database providers, whether it’s for Domino, ASP, ASP.NET or anything else for that matter. There’s no need to install Office at all, if your using Windows 2008 Server 64 bit you will need to install a download from Microsoft to get the ADODB dlls. This method is supported in all versions of Excel including 2007, but this uses the ACE Provider instead of JET.

Anyway, here’s some code written in LotusScript to demonstrate Create, Read and Update of Excel data. Unfortunately Delete is not supported.

The following code is used to read a XLS file. It will only read the first Worksheet, but you could read through all of them.

On Error Goto processError

	Const filename = "c:\users\ian\my documents\writexls.xls"
	Const adSchemaTables = 20

	Dim adodbConnection As Variant
	Dim connectionString As String
	Dim adodbRecordSet As Variant
	Dim worksheet As String

	connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
	filename & ";Extended Properties=""Excel 8.0;HDR=Yes;"""

	Set adodbConnection = CreateObject("adodb.connection")
	adodbConnection.Open(connectionString)	

	Set adodbRecordSet = adodbConnection.OpenSchema(adSchemaTables)
	worksheet = adodbRecordSet.Fields("table_name").Value

	adodbRecordSet.Close
	adodbRecordSet.Open "SELECT * FROM " + worksheet, adodbConnection
	While Not adodbRecordSet.EOF
		For i = 0 To (adodbRecordSet.Fields.Count - 1)
			Msgbox adodbRecordSet.Fields(i).Value
		Next
		adodbRecordSet.MoveNext
	Wend

	adodbRecordSet.Close
	adodbConnection.Close

finally:

	Set adodbConnection = Nothing
	Set adodbCommand = Nothing
	Exit Sub

processError:

	Print "Error: " & Error & ": " + Error$ + " on line " & Erl
	Goto finally

Now, here’s the code to create, insert and update data in the file:

On Error Goto processError

	Const filename = "c:\users\ian\my documents\writexls.xls"

	Dim adodbConnection As Variant
	Dim connectionString As String
	Dim adodbCommand As Variant

	Kill filename

	connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
	filename & ";Extended Properties=""Excel 8.0;HDR=Yes;"""

	Set adodbConnection = CreateObject("adodb.connection")
	adodbConnection.Open(connectionString)	

	Set adodbCommand = CreateObject("adodb.command")
	Set adodbCommand.ActiveConnection = adodbConnection

	adodbCommand.CommandText = "CREATE TABLE [Contacts] ([Id] number, [Name] string)"
	adodbCommand.Execute

	adodbCommand.CommandText = "INSERT INTO [Contacts] ([Id], [Name]) VALUES ('1', 'Ian')"
	adodbCommand.Execute
	adodbCommand.CommandText = "INSERT INTO [Contacts] ([Id], [Name]) VALUES ('2', 'Tom')"
	adodbCommand.Execute

	adodbCommand.CommandText = "UPDATE [Contacts] SET [Name] = 'Paul' WHERE [Id] = 1"
	adodbCommand.Execute

	adodbConnection.Close

finally:

	Set adodbConnection = Nothing
	Set adodbCommand = Nothing
	Exit Sub

processError:

	Print "Error: " & Error & ": " + Error$ + " on line " & Erl
	Goto finally

admin :: Jul.04.2010 :: Uncategorized :: No Comments »

Feed on Comments to this post