[AccessD] Setting Column Widths on Excel Spreadsheet

[Rocky Smolin]

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