[AccessD] A couple of Access+Excel Questions
bensonforums at gmail.com
Tue Oct 5 16:52:56 CDT 2021
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.
> 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
> 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
> End With
> Set objExcel = Nothing
> End Sub
> 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.
>> 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