Solved

Access 2003 export to Excel 2007 from Access 2007

Posted on 2014-11-06
5
134 Views
Last Modified: 2014-12-16
I would like to build a command button on a form that will export 5 queries on 5 tabs within an Excel 2003 workbook.

The form will be built in Access 2007 and used in a mixed office 2003 and 2007 environment on mainly Windows 7  (and a few XP machines) where users are mainly using runtime Access 2003.  I can also see that some users will want to upgrade to Office 2010 or later in the next year or so while others have no pressing need to and will continue using office 2003.

So I need the output to be   a .xls file with Query 1 on Sheet 1 , Query 2 on Sheet 2....etc within the same workbook.

Can anyone help me with the required VBA to do this?    I am assuming I should use the %temp% folder to do this so as not to get into trouble with different directory structures for the different versions of office etc.
0
Comment
Question by:mf999
  • 3
  • 2
5 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40429586
Transferring data between Office components of different versions is problematic, because of confusion over shared components.  If you have to work with different Office versions, it is best to create VMs and have one Office version in each VM.  If you have to get this to work if at all possible, then for each Access/Excel combination you would need to set a reference to the correct version of Excel, and then write VBA code to put the Access data into a workbook, sheet by sheet.  My Working with Excel ebook could be helpful here, but I think the mixed Office version environment would make it a difficult project.

It is also possible that there might be a problem with using a form created in Access 2007 (presumably in an .mdb database) in 2003.  If at all possible, create the form in Access 2003.
0
 

Author Comment

by:mf999
ID: 40432190
HI Helen, thanks for your reply.  Please excuse my limited knowledge of VBA.  What do you mean by create VM's . How would I set the reference to the correct version of Excel? Would this need code to check which version of Office is on the PC before you open the form and then use code to set the reference library?   Does you book have an easy to follow example of outputting to a workbook sheet by sheet ?
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 40472486
I use VMware to create VMs.   There is also a Microsoft program for this purpose.  I have one computer with Office 2010 installed, and also three VMs, one each for Office 2003, 2007 and 2013.  Thus I can do testing in each of these Office environments.  If you have to support a mixed Office version environment, you will have many problems.  

In some cases, it may be possible to get data transfer to work, but there are no guarantees.  To test this, open the References dialog in the Access database (Tools, References in the VB window) and uncheck any references' marked MISSING, then look for and check (if found) the item for the Excel version that you have installed.  Then try running code, making sure that it doesn't use any methods or named constants not supported by that version of Excel.
0
 

Author Comment

by:mf999
ID: 40482417
Where would I find the methods or named constants supported by the different versions of excel?
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40482898
In my Working with Excel ebook, for one place.  Here is the list for Office 2007:

Excel named constants
The Lotus constants were removed in Office 2010; otherwise the list is the same; the Excel 2007 constants also work for Excel 2010.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
This collection of functions covers all the normal rounding methods of just about any numeric value.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now