[AccessD] A couple of Access+Excel Questions

[Bill Benson]

Art how did things work out with the template approach?

On Sat, Oct 2, 2021 at 3:41 PM Bill Benson <bensonforums at gmail.com> wrote:

> 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
>>
>