mf999
asked on
Access 2003 export to Excel 2007 from Access 2007
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.
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.
ASKER
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Where would I find the methods or named constants supported by the different versions of excel?
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.