Need to export query results to Excel

[Bhaughbb]

Unfortunately I'm a bit short on knowledge of VBA and only know enough to start being dangerous. I've spent 3 days researching these forums and several access programming books in an effort to solve my problem but have not been able to sucessfully apply code examples I've seen to my database.

I have a dynamic query I'm creating in VBA based on a form with a dozen options and currently have it stored in a string variable. I need to export it to Excel (a file or just open it in a new spreadsheet in Excel) which I wouldn't think is too difficult but I have been unable to get even a simple test to work once yet. My requirements are when exporting to an excel file the user needs to be able to choose the directory and filename as the .mdb is located on a mapped location from the server that may vary by user and some users may be using remote desktop or VNC to get to it, also several users may be in the database at any given time.

In the past I've had actual queries in the .mdb that I just ran a DoCmd.OutputTo on and everything runs great.

I'm not sure the best solution to my problem and I've been unable to get any method I've seen to work. Any advice on the method and if possible a straight forward example would be incredibly helpful.

 

[bonekrusher]

Have you tried Office Automation?

 

[bonekrusher]

try here

http://www.vbaexpress.com/forum/showthread.php?t=7355&highlight=export+table+excel

 

[Bhaughbb]

Finally had time to get back to this project, That code looks useful but I'm suddenly hitting an error I've not had to deal with before, it's breaking when I try to define a variable as QueryDef. The exact line is's breaking on is:

Dim str1Sql As QueryDef

I can't use any libraries that wouldn't be turned on by default in Access as several users who's systems I don't have control over will be running this as well. Advice?

 

[RV]

Try this:

Dim str1Sql As DAO.QueryDef

I can't use any libraries that wouldn't be turned on by default in Access

But VBA requires specific libraries to be ticked.
And that needs to checked and done per single PC....

RV

 

[Bhaughbb]

I thought so, just couldn't remember for sure. Time to look for another way to code this, I can't use any libraries that don't install with Access 2003 by default due to the lack of control over users' systems that I have. Would ADODB be a good choice?