[AccessD] Excel Question

[James Button]

Added stress for a couple of hours - and then the code creation to setup and
manipulate worksheets becomes much easier.
Read up about R1C1 mode and CELLS(row,column).formulaR1C1 = "=the formula"
And Range(cells(),cells()).UNION.Range(cells(),cells())
And RANGE()="=formula"  incrementing the contents of the cells in the range as a
drag on a cell does.
And  CELLS(row,column).format = the settings  as per the recorded macro.
Also - usually better to use .VALUE2 than .VALUE  

Also - while you can have Excel create arrays, and lists - the default is that
all the attributes of each cell are in that  stored data unless you tell Excel
to just have VALUES  or strings 


Also read-up on excelspecialcells


And - use the EXCEL-L at PEACH.EASE.LSOFT.COM  forum for answers to questions that
you cannot easily get from docs.microsoft.com
Also  do the mind with some advice  by reading the archives
EXCEL-L at PEACH.EASE.LSOFT.COM  

Just remember that it is very difficult to protect excel worksheets from the
user - a technique  frequently adopted is to have a base workbook that is the
model for the users  work in excel
And a macro (script) in a protected workbook (file) that your access process
will run  - after creating the users version of the data
presentation/manipulation workbook from the template, 
and that script will setup the layout, and get the required data from the
database  using a view where Access limits the data they get.  

JimB

-----Original Message-----
From: AccessD
<accessd-bounces+jamesbutton=blueyonder.co.uk at databaseadvisors.com> On Behalf Of
Arthur Fuller
Sent: Saturday, January 22, 2022 1:36 AM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Excel Question

Thanks, Rocky. I've not used macros much, but shall give it a whirl. In
years past, I've written some fairly complex Excel code, but tody I've
learned more about Excel VBE than in the past several years.

On Fri, Jan 21, 2022 at 7:13 PM Rocky Smolin <rockysmolin2 at gmail.com> wrote:

> Firat - about the limits - if you;re anywhere near the limit of worksheets,
> I'd say you're using the wrong tool.
>
> Regarding setting any property in Excel - virtually everything can be done
> from code. I've done a lot of automating from Access - setting column
> headings, bolding, setting column widths, formulas - everything.  The trick
> is not know the VBA code to do these things but to use the macro recorder,
> do what would do manually, like select a row and then bold everything
> selected, and then stop the macro recorder and look at the code it
> generated. It's mostly a cut and paste job.
>
> r
>
> On Fri, Jan 21, 2022 at 3:12 PM Arthur Fuller <fuller.artful at gmail.com>
> wrote:
>
> > I am creating an Excel workbook from VBA code. I need to know this: Is
> > there a maximum number of sheets in a Workbook?
> > I also need to know if there is a way to programmatically set the
> > column-widths (equivalent to moving the cursor to the right edge of a
> > column then double-clicking).
> >
> > And finally, how can I set row 1 (containing the headers to Bold?
> >
> > --
> > 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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com