[AccessD] Access plus Excel question

[James Button]

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