VB help needed!!!!!

[Nittylions]

I'm t rying to export multiple spreadsheets and reports using a module. When i run the code i get the following error: "RUN TIME ERROR '2046'
The Command or acction 'OutputTo' isn't available now

It works fine until it gets to section highlighted in blue. If i remove that piece of the code then the next line i get the same error.

Here is the code that i'm currently trying to use. PLEASE HELP SOMEONE

Function BatchReports()
    Call RunBatchReports
End Function

Sub RunBatchReports()
   
DoEvents
    DoCmd.RunMacro "02_01_DOC_Inventory_Aging"
    DoCmd.OutputTo acOutputQuery, "02_04_DOC_Inventory_Aging", "Microsoft Excel (*.xls)", "\\lobot\corp\in-house-custodian\dc_output\Inventory_Aging_Data\DOC_Data" & Format(Date, "yyyymmdd") & ".xls", False, "", 0
   [COLOR="Blue"]DoCmd.OutputTo acOutputQuery, "01_03_Location_Aging", "Microsoft Excel (*.xls)", "\\lobot\corp\in-house-custodian\dc_output\Inventory_Aging_Data\CTD_Data" & Format(Date, "yyyymmdd") & ".xls", False, "", 0[/COLOR]
   DoCmd.OutputTo acReport, "Audit_CTD_Aging", "SnapshotFormat(*.snp)", "\\lobot\corp\in-house-custodian\dc_output\Management_REPORTS\Audit_CTD_Aging" & Format(Date, "yyyymmdd") & ".snp", False, "", 0
   DoCmd.OutputTo acReport, "Audit_DOC_Aging", "SnapshotFormat(*.snp)", "\\lobot\corp\in-house-custodian\dc_output\Management_REPORTS\Audit_DOC_Aging" & Format(Date, "yyyymmdd") & ".snp", False, "", 0
   DoCmd.OutputTo acReport, "Mailroom_CTD_Aging", "SnapshotFormat(*.snp)", "\\lobot\corp\in-house-custodian\dc_output\Management_REPORTS\Mailroom_CTD_Aging" & Format(Date, "yyyymmdd") & ".snp", False, "", 0
   DoCmd.OutputTo acReport, "Mailroom_DOC_Aging", "SnapshotFormat(*.snp)", "\\lobot\corp\in-house-custodian\dc_output\Management_REPORTS\Mailroom_DOC_Aging" & Format(Date, "yyyymmdd") & ".snp", False, "", 0
  DoCmd.OutputTo acReport, "Overall_CTD_Inventory_Aging", "SnapshotFormat(*.snp)", "\\lobot\corp\in-house-custodian\dc_output\Management_REPORTS\Overall_CTD_Inventory_Aging" & Format(Date, "yyyymmdd") & ".snp", False, "", 0
    DoCmd.OutputTo acReport, "Overall_DOC_Inventory_Aging", "SnapshotFormat(*.snp)", "\\lobot\corp\in-house-custodian\dc_output\Management_REPORTS\Overall_DOC_Inventory_Aging" & Format(Date, "yyyymmdd") & ".snp", False, "", 0
   DoCmd.OutputTo acReport, "QC_CTD_Aging", "SnapshotFormat(*.snp)", "\\lobot\corp\in-house-custodian\dc_output\Management_REPORTS\QC_CTD_Aging" & Format(Date, "yyyymmdd") & ".snp", False, "", 0
   DoCmd.OutputTo acReport, "QC_DOC_Aging", "SnapshotFormat(*.snp)", "\\lobot\corp\in-house-custodian\dc_output\Management_REPORTS\QC_DOC_Aging" & Format(Date, "yyyymmdd") & ".snp", False, "", 0
   DoCmd.OutputTo acReport, "Research_CTD_Aging", "SnapshotFormat(*.snp)", "\\lobot\corp\in-house-custodian\dc_output\Management_REPORTS\Research_CTD_Aging" & Format(Date, "yyyymmdd") & ".snp", False, "", 0
   DoCmd.OutputTo acReport, "Research_DOC_Aging", "SnapshotFormat(*.snp)", "\\lobot\corp\in-house-custodian\dc_output\Management_REPORTS\Research_DOC_Aging" & Format(Date, "yyyymmdd") & ".snp", False, "", 0
   DoCmd.OutputTo acReport, "Vault_CTD_Aging", "SnapshotFormat(*.snp)", "\\lobot\corp\in-house-custodian\dc_output\Management_REPORTS\Vault_CTD_Aging" & Format(Date, "yyyymmdd") & ".snp", False, "", 0
    DoCmd.OutputTo acReport, "Vault_DOC_Aging", "SnapshotFormat(*.snp)", "\\lobot\corp\in-house-custodian\dc_output\Management_REPORTS\Vault_DOC_Aging" & Format(Date, "yyyymmdd") & ".snp", False, "", 0
           
    DoCmd.Quit acSave
    
    
End Sub

 

[Smart]

Have you tried using transfer database

DoCmd.TransferDatabase acExport, "Microsoft Access", "\\spfp02\complaints\MI\KIP_Reporting\KPI.mdb", acTable, "Qry_Extract_For_Productivity", "Oteloproductivity", False

the above code will transfer from the current database the contents of the Qry_Extract_for_Productivity to the KpI.Mdb data base as a table called Oteloproductivity

 

[Nittylions]

Smart,

I need to export the query to excel so the management team can do their thing. I have the Module to run when a form is opened on start up. When i tested this i get the error but when i'm in the design view of the form and change it to form view it works fine. I don't understand what is going on. Where in the code that is used when the form is opened.

Option Compare Database


Private Sub Form_Load()
    RunBatchReports
End Sub

 

[Smart]

RE:RE:VB help needed!!!!!

This works for me transfers the contents of a table to an excel spreadsheet

DoCmd.TransferSpreadsheet acExport, 8, "Your table", "c:\aaatest.xls", True, ""

 

[Nittylions]

That worked but now the error drops to the the next line of code!

 

[boblarson]

I have a feeling that it may be due to your use of "DoEvents." Here's the help file description:

DoEvents Function


Yields execution so that the operating system can process other events.

Syntax

DoEvents( )

Remarks

The DoEvents function returns an Integer representing the number of open forms in stand-alone versions of Visual Basic, such as Visual Basic, Professional Edition. DoEvents returns zero in all other applications.

DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent.

DoEvents is most useful for simple things like allowing a user to cancel a process after it has started, for example a search for a file. For long-running processes, yielding the processor is better accomplished by using a Timer or delegating the task to an ActiveX EXE component.. In the latter case, the task can continue completely independent of your application, and the operating system takes case of multitasking and time slicing.

Caution Any time you temporarily yield the processor within an event procedure, make sure the procedure is not executed again from a different part of your code before the first call returns; this could cause unpredictable results. In addition, do not use DoEvents if other applications could possibly interact with your procedure in unforeseen ways during the time you have yielded control.

So, maybe what is happening is that your first export is not finishing before attempting the next one, etc.

 

[Nittylions]

I removed the DoEvents line and I still get the error. SHould I breakdown this code or is there another way to get this data exported in one piece of code.

 

[Nittylions]

I got it fixed. I kept the same code that i had before. Here is how i fixed it. When the database was opened i had the form open automatically at start up and and the module was setup to start on load of the form. I went tools in the menu bar and also made it so it would show the database window, so now each time this database is open it works.