Here’s a way to go that doesn’t involve reams of code. Use a few Named Cells in an Excel workbook template, then use ADO to connect to the workbook and write some values to the Named Cells. ShellExecute is an API call that makes a copy of your template, then opens the workbook after the values are inserted.
On your workbook, be sure and name the cell above the cell you want your values written to. So if you want First_Name data written to cell C:3, name cell C:2 “First_Name”.
I just hard-coded the values in the SQL statements for simplicity. You’d need to build your own SQL insert commands from values pulled from ADO recordset fields or GUI controls.
Code:
Option Compare Database
Public Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Private Const SW_NORMAL = 1
Private strContactDataTemplate As String 'Path to ContactData Template
Public Sub InsertData()
Dim RetVal As Long
strContactDataTemplate = Application.CurrentProject.Path & "\Template\ContactData.xlt"
FileCopy strContactDataTemplate, Application.CurrentProject.Path & "\Result\ContactData.xls"
'Open ADO connection to Excel workbook
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Application.CurrentProject.Path & "\Result\ContactData.xls;" & _
"Extended Properties=""Excel 8.0;HDR=NO;"""
'Insert values into Named Cells in Excel workbook.
oConn.Execute "Insert into First_Name Values ('Bob')"
oConn.Execute "Insert into Last_Name Values ('Bedell')"
oConn.Execute "Insert into Title Values ('Contract Manager')"
'Close the connection
oConn.Close
'Open the workbook
DoEvents
On Error Resume Next
RetVal = ShellExecute(0, "open", Application.CurrentProject.Path & "\Result\ContactData.xls", "", "C:\", SW_SHOWNORMAL)
End Sub
HTH,
Bob
Export Access Data to a Specific Cell in Excel.
Linear Mode
