[AccessD] Access + Excel Questions

[Bill Benson]

Art, I think I already mentioned... I store my formatted templates in an
attachment field, save them to disk. Here is some sample code. I cut out a
lot so don't expect all the variables to be declared properly, and I have
not included any of the customized stuff I did with the workbooks
afterwards.

Basically, when automating Excel from Access you have to fire up an
instance of Excel, with CreateObject or a direct reference to Excel in your
VBA project. And note too that unless you do set a reference to Excel
(which for portability reasons is actually not generally a good practice)
then if you use any code for Excel only, Excel-only constants like xlRight,
xlUp, etc have to be figured out what their long variable type equivalents
are as a numeral.

Now when you open the file, it it has its own macros, those can also be
called by access controlling Excel, via

MyXL.Application.Run "     fill in the rest..."


Sub OpenExcelAttach(strFileName As String)
Dim db As DAO.Database
Dim Rst As DAO.Recordset
Dim rstAttach As DAO.Recordset  'maybe DAO.Recordset2 ????
Dim wb As Object
Dim oApp As Object

Set fso = CreateObject("Scripting.FileSystemObject")
strTempPath = GetReportsFolder & "\"

On Error Resume Next

Set db = CurrentDb
Set Rst = db.OpenRecordset("Select * from tblFiles where Description = '" &
strFileName & "'")
If Not Rst.EOF Then
    Set rstAttach = Rst.Fields("Attachment").Value
    strFileName = rstAttach.Fields("FileName").Value
    On Error Resume Next
    rstAttach.Fields("FileData").SaveToFile strTempPath & strFileName
    rstAttach.Close
    Set rstAttach = Nothing
End If
Rst.Close
Set Rst = Nothing
On Error Resume Next

Set oApp = MyXL
oApp.Visible = True 'Set to false if you want
Set wb = oApp.Workbooks.Open(strTempPath & strFileName)

On Sun, Sep 19, 2021 at 6:13 PM Arthur Fuller <fuller.artful at gmail.com>
wrote:

> I have an Access app (actually I only wrote part of it, and the client has
> asked for a couple of enhancements. I'm not sure how to handle them/
>
> Within Access, I have code that creates a RecordSet and then calls Excel
> and sends the ReocrdSet there, but it arrives bare-bones. Withing Access,
> the appropriate button generates both an Access report and exports the data
> to Excel. present the
> the form on which the button lives contains some data that I would like to
> export to the Excel workbook 00 simple things like the CompanyID, the
> ProjectID, etc. I would like to send these values into the generated Excel
> file, and present them as a Header to the raw spreadsheet data. Ideally, I
> would like also to present the Recordset rows beneath a header that
> displays CompanyID and ProjectID.
> I've heard and read that an Excel template might do the trick. I have been
> doing some reading on this, but I'm not even confident that I know how to
> create a template, let alone address the cells in the header.
> Let's say the desired header looks like this:
> A1: "Company:"
> B1: CompanyID value set from Access
> A2: "Project"
> B2: ProjectID value set from Access
>
> Beneath this header are the rows that come from an Access RecordSet. Thet's
> the only part I have that works.
>
> My reading suggest that I should create a template (perhaps with two named
> ranges (CompanyID and ProjectID) and then address each of them
> directly from Access, presumably using the Range thing. Assuming that I get
> past these hurdles, then I need to tell Access to send the Recordset tp A3,
> and then tell Excel to set the RecordSet headeeers to bold, and size the
> columns according to the width of the column names.
>
> Frankly I haven't much of a clue how to control Excel from Access. My
> reading has led to the discovery of an Excel function called
> CopyFromRecordSet(), but don't know how to use it. Hell, I'm not even sure
> how to create an Excel temple, or how to a) load it from Access, and b)
> generate the XLS filename from within Access (which having loaded the
> template, the resultant file should receive its name from Access, so the
> whole process is invisible to the client and his users.
>
> I've been mostlyy retired for seceral years, and forgotten almost
> everything I knew, so reminders, suggestions, examples, and corrections to
> my strategy are invited.
>
> As Michael Corleone told his wife, "I keep trying to get out, but they keep
> dragging me back in." Things could be worse: they could try to assassinate
> me "In My Home! In My Home!"
>
> (You may conjecture that I love Godfather One and especially Two.)
>
> --
> Arthur
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>