Results 1 to 8 of 8

Thread: Export to Excel

  1. #1
    Join Date
    Apr 2006
    Posts
    11

    Question Unanswered: Export to Excel

    Greetings,

    I have an Access 2000 Report of formulas and numbers that I want to export to Excel where all sorts of great things happen to the data. When I click on "Analyze It with Microsoft Office Excel" the data ends up in a spreadsheet - all in one row.

    This causes me grief in two ways: I'm exporting 280 fields (more than the number of columns in Excel), and it requires ridiculous amounts of maping.

    Is there a way to export the data that keeps a table-like format?

    Thank you in advance!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Why not just export the underlying query of the report (saved as a query) instead of Analyze it with Excel from the report side of it.

    A button on a form with an onclick with the command:

    docmd.transferspreadsheet ...."queryname"..... would do what I think you want it to do (just keep in mind that excel can only handle about 65,000 rows of exported data).

    i.e. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryName", "C:\MyFileName.xls", True

    If you're going to use the Analyze option, make your Access report as excel friendly as possible (i.e. no headers, etc.), just the columns/rows of data with a border around each field.
    Last edited by pkstormy; 10-06-06 at 18:00.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Apr 2006
    Posts
    11
    Most of the fields in the report have contain this sort of stuff:
    =DCount("[No]","[qryGlobalReport]","[Type] = 'Multi-Family' And [Valuation] = 'Evaluation'")

    They add up various data from the tables. The report relies on a query, but the query does not contain all of the calculations.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Then I'd try to make the report look like excel as much as possible (with borders around the fields and no headers, blank lines, etc.) You might also be able to use your DCount as an expression in your query.
    Last edited by pkstormy; 10-06-06 at 18:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Apr 2006
    Posts
    11
    I think I have done exacttly what you suggest. (see attached screenshot) No spaces, headers, etc. and with borders around each cell. Any other thoughts? All of my other reports export beautifly.
    Attached Files Attached Files

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yep, that's almost always worked for me. Have you tried re-creating the report just in case something got changed somehow? Perhaps copy one of the other reports which worked and change the recordsource/fields on the report. Or maybe because the first row/column is numeric is causing problems - how about putting in a header row above the numbers? Other than that, I can only suggest exporting out the query.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Apr 2006
    Posts
    11
    Per your suggestion, I tried recreating the report and using a report that works and adding the correct fields and the same thing occurs. It leads me to think that it must have something to do with the code that is in the cells.
    Sound possible?

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Either that or not having a header row somehow affects it. Maybe having your expression in the query might also work. Either that or you can also do a make-table query before opening the report and have the report recordsource = to the new table (I'm assuming you need the report view for some reason.)
    Last edited by pkstormy; 10-07-06 at 19:47.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •