[AccessD] A couple of Access+Excel Questions

[Bill Benson]

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.
https://www.datanumen.com/blogs/open-populate-template-excel-vba/

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
Excel instance.

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

With objRecordset
    .MoveLast
    iLastRow = .RecordCount
End With

Set objExcel = New Excel.Application
With objExcel
    .Visible = True
    .Workbooks.Add Template:=PathToTemplate
    Set objWorkbook = .ActiveWorkbook
    With objWorkbook
        Set objWorksheet = .Worksheets(1)
        With objWorksheet
            Set rngInsertData = .Range("A4:C4").Resize(iLastRow, 3)
            rngInsertData.CopyFromRecordset Data:=objRecordset
        End With
        Set objWorksheet = Nothing
        .SaveAs FileName:=strWorkbookSavePath,
FileFormat:=xlOpenXMLWorkbook '51, xlsx
        .Close False
    End With
    Set objWorkbook = Nothing
    .Quit
End With
Set objExcel = Nothing
End Sub

On Fri, Oct 1, 2021 at 9:08 PM Arthur Fuller <fuller.artful at gmail.com>
wrote:

> 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?
>
> Thanks.
>
> --
> Arthur
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>