Setting Excel Column Width from Access VBA Module 2

[CuckooNut]

Hello,

I have an Access VBA module that outputs the results of a query to an Excel 2007 spreadsheet. I have written the code to format the cells as needed, and everything seems to work except for one thing: for some reason, it just ignores the code I have written for setting column width.

Here is how I have it written right now:

With ExcelWkb.ActiveSheet.Range("A:A")
.ColumnWidth = 21
End With

It seems to me this should work, but it doesn't. It doesn't give me any errors or anthing, it just doesn't do anything. Is my syntax wrong?

Thanks in advance for any help.

 

[SkipVought]

hi,

Try this...

    With ExcelWkb.ActiveSheet.Range("A:A")
        [highlight].EntireColumn[/highlight].ColumnWidth = 21
    End With

It woud actually be better, if you can, to explicitly referece the sheet

    With ExcelWkb[highlight].Sheets("YourSheetName")[/highlight].Range("A:A")
        .EntireColumn.ColumnWidth = 21
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[CuckooNut]

Hi Skip,

Thanks for your reply. I did as you suggested, but it still does nothing. Here is what I have written:

With ExcelWkb.Sheets("Sheet1").Range("A:A")
.EntireColumn.ColumnWidth = 35
End With

What I don't understand, is why all of the other formatting seems to work. In fact, just to experiment, I added lines to the above code to change the cell color and bold the font, and it worked perfectly. The same is the case with other columns I am formatting. For some reason, it just won't do anything with the column width.

Any ideas?

 

[SkipVought]

You need to see what exact object ExcelWkb refers to.

Use faq707-4594 to help you DISCOVER what is happening.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Is it possible that you over-write the ColumnWidth with something like:
[tt]
.Columns("A:Z").EntireColumn.AutoFit
[/tt]
That's what I usually do at the end.

Did you step thru your code and see what your line of code actually do, if anything?

Have fun.

---- Andy

 

[CuckooNut]

Skip/Andy,

Thanks for your help. Andy, you were spot on. I found a line farther down in my code that executed the autofit and thus overwrote my static column widths. Feelin' pretty sheepish right now!

Skip, even though I ended up not needing it this time, that FAQ is extremely useful, so thank you.

 

[SkipVought]

I know, I know. Same ol' same ol': "Wait time next time!" ;-)

Glad you found it helpful!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]