Excel file export auto cell width (1 Viewer)

megamef

Registered User.
Local time
Today, 11:21
Joined
Apr 18, 2013
Messages
161
Hi All,

I have the following code to export a query into a excel file:

Code:
Dim outputFileName As String

outputFileName = "C:\AccountSpreadsheet\test.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Q_Search_Invoices", outputFileName, True

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

xlApp.Workbooks.Open "C:\AccountSpreadsheet\test.xls", True, False

Set xlApp = Nothing

This works almost exactly how I want it to work.

The only thing wrong is that the columns are all the same width and they are all to narrow.

Is there a way to make the columns automatically become the width of the longest text within them (exactly what happens when you double click on the side of a squashed column in excel)?

Cheers

Steve
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:21
Joined
Aug 11, 2003
Messages
11,696
I think this should work...
Code:
xlApp.activeworkbook.Cells.EntireColumn.AutoFit

Otherwize some other variation of: Cells.EntireColumn.AutoFit
 

megamef

Registered User.
Local time
Today, 11:21
Joined
Apr 18, 2013
Messages
161
Yes I think you're right as when I made a macro in excel it gave me the code

Cells.Select
Cells.EntireColumn.AutoFit

but unfortunately when I try

xlApp.activeworkbook.Cells.EntireColumn.AutoFit

I keep getting the

"object doesn't support this property or method"

error
 

Bilbo_Baggins_Esq

Registered User.
Local time
Today, 06:21
Joined
Jul 5, 2007
Messages
586
Maybe try something like this:
Code:
Dim xlApp as Excel.Application
Dim xlWB as Excel.Workbook

Set xlApp = New Excel.Application
Set xlWB.Workbooks.Open "C:\AccountSpreadsheet\test.xls", True, False

xlWB.Columns("a:c").EntireColumn.AutoFit

I've used "a:c" but you could replace with whatever columns you know you're using.
Or, it may also need to be:
Code:
xlWB.Worksheets("Worksheetname").Columns("a:c").EntireColumn.AutoFit
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:21
Joined
Aug 11, 2003
Messages
11,696
cells as well as columns I think are worksheet functions....
xlApp.activeworksheet.Cells.EntireColumn.AutoFit
??
 

Users who are viewing this thread

Top Bottom