[AccessD] Setting Column Widths on Excel Spreadsheet

[Rocky Smolin]

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