nemesis7
asked on
Cannot use the Docmd.OutPutTo command to save query results as Excel file in Access
In my Access 2003 VBA code, I am looping through & outputting the results of a query to an Excel file to be saved at a specific location. I am using the code:
DoCmd.OuptputTo acOutputQuery, strSMTPquery, acFormatXLS, "C:\WeeklySheets\New Members\" & Date & " " & strChapter & " New Members.xls", False
I keep getting "Method or Data member not found" and the OutPutTo is highlighted. I am not sure if this is a Reference issue, becuse when I type Docmd & then ".", I get the drop down list of possible options, one of which is OutOutTo. Anyhow, just in cse, my references in order are in order of appearance:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft Excel 11.0 Object Library
Microsoft Activex Data Objects 2.0 Library
Please help!!!
DoCmd.OuptputTo acOutputQuery, strSMTPquery, acFormatXLS, "C:\WeeklySheets\New Members\" & Date & " " & strChapter & " New Members.xls", False
I keep getting "Method or Data member not found" and the OutPutTo is highlighted. I am not sure if this is a Reference issue, becuse when I type Docmd & then ".", I get the drop down list of possible options, one of which is OutOutTo. Anyhow, just in cse, my references in order are in order of appearance:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft Excel 11.0 Object Library
Microsoft Activex Data Objects 2.0 Library
Please help!!!
ASKER
No, your syntax does not work in the Immediate pane either. Also, I must point out that the Sub Procedure fails before it even starts, in other words, it is not even compiling. For example, my event is Private Sub cmdRun_Click(), & as soon as I click the Run command button, Private Sub cmdRun_Click() is higlighted in Yellow (i.e. execution stops at the beginning) & it has the OutPutTo highlighed in blue...ie, indicating that there is a problem with the OutPutTo, even though Access seems to be recogniziing it...becuase it includes it in the available options of the Drop down.
What can we try?
* Does it work in a new blank database with a simple table and a simple query?
* You probably didn't create something called DoCmd (since the dropdown works)...
* Have you tried a decompile?
* What are your security settings?
I just tried your exact line in Access 2003 and had no error.
(°v°)
* Does it work in a new blank database with a simple table and a simple query?
* You probably didn't create something called DoCmd (since the dropdown works)...
* Have you tried a decompile?
* What are your security settings?
I just tried your exact line in Access 2003 and had no error.
(°v°)
ASKER
* I havent tried this, will let you know in the evening
*Create something DoCmd? I am using DoCmd everywhere in my program, i.e., DoCmd.openform, DoCmd.CloseForm...& other places too. I dont need to create that.
* No, I have not tried a Decompile...whatt will that do?
* I dont think it has anything to do with Security Settings, but I will check, what should they be?
*Create something DoCmd? I am using DoCmd everywhere in my program, i.e., DoCmd.openform, DoCmd.CloseForm...& other places too. I dont need to create that.
* No, I have not tried a Decompile...whatt will that do?
* I dont think it has anything to do with Security Settings, but I will check, what should they be?
ASKER
Look at my References...do you see a problem with those?
No, I don't see problems in your list of references. DoCmd is an Access object, so it can't really be "missing".
About decompile... You simply start Access with the switch /decompile. This is equivalent to creating a new blank database and importing everything, as it deletes all partially compiled information. Think of it as the "compact and repair" of the VB project. See for example: http://support.microsoft.com/kb/819780 (different bug, but same solution).
Please try the command in a new blank database (you can also make sure the same references are used, if you like). Basically: if DoCmd.OuptputTo works in a new clean database, the problem is in your database (corruption, etc.). If not, the problem is in the installation (a library, etc.).
(°v°)
About decompile... You simply start Access with the switch /decompile. This is equivalent to creating a new blank database and importing everything, as it deletes all partially compiled information. Think of it as the "compact and repair" of the VB project. See for example: http://support.microsoft.com/kb/819780 (different bug, but same solution).
Please try the command in a new blank database (you can also make sure the same references are used, if you like). Basically: if DoCmd.OuptputTo works in a new clean database, the problem is in your database (corruption, etc.). If not, the problem is in the installation (a library, etc.).
(°v°)
ASKER
I dont think my DB is corrupted, can you tell me how I can fix the installation...if that is the issue. Is there a certain file I need to add & reference?
ASKER
Also, what about any other easy & quick 1 line method...like acTransferSpreadsheet? I dont know the details of that...suggest anything else that might help.
For the third time. Does the command work in a new blank database? I really cannot help you if you are not willing to do the most obvious tests. "I don't think my DB is corrupted" really isn't enough here.
Cheers!
(°v°)
Cheers!
(°v°)
ASKER
I feel so stupid....but the OutPutTo was spelled incorrectly....works great!
Ouch! That hurts. Sorry I didn't spot that... Success with your project! -- (°v°)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
? Date()
In the immediate pane? On many computers, it will contain characters that are invalid as file name. The same could be true for strChapter. Does the following work from the immediate pane?
DoCmd.OuptputTo acOutputQuery, "<valid query name>", acFormatXLS, C:\WeeklySheets\TEST.xls",
(°v°)