[AccessD] Setting Column Widths on Excel Spreadsheet

[Rocky Smolin]

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