Results 1 to 7 of 7
  1. #1
    sensetech is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    4

    Access 2007 query export to Excel "feature"

    My client has an Access 2003 database with many queries, some of whose names starts with 200_. There is a VBA subroutine which loops through each QueryDef in the database and uses the TransferToSpreadsheet command to export the ones starting 200_ to an Excel workbook in Excel 2003 format where they end up on worksheets of the same name. It has all worked fine for several years.



    We are testing a move up to Access 2007 and Excel 2007, making the relevant changes to the VBA code so it now produces an Excel 2007 .xlsx workbook. At first glance everything is again fine BUT then we notice that the resulting worksheet names have been prefixed by _, so query 200_Query1 appears as worksheet _200_Query1. This is not acceptable to the client!!!

    I did some further tests and found that the "feature" seems to affect any query name starting with a number whereas those starting with a letter are unaffected. It also happens if the export is manual rather than via VBA automation. It doesn't happen with the SendObject command but that seems to be limited to 64k rows which defeats the main object of moving up to 2007.

    Using Google I can't find any mention of this so does anyone on here know what's going on and if there's a fix?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,620
    If the exports are all by VBA code and this doesn't happen with VBA procedure then why is this an issue?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    sensetech is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    4
    Sorry, I must have mislead you somehow. The problem happens if I do the export manually or if I use VBA TransferTo Spreadsheet. If I change the VBA to use SendObject then it's OK but limited to 64k rows.

    Bottom line is that something has changed between Access/Excel 2003 and Access/Excel 2007 and I need to whether there's a fix (that doesn't involve renaming the queries!)

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,620
    The perils of upgrade!

    I think options are:

    1. change query names

    2. more code to change names of the worksheets
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    sensetech is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    4
    Yep, that's what I'd thought unless someone could tell me about a new option or whatever that I was missing in Access 2007. I'll persuade the client that the 'query rename' option is best as that feels cleaner than post-export code to rename the worksheets; there's already lots of weird code in there from the guy who put the app together initially.

    As you have Access 2010, could you give it a try (manual export) to see what happens there? Any query whose name starts with a number should test it out.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,620
    Yes, get the same result with Access 2010.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    sensetech is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    4
    OK, thanks.

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

Similar Threads

  1. Replies: 4
    Last Post: 02-13-2013, 02:35 PM
  2. Export "Query or Report" to a "Delimited Text File"
    By hawzmolly in forum Import/Export Data
    Replies: 3
    Last Post: 08-31-2012, 08:00 AM
  3. Replies: 2
    Last Post: 03-29-2012, 08:49 AM
  4. Replies: 2
    Last Post: 11-04-2011, 02:45 AM
  5. Replies: 0
    Last Post: 09-25-2008, 12:19 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