[AccessD] Setting Column Widths on Excel Spreadsheet

Jim Dettman jimdettman at verizon.net
Thu Mar 23 07:32:51 CDT 2023 

Rocky,

Worksheets("Sheet1").Cells.EntireColumn.AutoFit
Worksheets("Sheet1").Cells.EntireRow.AutoFit

 And be done with it.

Jim.

-----Original Message-----
From: AccessD On Behalf Of Rocky Smolin
Sent: Wednesday, March 22, 2023 9:16 PM
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