Results 1 to 7 of 7
  1. #1
    jerryrs is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2010
    Posts
    3

    Question Formatted Excel Output

    Hi,
    I would like to have an output to Excel, but would like to see a formatted data in Excel not 'raw' data. Is it possible?


    I am using the following command for Excel output:
    DoCmd.OutputTo acOutputReport, ReportName, acFormatXLS, ReportName & ".xls", True

    Thanks for any ideas.

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    am not sure what you mean... what is an example of 'raw' vs' formatted' in excel?

    but in any case - have you explored the docmd.transfer alternatives such as text or spreadsheet (rather than report)....and with an export specification made & used I would think you should get the data as you seek.....

  3. #3
    jerryrs is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2010
    Posts
    3
    By formatted file, I meant that columns can be formatted(number, decimal points, justifications etc.) plus some nice headers, colors etc.
    When I output data to excel, it comes w/out all this nice stuff - like 'raw' file.

  4. #4
    SteveH2508 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Chelsea, London, England
    Posts
    117
    Quote Originally Posted by jerryrs View Post
    By formatted file, I meant that columns can be formatted(number, decimal points, justifications etc.) plus some nice headers, colors etc.
    When I output data to excel, it comes w/out all this nice stuff - like 'raw' file.
    The only way you will achieve this is by using automation programming Excel VBA from within Access. This is not trivial.

  5. #5
    SteveF is offline Generally AccessAble
    Windows XP Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Location
    Fourth Corner
    Posts
    123
    Quote Originally Posted by jerryrs View Post
    By formatted file, I meant that columns can be formatted(number, decimal points, justifications etc.) plus some nice headers, colors etc.
    When I output data to excel, it comes w/out all this nice stuff - like 'raw' file.
    Basic number formatting (decimal points, for example) can be easily handled by formatting the data in an Access query, then using TransferSpreadsheet to turn that into an Excel file.

    Text formatting (fonts, colors, size, bold, etc.) is an entirely different kettle of fish, and you would need to go down that -advanced- path of Automation Excel VBA programming.

    Not what you probably wanted to hear, but there you are.

    Steve

  6. #6
    thhui is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Feb 2009
    Posts
    235
    Access Query just gives the raw data you want.
    So it has to be done in excel vba to add formatting to the data.
    Calling outside from access to use excel vba to work is possible however.

  7. #7
    NISMOJim is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    273
    I'm no expert by any means, but what I have done in the past is do a transfer spreadsheet macro to an Excel worksheet. Then I add a second worksheet where each cell references the data you want from the first sheet. You can format the second sheet any way you want it to look, and save it so the spreadsheet opens to the formatted page.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. URGENT HELP: output to excel
    By jerryrs in forum Import/Export Data
    Replies: 1
    Last Post: 12-22-2010, 12:18 PM
  2. specific record output to .pdf
    By REBBROWN in forum Reports
    Replies: 1
    Last Post: 09-25-2010, 07:35 AM
  3. Best way to import badly formatted data
    By mkallover in forum Import/Export Data
    Replies: 3
    Last Post: 09-16-2010, 01:59 PM
  4. Replies: 3
    Last Post: 11-19-2009, 09:15 PM
  5. Displaying data formatted
    By Zoroxeus in forum Forms
    Replies: 0
    Last Post: 03-14-2006, 09:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums