ShrinkToFit Problems Using Automation

[BFarrell]

I am creating Excel reports out of access, and then reformatting the Excel
Files through Automation. This works great and I have had much success.

However, I am trying to set the ShrinkToFit property on 2 columns and it is
not setting this property to true.

The following code correctly sets the WrapText property, but does not set
the ShrinkToFit property.

Set xlObject = CreateObject("excel.application")
Set xlbook = xlObject.Workbooks.Open(path_filename)
xlObject.worksheets(Sheet_Name).Activate
With xlObject
.range("C:C").select
.Selection.wraptext = True
.Selection.ColumnWidth = 22
.range("D").select
.Selection.ShrinkToFit = True
.Selection.ColumnWidth = 25
End With

Any help would be appreciated!
Thanks!

 

[Kim Greenlee]

I know that programmatically creating Excel objects results in an Excel
instance that is not visible. I have also read that Excel was designed to be
run interactively and that if it’s not then some properties may not be set as
the developer expects. Soooo...with both of those thoughts in mind. Is it
possible that ShrinkToFit is not working as you expect, because Visible is
set to False? This is just a WAG and I have found no evidence to indicate
that it would work in that manner. However, I would try setting Visible to
True and see what happens.

Good luck,

Kim Greenlee