• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 985
  • Last Modified:

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!!!
0
nemesis7
Asked:
nemesis7
  • 6
  • 5
1 Solution
 
harfangCommented:
What do you see when you type:

    ? 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", False

(°v°)
0
 
nemesis7Author Commented:
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.
0
 
harfangCommented:
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°)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
nemesis7Author Commented:
* 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?
0
 
nemesis7Author Commented:
Look at my References...do you see a problem with those?
0
 
harfangCommented:
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°)
0
 
nemesis7Author Commented:
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?
0
 
nemesis7Author Commented:
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.
0
 
harfangCommented:
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°)
0
 
nemesis7Author Commented:
I feel so stupid....but the OutPutTo was spelled incorrectly....works great!
0
 
harfangCommented:
Ouch! That hurts. Sorry I didn't spot that... Success with your project! -- (°v°)
0
 
Computer101Commented:
PAQed with points refunded (50)

Computer101
EE Admin
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now