[AccessD] A couple of Access+Excel Questions
bensonforums at gmail.com
Sat Oct 2 14:41:05 CDT 2021
Ensure the Access app has a reference to Excel. That way you can use
Intellisense and all the intrinsic constants, which becomes more and more
useful as you put in more and more code developed for Excel into the Access
app. And you are not telling Access to open something an Excel file, you
need to tell EXCEL to *add* a workbook using an Excel template. Code that
will do that can be found at the link pasted below. That author is using
ADODB, I think you can easily substitute DAO recordsets and commands.
I see no particular reason in this case to create a named range. It would
be helpful to at least know the sheet you want the recordset inserted at.
The CopyFromRecordset property of the Excel range object is ready to accept
the recordset you pass it from Access, so long as Access is controlling the
Here is a basic example.
Sub InsertExcelData(objRecordset As DAO.Recordset, PathToTemplate As
String, strWorkbookSavePath As String)
Dim strSQL As String
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim rngInsertData As Excel.Range
Dim iLastRow As Long
iLastRow = .RecordCount
Set objExcel = New Excel.Application
.Visible = True
Set objWorkbook = .ActiveWorkbook
Set objWorksheet = .Worksheets(1)
Set rngInsertData = .Range("A4:C4").Resize(iLastRow, 3)
Set objWorksheet = Nothing
FileFormat:=xlOpenXMLWorkbook '51, xlsx
Set objWorkbook = Nothing
Set objExcel = Nothing
On Fri, Oct 1, 2021 at 9:08 PM Arthur Fuller <fuller.artful at gmail.com>
> I have an Access 365 app that occasionally has to talk to Excel. I created
> an Excel template to receive a DAO RecordSet from Access. I formatted the
> columns and column heads, then inserted four rows above the recordset to
> hold the values CompanyName, ProjectNamd and ReportHeadingl these
> values exist on the Access form containing the button that does the export
> to Excel. They also exist as static functions in Access.
> 1. In the Access code, how do I tell Excel to open the template rather
> than just creating a blank workbook?
> 2. The Access code also supplies the name of the Excel file to create. That
> part works fine.
> 3. I want to send the values mentioned above to cells B2, C2 and D2. And
> since I've added those three rows, I'll need to somehow indicate that the
> recordset data should start at A4
> 3. How do I create a named range in excel? Is that even the right
> approach? Is there a better way?
> AccessD mailing list
> AccessD at databaseadvisors.com
> Website: http://www.databaseadvisors.com
More information about the AccessD