[AccessD] Setting Column Widths on Excel Spreadsheet

Rocky Smolin rockysmolin2 at gmail.com
Thu Mar 23 11:24:04 CDT 2023 

Where's the challenge in that?  :o)  Fortunately I figured it out and, in
the end it was (as all these brain fargers are) a pretty simple fix.

r

r

On Thu, Mar 23, 2023 at 5:33 AM Jim Dettman via AccessD <
accessd at databaseadvisors.com> wrote:

> 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
>
> --
> 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