Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2840
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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