[AccessD] Access + Excel Questions

Arthur Fuller fuller.artful at gmail.com
Sun Sep 19 17:12:54 CDT 2021


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


More information about the AccessD mailing list