Home » Development » How to write data to Excel file by using Access Database Engine

How to write data to Excel file by using Access Database Engine

I have recently had to deal with a legacy Visual Basic application that writes data to Excel. It suddenly started malfunctioning (It was writing data only to the first row instead of all across the worksheet).

We solved the issue by installing the latest version of Access 2010 Database Engine. I am adding the source code here in case someone needs it.

Sample code for Access Database Engine solution

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
   Dim xConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\temp\PlusCommissionTemplate.xlsx;Extended Properties='Excel 12.0;HDR=NO;'"
   Dim objConn As New OleDbConnection(xConnStr)
   Dim objCmdSelect = New OleDbCommand("UPDATE [Template$B2:B2] SET F1='Company 1'", objConn)

End Sub

A more complete approach:

Dim rowNumber = 1 'Starting from 1 to skip the header row
Dim columnNumber
Dim columnLetter As String() = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}
Dim cellLocation
While MyDataReader.Read()
    rowNumber = rowNumber + 1
    columnNumber = 0
    schemaTable = MyDataReader.GetSchemaTable()
    For Each myField In schemaTable.Rows
	For Each myProperty In schemaTable.Columns
    	   If myProperty.ColumnName = "ColumnName" Then
	   columnNumber = columnNumber + 1
	   cellLocation = columnLetter(columnNumber) & rowNumber & ":" & columnLetter(columnNumber) & rowNumber
	   ThisFieldName = myField(myProperty).ToString
	   ThisfieldValue = MyDataReader(ThisFieldName).ToString
	   'Dim objCmdSelect = New OleDbCommand("UPDATE [" & ThisFieldName & "] SET F1='" & ThisfieldValue & "'", objConn)
	   Dim objCmdSelect = New OleDbCommand("UPDATE [Template$" & cellLocation & "] SET F1='" & ThisfieldValue & "'", objConn)
	   Exit For
           End If
End While

Please note that Access Database engine is not designed or supported for server-side solutions. You should use Open XML instead (Links are below).

To be used by a system service or server-side program where the code will run under a system account, or will deal with multiple users identities concurrently, or is highly reentrant and expects stateless behavior. Examples would include a program that is run from task scheduler when no user is logged in, or a program called from server-side web application such as ASP.NET, or a distributed component running under COM+ services.



Are you looking for a macro to organize images in Excel automatically? Check this post out: How to organize images automatically in an Excel file using a macro?

Leave a Comment