Results 1 to 8 of 8
  1. #1
    stevemills04 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    4

    Access Query with links to Excel, export to XML and back into Excel

    Hi All,

    Thank you so much for any help you can provide. I am using Excel and Access 2010.

    I have an excel spreadsheet with 8 tabs. They are all in the same format and column order. They are employees grouped by region. My ultimate goal is to merge all of these onto one excel tab, relatively instantly. I created a master tab and tried doing array formulas and Vlookups, it worked but my spreadsheet was way too slow.

    My solution? Import and link them to an Access database, step complete. Create an XML export then import into Excel.

    My problem? The only way to update the excel tab with the combined tabs is to save the excel file after changes, go back into Access, re-export to XML, then go back into excel and refresh the data.

    My questions, is there any way to automate this process to the point that I can change excel, save, then hit refresh on my excel tab with the XML import to auto-update? Or, if there is a complete better solution, I will use it.

    Thank you so much!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,728
    Why use Excel at all?
    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
    stevemills04 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    4
    We have users enter data on their tab. Some of the options require data validations, formulas, etc.. Very few people use Access so it would require an extra learning curve, something we are not willing to handle at the moment.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,728
    I expect it can be automated with VBA code. Next question - why XML? Access has tools for export direct to Excel.

    Might be easier to abandon Excel and migrate totally to Access.
    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
    stevemills04 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    4
    I thought it would be VBA, just not able to find any help with the coding. I have exported to Excel several times and it says complete but never exports. When I open the file the information is nowhere to be found. The only thing I have had success with was XML.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,728
    There is the import/export wizard, which I suppose you use for the XML export. I don't do a lot of export to Excel but I have used both the wizard and various forms of VBA code to accomplish.
    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
    stevemills04 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    4
    I am using the wizard, but like I said, I have to go back into Access, run the export process, then jump back into excel and refresh. Was just looking to automate the export process.

    Thank you for helping!

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,728
    If you want to see examples of other export methods, review http://forums.aspfree.com/microsoft-...el-414974.html
    Look at the two additional links in the last post.
    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.

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

Similar Threads

  1. Customizing Links to linked Excel data
    By bigmike in forum Import/Export Data
    Replies: 3
    Last Post: 01-28-2013, 03:10 PM
  2. Access Query export to Excel form
    By Odogggoneit in forum Access
    Replies: 1
    Last Post: 01-14-2013, 05:16 PM
  3. Replies: 3
    Last Post: 10-07-2011, 07:49 AM
  4. Export table to excel using excel template
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 1
    Last Post: 06-27-2011, 02:10 AM
  5. Export from Access to Excel
    By Eowyne in forum Import/Export Data
    Replies: 5
    Last Post: 04-23-2011, 07:08 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