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)
   objConn.Open()
 
   Dim objCmdSelect = New OleDbCommand("UPDATE [Template$B2:B2] SET F1='Company 1'", objConn)
   objCmdSelect.ExecuteNonQuery()

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)
	   objCmdSelect.ExecuteNonQuery()
	   Exit For
           End If
	Next
    Next
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.

Source

References:

• Welcome to the Open XML SDK 2.5 for Office
• Read and Write Microsoft Excel with Open XML SDK
• Office XML SpeadsheetML
• How to read or write Excel file using ACE OLEDB Data Provider
• Working with MS Excel(xls / xlsx) Using MDAC and Oledb

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?