Solved

How do I adjust excel column widths when exporting from Access with DoCmd.OutputTo

Posted on 2008-10-12
2
386 Views
Last Modified: 2013-11-27
Hi I am using the following code to export an excel spreadsheet from Access.

DoCmd.OutputTo acOutputQuery, "qry_export", acFormatXLS, rs!state_FK & "-" & rs!Declaration & " Loan Closing " & DatePart("m", dteReport) & "-" & DatePart("d", dteReport) & "-" & DatePart("yyyy", dteReport) & " .xls"

What vba code do I use to make the second column ("B") export with the autofit option so the cell contents aren't truncated?
0
Comment
Question by:BradleyOnTheRoad
2 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 22699686
Since you need to automate Excel to do the column width, I'd just do this:

Dim xlApp As Object, xlWb As Object
Dim rsOut As DAO.Recordset

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
With xlWb
    Set rsOut = CurrentDb.OpenRecordset("qry_export")
    With .Worksheets(1)
        .[a1].CopyFromRecordset rsOut
        .Columns(2).AutoFit
    End With
    .SaveAs rs!state_FK & "-" & rs!Declaration & " Loan Closing " & Format(dteReport, "yyyy-mm-dd") & ".xls"
    .Close False
End With

Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
0
 

Author Closing Comment

by:BradleyOnTheRoad
ID: 31505514
Thanks. That'll do the trick.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question