[AccessD] Access plus Excel question

[Arthur Fuller]

Thanks, Jim.
To answer your first question, yes I am using the XLSX file format. The
original data resides in a DAO RecordSet within the Access app. The Access
code walks the rows in a Do While loop, then calls the Access reporter ,
and finally calls Excel in the usual way (DoCmd.OutputtTo etc.). The app
began years ago and its first developer is an old friend, who brought me
aboard to help help finish it. Eventually he retired and I inherited the
client, the app and the code. My friend wrote the Recordset code in this
part of the app, and now, years later, the client has asked for new
functionality. He and I have become good friends over the years.
This explains in part all the questions I've asking about RecordSets and
Collections.
Since the project began (even before that) I've been using MZ-tools, so
there is a historical record of when the code was written and by whom.
Believe it or not, the project began in 2004! That's an app with legs, as
it were
One whole aspect of the app is its interface with the government(s). The
client is a Safety Assessment Engineer, which loosely means that he visits
factories and plants and assesses the machines for compliance with safety
requirements, and in the event of non-compliance he makes recommendations
for how to solve the problem(s). In Canada, thse regulations are the
jurisdiction of the provinces. I imagine the same is true in the USA. Each
government has its own regulations about document formatting etc., so as
David expanded his practice to include other provinces, I had to create a
raft of Word documents full of bookmarks, and write code to fill in the
bookmarks, sometimes with paragraphs, other times with tables. As a result
I learned quite a bit about talking to Word from Access.
It's been a while since I've coded in Access, and hence my barrage of
postings lately

On Thu, Sep 9, 2021 at 2:48 PM James Button via AccessD <
accessd at databaseadvisors.com> wrote:

> Firstly, - you should be using xlsx file type - that is "OK2 as far as
> most IT
> admin departments and email processes and the newer windows warning about
> files
> being moved between systems.
> .xls and xlsb, as well as xlsb are designated to be allowed to contain
> macros
> .xlsx is (supposed) to only contain data and formulas.
> Also there is, with tin the excel options a table of allowed xls (and
> other)
> file types that will be processed.
>
> Then - to what your app is doing
> how are you getting the data into the excel workbook ?
> A template, an existing file to be updated or a "New file" generated by a
> data
> extract script ( as in excel doing the data import using SQL, or having
> the OS
> associations getting Excel to generate a single sheet workbook from an
> opened
> flat file CSV etc.
>
> If you can use a template, or a starter workbook, that could contain code
> to get
> the data from Access, and set headings etc for columns as well as formulae
> to
> manage the data.
>
> Excel will take an input file, and generate a "table" of data that has the
> first
> data row as column headers, and the table will have some "management" done
> by
> excel - formatting inserted rows etc.
>
> If you can (are allowed to) maybe a template excel file with macros and
> have
> that
> create the file the user will view (maybe from a macro containing template
> workbook )
> Format the worksheets etc
> import the wanted data from the database
> format that data in the workbook, add totals and data extraction
> facilities,
> totalling etc.
> Set the header data you want
> I used to use the copy and paste an image facility (shift key while
> copying)  so
> that the notes and totals could be in a separate worksheet (or workbook if
> you
> want)
> and they are shown as a live link over the header row of the data.
> just remember to set the row 1 height large enough  to show the image, and
> the
> column header/data selection options.
>
> If you want to have the table NOT start in row 1, then you will probably
> do that
> easiest  by inserting a block or rows at row 1 after the data is imported
> If excel is not using "Tables" to manage the data then the other basic way
> is to
> use whole columns - and that caused annoyance as you cannot have extra
> data in
> the rows above the data
> that is unless you avoid column references and use ranges ( defined names
> or
> just formulas such as A$12:A56)    where YOU have to determine the end
> according
> to the data rows you pass over
> hence my approach - sheet2 has the totalling entries based on whole
> columns of
> sheet1, and then they are shown as an overlayed picture on row 1 (a defined
> header area with no data) of sheet1
> And - if you want text in the header that could be considered data  but is
> not
> actually there
> Use the custom format such as ;;;"the commentary"  to include the text
> wanted
> and leave the cell empty of data.
> You can also use a formula to show text based on what is in other cells,
> and CF
> for highlighting, or white text on white background
>
> JimB
>
>
> -----Original Message-----
> From: AccessD
> <accessd-bounces+jamesbutton=blueyonder.co.uk at databaseadvisors.com> On
> Behalf Of
> Arthur Fuller
> Sent: Thursday, September 9, 2021 6:30 PM
> To: Access Developers discussion and problem solving
> <accessd at databaseadvisors.com>
> Subject: [AccessD] Access plus Excel question
>
> The Access app I'm working on invokes Excel and tis part works perfectly.
> Access builds up the XLS filename, complete with path, company, project and
> actual XLS filename -- smooth as silk.
>
> One problem remains. What Excel receives is the tabular data and nothing
> else. I want to include some header information above the grid in the XLS
> file, similar to what I do in the Access report to which the XLS file
> corresponds.
> A couple of things come to mind. Use an XL template with a few named
> ranges, then address those from Access once the XLS file is open, or
> reverse the process and have Excel call back to Access and have it return
> the data. (Access can make the three items of interest available using
> three static functions.) Another notion that occurred to me depends
> on whether I can pass parameters to Excel when I invoke it.
> Besides these, there are doubtless other approaches I might use to get
> there. Any suggestions?
>
> (If it matters, this is all being done in Office 365.)
>
> --
> Arthur
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


-- 
Arthur