Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64

    Unanswered: Exporting to Excel

    I know it's fairly easy to export an object such as a table or query to excel, but is it possible to export to a pre-defined destination excel table? I have a query in Access which I would like to export to an Excel spreadsheet which I have already set up with various formulas. In other words, I would like to populate the spreadsheet automatically with the contents of the Access query (hope that makes sense!)
    Any suggestions or advice would be much appreciated.

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Look in the code bank for Excel Automation.

  3. #3
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    Thanks - I just checked that out, but couldn't really see exactly what I was looking for. I get a bit nervous around modules and vb code and stuff like that as I don't fully understand what's going on with it and how it works! As I said, I'd like to be able to squirt the data from a query straight into a spreadsheet which already has formulas in it.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by kabuki
    I get a bit nervous around modules and vb code and stuff like that as I don't fully understand what's going on with it and how it works!
    You will struggle a bit then trying to do relatively complex stuff if you aren't prepared to get your boots wet!

    Start with the most basic export and ignore the rest. Read up before hand on debugging and stepping through code - go through a line at a time as it executes and move on as you figure out what each line does.

    The key process is populate a recordset, set a reference to an excel workbook and use the ... umm.... I've forgotten the name - PopulateFromRecordset method.

  5. #5
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    LOL! Hope my boots haven't got any holes in them! OK I'll give it a go and see how I get on - I always prefer to work things out myself if I can as it sticks in the brain better that way, but sometimes when I'm against a deadline I have to look for a shortcut. Does the PopulateFromRecordset method (presumably part of the vb code) go on the Access side or the Excel side? In other words, are we "Exporting to" Excel from Access, or "Importing from" Access to Excel? And does it matter? (Or even make sense)

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The latter & yes it does. Don't take my word for it on the method name - search for "recordset" and skip over the stuff that is irrelevent (Such as Dim rst As DAO.Recordset).

Posting Permissions

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