Code Excel Automation Template | Code Database

Function for using Excel Automation

Excel Automation is a very powerful technique where you can communicate with an Excel spreadsheet from within VBA code in your Access Project. This feature allows you to do some very powerful things, such as populating an existing spreadsheet with data from your database, taking advantage of excel sheets you have already set up, dump data into a spreadsheet and format it, and wide variety of functions. The example below shows simply how to interact with Excel from within your Access Application.

Public Sub doExcelAutomation() On Error GoTo doExcelAutomationErr Dim mySheet As Object Dim xlApp As Object Dim strName As String strName = "C:\MyNewExcelFile.xls" Set xlApp = CreateObject("Excel.Application") Set mySheet = xlApp.Workbooks.Open("C:\MyExistingExcelFile.xls").Sheets(1) If FileExists(strName) Then Kill strName End If mySheet.SaveAS strName xlApp.Visible = False Set mySheet = xlApp.Sheets("YourSheetName") mySheet.cells(8, 1).Value = Me.txtAccessFormField1 mySheet.cells(9, 1).Value = Me.txtAccessFormField2 mySheet.cells(10, 1).Value = Me.txtAccessFormField3 mySheet.cells(9, 8).Value = Me.txtAccessFormField4 mySheet.Application.ActiveWorkbook.Save Set mySheet = Nothing Set xlApp = Nothing doExcelAutomationExit: Exit Sub doExcelAutomationErr: MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number Resume doExcelAutomationExit End Sub

The Microsoft Access Excel Automation Database is a sample database that shows you how to implement Excel Automation inside your own Access Database.

Please note that this is a template database. If it does not meet your needs 100%, and you are unable to customize it yourself, let us know and we will provide a quote to develop the customizations you need to get you exactly what you want in your Access Excel Automation Database System.

Excel Automation Form

Live Demonstration: To experience a more interactive and personalized demonstration of this WSI template, we invite you to contact WSI and schedule a live demonstration with one of our team members. This opportunity allows you to engage directly with us, either as an alternative to or in conjunction with downloading and exploring the demo version of the template at your leisure. Please note that downloading Microsoft Office files, including Microsoft Access database files, might trigger a security warning on your computer. To avoid this and get a comprehensive overview, you have the option of viewing the template through a virtual meeting on Teams or Zoom with a WSI representative.
Once you purchase the database template, you will receive the full version of the database in a standard Access Database file. The purchased database will have full access to the database window, all design objects, the visual basic project code, and you will be able to modify or add to the design in any way you wish (this does not apply to the run-time/compiled versions). If you lack the time to integrate this database into your system, please Contact Us and we will be glad to help with your integration process. If you like the basics of what you see in the database, but need enhancements to make the system functional for your business, please Contact Us and we will develop a quote for you to implement the additional features and enhancements that you require.

$24.99 - this allows you to use this database template however you wish within your organization.

$49.99 - for unlimited developer use - this allows you to use this database template however you wish for any applications you develop and distribute.

This next function shows how to import an excel worksheet into an existing Access Database. This method is very valuable when you have to trap for certain situations and make sure the data is as expected.

Function doImportExcelFile() On Error GoTo cmdImportErr Dim sFileName As String 'Note, this function uses the common file dialog to allow the user to select the 'File they wish to import. If you want, you can simply set the sFileName variable 'To a hardcoded value of your desire. sFileName = dhFileDialog(strFilter:="Microsoft Excel (*.xlsx)" & _ vbNullChar & "*.xlsx" & vbNullChar & vbNullChar, _ strDialogTitle:="Open", lngFlags:=dhOFN_OPENEXISTING, fOpenFile:=False) Dim rs2 As New ADODB.Recordset Dim cnn2 As New ADODB.Connection Dim cmd2 As New ADODB.Command With cnn2 .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFileName & ";Extended Properties=""" & "Excel 12.0 Xml;HDR=YES;IMEX=1;" & """" .Open End With Set cmd2.ActiveConnection = cnn2 cmd2.CommandType = adCmdText cmd2.CommandText = "SELECT * FROM [Sheet1$]" rs2.CursorLocation = adUseClient rs2.CursorType = adOpenStatic rs2.LockType = adLockReadOnly rs2.Open cmd2 DoCmd.SetWarnings False Dim sql As String Dim sWeight As Double Dim sWeightTotal As Double While Not rs2.EOF If Trim(rs2(13)) = "" Or Not IsNumeric(Trim(rs2(13))) Then sWeight = 0 Else sWeight = rs2(13) End If If Trim(rs2(14)) = "" Or Not IsNumeric(Trim(rs2(14))) Then sWeightTotal = 0 Else sWeightTotal = rs2(14) End If sql = "'" & rs2(0) & "'," & rs2(1) & ",'" & rs2(2) & "','" & rs2(3) & "','" & rs2(4) & "','" & rs2(5) & "'," & sWeight & "," & sWeightTotal DoCmd.RunSQL "INSERT INTO tblYourTable ( Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8 ) Values (" & sql & " )" rs2.MoveNext Wend DoCmd.SetWarnings True rs2.Close cnn2.Close Exit Sub doImportErrExit: Exit Sub cmdImportErr: MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number Resume doImportErrExit End Function

If you need any help implementing this function or modifications made for your needs, please Click Here to get a free quote for your Microsoft Access Development needs. 

Click Here to learn more about the WSI quoting process.