How to control the width of the excel cell?

[sabavno]

Hi

I export my Access table to the excel spreadsheet...One of the fields is quite long, but when it all is transfered to the spreadsheet, the cell's width is fixed. I know you can change the excel cell's width by simply dragging cell's border to the right...but...can I export the table that way so the user wouldn't have to expand all the cells in the Excel?

 

[Sawatzky]

dear sabavno,

assuming exsheet is your excelsheet
try this:


exsheet.Cells.Select
exsheet.Cells.EntireColumn.AutoFit


HTH

regards Astrid

 

[sabavno]

Ok
Here is my code:

DoCmd.OutputTo acOutputTable, "Wbt_Orders", acFormatXLS, "V:\Polina\WbtOrders - " & datExst & ".xls"

Where should I put the lines
exsheet.Cells.Select
exsheet.Cells.EntireColumn.AutoFit
???

 

[Sawatzky]

Dear sabavno,

sorry I did not think it over properly:

try this

the docmd.outputto's last option is the name of the template to use for output.

so you create a template for your excelsheet , where you put the code into the open event:
cells.select
cells.entireColumn.autofit

So when your user opens the new excelsheet , the macro will be executed.

HTH

regards Astrid

 

[fredk]

Hi HTH - One question - Where do you place the code in excel?

Thanks

 

[buggyman]

hi fredk,

just saw your question here....
If I understand your question correctly, my answer would be:

If you want the code to run immediately as the Excel workbook is opened, you should, as what HTH said, put the code in the Open Event "shell" of the Excel workbook (in this case, your Excel template).

Go to Tools > Macro > Visual Basic Editor
And then in the Project window of this template, select "This Workbook", double click on that.
You would see two scroll-down list box of "(General)" and "(Declarations)" now in the "big/main" code window.
Choose "Workbook" instead of "(General)" and make sure that the scroll-down list box on the right would now say "Open".
And you can just put the code between these lines then:

Private Sub Workbook_Open()

'put your codes here

End Sub


Hope this may answer your question!

-jd- = ]

 

[Sawatzky]

Dear fred, dear buggyman,

just for your information

HTH = Hope That Helps

and my NAME is Astrid

regards

 

[fredk]

Thanks Buggyman, that is exactly what I wanted to know - I appreciate it - Sorry about that Astrid!!!

 

[buggyman]

Haha... = ] = ]
I was kinda confused too actually....
If HTH were your name, what Astrid was.... = } = ]
Sorry about that.. !! = }

no problem fredk! = ]

-jd-

 

[Sawatzky]

Dear fred, dear buggyman,


I am really amused.

Dear Sabavno,

has your question been answered fully now ?

regards Astrid