[AccessD] Setting Column Widths on Excel Spreadsheet
James Button
jamesbutton at blueyonder.co.uk
Thu Mar 23 14:41:53 CDT 2023
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
> > >
> > >
> > > On Wed, Mar 22, 2023 at 10:31 AM Rocky Smolin <rockysmolin2 at gmail.com>
> > > wrote:
> > >
> > >> Dear List(s):
> > >>
> > >> I have a requirement to create an Excel spreadsheet from a dataset.
> > >> Setting the column widths is the problem.
> > >>
> > >> In this same app I create an empty spreadsheet (which is later
> imported
> > >> into the Access app) and put in the column heading and set the widths.
> > >> This is working well but the column widths are predefined (by me) so
> the
> > >> code is pretty simple:
> > >>
> > >> ' Setup
> > >> Set objXLApp = New Excel.Application
> > >> Set objXLBook = objXLApp.Workbooks.Add
> > >> Set objXLWS = objXLBook.Sheets(1)
> > >>
> > >> objXLWS.Cells(1, 2) = "CRS:"
> > >> objXLWS.Cells(1, 3) = Me.fldCourseNumber
> > >>
> > >>
> > >> ' Column Heads
> > >>
> > >> ' A
> > >> objXLWS.Cells(4, 1) = "Course"
> > >> objXLWS.Columns("A").ColumnWidth = 52
> > >>
> > >> ' B
> > >> objXLWS.Cells(3, 2) = "Part"
> > >> objXLWS.Cells(4, 2) = "No"
> > >> objXLWS.Columns("B").ColumnWidth = 7
> > >> .
> > >> .
> > >> .
> > >> Etc. through column X.
> > >>
> > >> However, in the case I'm having a problem with the column widths are
> > >> defined by the user along with being able to specify which columns
> they
> > >> want to appear. This is working well for the datasheet format on the
> > form
> > >> showing data to be exported using this code when the form opens
> > iterating
> > >> through the the Fields table and setting the width:
> > >>
> > >> ' set column widths of fields to be displayed
> > >>
> > >> Set db = CurrentDb
> > >> Set rs = db.OpenRecordset("Select * FROM tblParticipantListFields
> > >> ORDER BY fldPLOrderNumber")
> > >> Do While rs.EOF = False
> > >>
> > >> If rs!fldPLFieldVisible = False Then
> > >>
> > >>
> >
> Forms!frmParticipantList!sfrmParticipantList(rs!fldPLFieldName).ColumnWidth
> > >> = 1
> > >> Else
> > >>
> > >>
> >
> Forms!frmParticipantList!sfrmParticipantList(rs!fldPLFieldName).ColumnWidth
> > >> = rs!fldPLFieldWidth * 1440
> > >> End If
> > >>
> > >> NextField:
> > >> rs.MoveNext
> > >> Loop
> > >>
> > >> Now to the code that tries the same trick pushing the column headings
> > out
> > >> to the spreadsheet and setting the width of the column in the
> > spreadsheet.
> > >>
> > >> ' Open Fields and Participant Tables
> > >> Set db = CurrentDb
> > >> Set rsPLFields = db.OpenRecordset("Select * FROM
> > >> tblParticipantListFields")
> > >> Set rsPLData = db.OpenRecordset(Me.txtRecordSource)
> > >>
> > >> ' Create XL Spreadsheet
> > >> Set objXLApp = New Excel.Application
> > >> Set objXLBook = objXLApp.Workbooks.Add
> > >> objXLBook.Activate
> > >> Set objXLWS = objXLBook.ActiveSheet
> > >>
> > >> ' Make Column Headings - start on row 3
> > >> Do While rsPLFields.EOF = False
> > >> If rsPLFields!fldPLFieldVisible = False Then GoTo
> GetNextField:
> > >>
> > >> objXLWS.Cells(3, intCol) = rsPLFields!fldPLFieldLabel
> > >> strColumn = XLColumn
> > >>
> > >> objXLBook.ActiveSheet.Range(strColumn).ColumnWidth =
> > >> rsPLFields!fldPLFieldWidth * 1440
> > >>
> > >> intCol = intCol + 1
> > >>
> > >> GetNextField:
> > >> rsPLFields.MoveNext
> > >> Loop
> > >>
> > >> The Private Function XLColumn returns the column letter correctly. In
> > >> column 1 for example it returns A:A.
> > >>
> > >> Private Function XLColumn()
> > >> If intCol <= 26 Then
> > >> XLColumn = Chr(64 + intCol) & ":" & Chr(64 + intCol)
> > >> Else
> > >> XLColumn = "A" & Chr(64 + intCol - 26) & ":" & "A" & Chr(64 +
> > >> intCol - 26)
> > >> End If
> > >>
> > >> End Function
> > >>
> > >> Just to get this Function out of the loop I tried hard coding the
> first
> > >> couple of columns ("A:A", etc.) but got the same error. So I 'think'
> > the
> > >> problem is not in the column returned by the function. (Can I use
> column
> > >> numbers instead? That would be a nice simplification. I tried that but
> > it
> > >> throws the same error as the letters.)
> > >>
> > >> The problem is in the line:
> > >>
> > >> objXLBook.ActiveSheet.Range(strColumn).ColumnWidth =
> > >> rsPLFields!fldPLFieldWidth * 1440
> > >>
> > >> which throws the error: Unable to set the ColumnWidth property of the
> > >> range.
> > >>
> > >> I don't really grok Office Automation, but I've used it a lot - by
> trial
> > >> and error and cribbing from code that works. I've tried twelve ways
> > from
> > >> Sunday to make this work but am stumped. But it's probably something
> > >> obvious to someone who understands Office Automation.
> > >>
> > >> MTIA
> > >>
> > >> Rocky
> > >>
> > >>
> > >>
> > --
> > 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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
More information about the AccessD
mailing list