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

x
?
Solved

Export Report to Excel and Format Cells

Posted on 2008-10-22
10
Medium Priority
?
476 Views
Last Modified: 2013-11-28
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.
0
Comment
Question by:gpotenza
  • 4
  • 4
  • 2
10 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 22781012
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")

Jim
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 22781333
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
xlApp.Quit
Set xlApp = Nothing

Regards,

Patrick
0
 

Author Comment

by:gpotenza
ID: 22787244
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?
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22788175
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).
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22788475
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?
0
 

Author Comment

by:gpotenza
ID: 22789444
Got it.  Is there a line I can add to automatically open the spreadsheet when it is created?
0
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 1000 total points
ID: 22791695


Dim stAppName As String

    stAppName = "excel.exe C:\MyPath\MyFile.xls"
    Call Shell(stAppName, 1)
0
 

Author Comment

by:gpotenza
ID: 22792004
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"
0
 

Author Comment

by:gpotenza
ID: 22792022
The value of stAppName seems to be fine.  It is getting chopped up on the call shell line.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22792305
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

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!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

971 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