Export Report to Excel and Format Cells

Is there a way to export an access report into excel and specify the formatting of some of the cells.  In this case I would like a particular cell to be formatted as "wrap text" when the report is exported.
Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:
Hello gpotenza,

Slight tweak to Jim's approach:

Dim xlApp As Object, xlWb As Object, xlWs As Object
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("NameOfQuery")

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add("c:\SomeTemplate.xlt")
Set xlWs = xlWb.Worksheets("Name of Ws")

With xlWs
    .[a2].CopyFromRecordset rs
    'maybe other code here to do formatting or whatnot
End With

xlWb.SaveAs "c:\folder\subfolder\file.xls"
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing


I would recommend using a preformatted template and doing a save as when the file is built, see example for opening:

Set objWkb = objXL.Workbooks.Open("C:\XLTemplates\V9_ARTop10PastDue.xls")
Set objSht = objWkb.Worksheets("Top 10 Past Due Customers")

gpotenzaAuthor Commented:
I am receiving a "subscript out of range error" on the following line:

Set xlWs = xlWb.Worksheets("Validation")
Maybe I am not clear what should be in the quotes.  Can you clarify?
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Patrick MatthewsCommented:
gpotenza said:
>>Set xlWs = xlWb.Worksheets("Validation")
>>Maybe I am not clear what should be in the quotes.  Can you clarify?

It should be the name of a worksheet in the Excel file (created based on the template).
Most likely you have a numeric value Dim as Integer where it should be Long. Also, how big is the recordset being pushed out to Excel?
gpotenzaAuthor Commented:
Got it.  Is there a line I can add to automatically open the spreadsheet when it is created?
jmoss111Connect With a Mentor Commented:

Dim stAppName As String

    stAppName = "excel.exe C:\MyPath\MyFile.xls"
    Call Shell(stAppName, 1)
gpotenzaAuthor Commented:
Any idea how I can deal with spaces in the name of the file.  There is no way for me to eliminate the spaces.  It seems to break apart the filename at the space and treat it as a separate file.  Here is what I am trying to open:

 stAppName = "excel.exe \\kfinance\DynGP_Shared\StockCounts\ValidationReports\" & strRespID

the value of strResp ID = "RRL BB Mgr.xls"
gpotenzaAuthor Commented:
The value of stAppName seems to be fine.  It is getting chopped up on the call shell line.
I never opened an Excel file from Access; this works for me. I addition to the code, set a reference to Microsoft Excel 11.0 Object Library
Dim appExcel As Excel.Application
 Dim myWorkbook As Excel.Workbook
  Set appExcel = CreateObject("Excel.Application")
    Set myWorkbook = appExcel.Workbooks.Open("C:\MyFolder\My File b.xls")
    appExcel.Visible = True
    Set appExcel = Nothing
    Set myWorkbook = Nothing

Open in new window

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.

All Courses

From novice to tech pro — start learning today.