Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2627
  • Last Modified:

Help with formatting column widths in vba upon export to excel from access.

I've got a query in an access database which I'm using in some vba code.  I'm exporting its results to an Excel spreadsheet.  All is working well except that the columns are all the same width and rather narrow.  How can I cause them to automatically adjust to appropriate widths?

Here is the code:
DoCmd.OutputTo acOutputQuery, "OptCouncilRpt", acFormatXLS, "C:\Temp\OptCouncilRpt.xls"

Open in new window

0
David L. Hansen
Asked:
David L. Hansen
  • 2
  • 2
2 Solutions
 
SharathData EngineerCommented:
0
 
Rey Obrero (Capricorn1)Commented:
after exporting to excel, you have to open the excel file using vba to set the formatting

DoCmd.OutputTo acOutputQuery, "OptCouncilRpt", acFormatXLS, "C:\Temp\OptCouncilRpt.xls"
                                 

dim xlObj as object
set xlobj=createobject("excel.application")
      xlobj.workbooks.open "C:\Temp\OptCouncilRpt.xls"

   
            xlObj.activeSheet.usedrange.columns.autofit

            xlObj.activeworkbook.save

           xlObj.quit
1
 
David L. HansenProgrammer AnalystAuthor Commented:
Sounds great capricorn, there must be a small problem though.  Your code runs without error but only creates the file, it does not open.  Not sure why.
0
 
Rey Obrero (Capricorn1)Commented:
<only creates the file, it does not open. >

the code open it, hidden, formatted the column width and saved.

open the excel file and see.

do you want it to stay open?
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Yes, sorry for forgetting about this.  I did want it to open automatically.  I found the code I needed too.  Thanks a ton.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now