[AccessD] Fwd: Setting Column Widths on Excel Spreadsheet

Thu Mar 23 22:45:57 CDT 2023

---------- Forwarded message ---------
From: Rocky Smolin <rockysmolin2 at gmail.com>
Date: Thu, Mar 23, 2023 at 8:41 PM
Subject: Re: [AccessD] Setting Column Widths on Excel Spreadsheet
To: Access Developers discussion and problem solving <
accessd at databaseadvisors.com>

Without doing too deep a dive, I got the program to behave as I wanted, and
am using a default of 10 CPI to set column width in the exported
spreadsheet. I don't believe in over engineering especially when it's my
time at the display (and my client's money - but that's a secondary issue
at this point). So I'll see how this solution [;ays with the end user and
will refine as they demand.

tks,

Rocky

On Thu, Mar 23, 2023 at 12:42 PM James Button via AccessD <
accessd at databaseadvisors.com> wrote:

> Many problems trying to deal with width (and height)
> I started trying to set the window, and Excel display cells etc. on the
> screen as appropriate for the users screen
>
> And eventually gave up!
>
> So - Twips are the positioning on the screen I actual parts of an inch -
> so the bottom righthand "twip" on a 20" wide by 10 high screen is double
> the number of that on a 10" x 5" screen.
>
> Access - and the OS  use twips for window positioning and size, and if
> remembered correctly, the positioning of pop-up panels such as error
> messages.
>
> Points is what is (usually) used within the app display for text and
> embedding things in the text 'display' area
>
> Then the next consideration - what font is being displayed
> Is it fixed - (Courier/System) or variable  - (Times new Roman)?
> And is it wide, bold, narrow
>
> Then there is the effect of the Zoom factor, what will fit within a
> cell/line at 100% may well be considered too wide if the view is at 70%
>
> Then the users Excel incidence will have a default font and width setting
> - whatever the user set in Options for that particular version of Excel on
> their system.
>
> Up until 2013, opening a workbook via code would have that as part of the
> active excel app incidence and the settings user were those that the file
> had last been saved as.
> Then MS changed the process so that the new file was opened in a new Excel
> window - and  it used cascade technique on the new window, so mostly
> covering what the user had been looking at
>
> What I ended up doing post 2013 was getting the user to enter the window
> positioning,  size,  and zoom factor
> And then having a macro (subroutine) that would in future set the new
> workbook view to what the user had set as their preference.
> A Column with a set value was set to fit the data, and then that width was
> recorded by the code
> so the code could work out the appropriate width for a text string.
> BUT
> That was working on the actual worksheet display from within the Excel
> session.
>
> For doing the process from Access for an Excel setup where the setup has
> to be done remotely, or as a preset, then  -
>
> Find out the screen width and height,
> Subtract some to allow for the taskbar - 1, 2, 3 rows? - I normally use 2
> for my desktop toolbars, recent  pinned etc,
> BUT those can be on the side of the display -
> And - maybe the user will be moving the Excel window onto a 4K screen set
> as an extension to the laptop window.
> retaining the desktop controls on the laptop screen, but having the excel
> data shown on a 32"? screen with Zoom set to get as much data on the
> display as possible for their sight.
>
> My aim being to get the displayed data visually large enough to tell e
> from a and I from l,
> While still being able to show the requisite data , and selection criteria
> -
> - as stated - gave up and took what the user wanted on the system they
> were using -
> And if they changed the screen etc - well that was their responsibility to
> alter the setup!.
>
> Maybe see if the code at these is of use - or at least as a start point
> "https://www.google.com/search?q=vba+get+screen+sizes+in+twips+and+inches"
> <
> https://www.google.com/search?q=vba+get+screen+sizes+in+twips+and+inches&ei=irIYZNLJKvXA8gKC_arIBw&ved=0ahUKEwjSttPSnev9AhV1oFwKHYK-CnkQ4dUDCA8&uact=5&oq=vba+get+screen+sizes+in+twips+and+inches&gs_lcp=Cgxnd3Mtd2l6LXNlcnAQAzIFCAAQogQyBQgAEKIEMgUIABCiBDoKCCEQoAEQwwQQCjoICCEQoAEQwwRKBAhBGABQAFi4EmDwImgAcAF4AIAB1wKIAfgLkgEHMS40LjIuMZgBAKABAcABAQ&sclient=gws-wiz-serp
> >
>
> <
> https://wellsr.com/vba/2019/excel/calculate-screen-size-and-other-display-details-with-vba/
> >
>
>
> JimB
>
>
>
> -----Original Message-----
> From: AccessD <accessd-bounces+jamesbutton=
> blueyonder.co.uk at databaseadvisors.com> On Behalf Of Rocky Smolin
> Sent: Thursday, March 23, 2023 1:16 AM
> To: Access Developers discussion and problem solving <
> accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Setting Column Widths on Excel Spreadsheet
>
> Borge:
>
> That will work for the user who may be looking at the finished exported
> spreadsheet, but how can I determine the units of measurement of the column
> width in VBA from my access app?
>
> At the moment, I'm just multiplying the width in the fields table by 10 and
> that might be a pretty good approximation - somewhere in my mucking about
> in the internets I read that the standard units is approximately 10
> characters per inch.
>
> If the user has changed that (seems unlikely among these users) then I'd
> like to be able to set the column widths correctly.
>
> TIA
>
> Rocky
>
>
> On Wed, Mar 22, 2023 at 5:46 PM Borge Hansen <pcs.accessd at gmail.com>
> wrote:
>
> >
> >
> https://support.microsoft.com/en-us/office/change-the-column-width-and-row-height-72f5e3cc-994d-43e8-ae58-9774a0905f46
> >
> > /borge
> >
> > On Thu, 23 Mar 2023 at 09:59, Rocky Smolin <rockysmolin2 at gmail.com>
> wrote:
> >
> > > OK - got it.   I thought the column width was twips. so it was
> > generating a
> > > number out of range for the column width. Now, since I have your
> > attention,
> > > since this app will be used by users whose Excel may have a different
> > ruler
> > > units, how do I know what it is, or better, how can I change it?
> > >
> > > TIA
> > >
> > > Rocky
> > >
> > >
> > > On Wed, Mar 22, 2023 at 4:38 PM Rocky Smolin <rockysmolin2 at gmail.com>
> > > wrote:
> > >
> > > > Tried this:
> > objXLBook.ActiveSheet.Range("A:A").EntireColumn.ColumnWidth =
> > > > 1000 but no cigar. same message: Unable to set the ColumnWidth
> property
> > > of
> > > > the range.
> > > >
> > > > Tried this: objXLWS.Columns("A").ColumnWidth = 1000 - same message
> > > >
> > > > Couple of other WAGs. No cigar.
> > > >
> > > > Anybody?
> > > >
> > > > MTIA
> > > >
> > > > Rocky
> > > >
> > > >
> Website: http://www.databaseadvisors.com
>