[AccessD] Setting Column Widths on Excel Spreadsheet

[Borge Hansen]

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
>