Results 1 to 4 of 4
  1. #1
    Join Date
    May 2006

    Question Unanswered: Exporting DB to Excel w/ reasonably good formatting

    I have a problem. As you may have seen from my intro post in the new members section, I need to use Access for my job, but have been given no training in it. I am only days away from writing death threats to Bill Gates. Can anyone save me from a lengthy prison sentence?!

    I need to use Access to capture data on approx 85 organisations and: what targets they are aiming towards (many to many relationship); who the contacts are for each organisation (many to many); which areas of the country they cover (many to many) etc etc.

    I can do this fine, but I then need to:

    - Export/Convert this data into an Excel spreadsheet (to create an "update template") which can be emailed out to the contacts for each organisation for them to update the info on each organisation and then mail it back to me, for me to update the DB. But, when I try to export to Excel, instead of a reasonably professional looking product, it dishes up a jumbled mess of thousands of pages of repeating fields. For eg, it will repeat the name and targets over and over. Exporting to Word is an even bigger disaster. There's text all over the place. It doesn't even export in Word format, it can only export in RTF. I have tried exporting forms, queries and reports and none of them work.

    The update template can;t be in an Access format because not all the contacts have Access.

  2. #2
    Join Date
    Apr 2006
    Huddersfield, UK



    There can be problems when exporting information from access to any other of the office software.

    The problem you are having (with repeated text) sounds very much like an issue with a query. Sometimes queries can create thier own sets of problems, and often do repeat data, if these problems are not remedied.

    Use Group BY functions in your query to help eradicate the long lists of repeated text, alternatively, use groupings, with headers and footers in a report, before exporting to Excel.

    When you have your report/query right...

    When it is exported to excel, it will ook awful to be honest, it'll be plain old text/number configuration, and won't look very profesh at all. My idea would be to get a PDF printer driver (type PDF Brewer in google for freeware) and send it as a PDF and email to your contacts, the only issue with this is it isn't open for data manipulation. (ie your contacts can't take the data and use it in thier own pie charts etc - can be a good thing as well as a bad thing)

    Hope this helps;

    sometimes simple is best.... and i'm just a simple fellow.

  3. #3
    Join Date
    Dec 2002
    Préverenges, Switzerland
    Dan is right: if you have a lousy query you will end up with a lousy export.

    first: beat the query into shape!

    from there, your options are limited: A's XLS export is factually fine but useless for formatting layout in any but the simplest cases.

    i use a couple of different ways:

    a suitably formatted XLS blank
    drive Excel from A code
    open a recordset on your query and manually go thru row by row inserting whatever you need where you need it. it sounds awful, but it is not so bad unless you need to dump thousands of rows. the edits is the reverse.

    otherwise, you might find some inspiration here
    as you see from the thread - nobody admits to having cracked it completely, but adding pootles style sheet idea could make the overall scam very interesting. from this route is uncharted territory. here's your chance to make a name for yourself ...and probably a commercially-viable product.
    you saw it first on dbforums/f84 folks!

    currently using SS 2008R2

  4. #4
    Join Date
    May 2006
    thanks for the info guys. i've managed now to do a reasonable export to excel. i first used queries to group the data, then reports to remove duplicates, then i export them to excel in separate tabs for each one-to-many or m-to-m relationship. It's not pretty but it will do for now.

    pootle's explanation is a little over my head right now. hopefully it will make more sense when i've learned more of the jargon.

Posting Permissions

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