Export to an Excel-sheet, AUTOSIZING all the colums.

[Luc Ringoir]

HI

I open an Excel-sheet with a line Access-code

DoCmd.OutputTo acOutputQuery, "qryPlanningOnLine", "ExcelWorkbook(*.xlsx)", “C:\Planning\Planning.xlsx”, True

and it’s working fine.

However I would open this Excel-sheet, AUTOSIZING all the colums.

Is it possible?

 

[namliam]

Only way to do this is to open the file in excel using code and coding the autowidth ...

A quick google for you turns up this link (among others offcourse)
http://www.pcreview.co.uk/forums/autofit-excel-columns-access-vba-t1641527.html

 

[Rx_]

For general moving Access into Excel with code - this is still my favorite:
http://www.btabdevelopment.com/ts/default.aspx?PageId=10

Once the Excel document is open
Right click in the Ribbon (menu) and Customize the Ribbon
On the Right-Hand list box (Customize the Ribbon
the Developer box is unchecked - check it
Now the menu has "Developer"
When Developer tab is open, choose Record Macro
On the upper Left-Hand cell (just above 1 and just to the left of "A") choose
This selects the entire Worksheet
Put the mouse cursor just between the line between A | B the cursor will change to show the line is chosen - Double Click
Now, Stop Recording (menu)
Double click on the Macro box - Sub Macro1 will open and display
Cells.Select
Cells.EntireColumn.AutoFit

While the Macro Recorder won't take care of advanced programming, it will provide basic guidance for the Excel Object, Properties, and Methods.

This code can typically be placed back into the Access VBA. It will just need the object reference to Excel to run from there.

Example:
Dim MyExcel as Excel.Application
See code link above to open excel from Access and then run this extra:

MyExcel.Cells.Select
MyExcel.Cells.EntireColumn.AutoFit

I didn't mean to steal any thunder. They just gave me a new Xenon computer with SSD Hard drive, so I am re-setting up all of my Office applications. Including the Menu item for Excel Developer.
The Company Web Troll Monitor now blocks MZTools. Lucky I kept the last download. For a while, I was wondering if I could get to Bob Larson's site mentioned above.

 

[vbaInet]

While the Macro Recorder won't take care of advanced programming, it will provide basic guidance for the Excel Object, Properties, and Methods.

... and it never gives optimised code, for example the Select is not necessary.

 

[Luciano]

Hi
The following code is working fine:

Dim objXl As Object
Dim objActiveWkb As Object
DoCmd.OutputTo acOutputQuery, ”qryPlanningOnLine” , "ExcelWorkbook(*.xlsx)" , “C:\Planning\Planning.xlsx” , False
Set objXl = CreateObject("Excel.Application")
Set objActiveWkb = objXl.Workbooks.Open(“C:\Planning\Planning.xlsx”)
objActiveWkb.ActiveSheet.Cells.EntireColumn.AutoFit
objActiveWkb.Close SaveChanges:=True
objXl.Workbooks.Open (“C:\Planning\Planning.xlsx”)
objXl.Visible = True

 

[namliam]

Why would you, open, save, close, open ???

Why not stick to Open, Save ??

Did you add
Set objXl = Nothing
Set objActiveWkb = Nothing
To clean up your varaibles?

 

[Luciano]

Hi namliam

je hebt inderdaad overschot van gelijk, 'k heb mijn code aangepast en dit geeft een beter en sneller resultaat.
Thanks
DoCmd.OutputTo acOutputQuery, "qryPlanningOnLine, "ExcelWorkbook(*.xlsx)", "C:\Planning\Planning.xlsx", False
Dim objXl As Object
Dim objActiveWkb As Object
Set objXl = CreateObject("Excel.Application")
Set objActiveWkb = objXl.Workbooks.Open("C:\Planning\Planning.xlsx")
objActiveWkb.ActiveSheet.Cells.EntireColumn.AutoFit
objXl.Visible = True
Set objXl = Nothing
Set objActiveWkb = Nothing

 

[namliam]

Google translate fails

Though you are no longer saving your excel sheet after your Autofit, thus if the user closes it without saving, the autofit is undone.

 

[Luciano]

How saving the Excel-file before openeing ?

 

[namliam]

objActiveWkb.Save

Or something simular I think