Output using VBA not consistent (2000 SR1)

Topic

New Reply

Why does the code below work in some databases and not others? Even from week to week in the same database?

Option Compare Database
Option Explicit

Sub OUTPT()
Dim qdftemp As QueryDef
Dim a As String
For Each qdftemp In CurrentDb.QueryDefs
a = qdftemp.Name
DoCmd.OutputTo acQuery, a, “MicrosoftExcel(*.xls)”, _
“C:My DocumentsTESTSTORES” & qdftemp.Name & “.xls”, False, “”
Debug.Print qdftemp.Name
Next

End Sub

Reply | Quote

Replies

How does it fail? Do you get an error message, and if so, what does it say?

Does the code export a few queries and then fail, or does it cause an error immediately?

Can you discover a pattern in when or where the code fails?

Reply | Quote

Compile error:
User-defined type not defined.

It fails everytime before it runs anything …highlighting the dim statement.

The frustrating part is that I copied this from one database I’m using (where it works every week). Plus when I ran this two weeks ago in this one & it worked.

Reply | Quote

In the Visual Basic Editor, select Tools | References.
Locate Microsoft DAO 3.6 Object Library, tick the corresponding check box and click OK.
You will have to do this for every database containing this code. In Access 2000 and up, there is no default reference to the DAO library (there was in Access 97 and before.)

Reply | Quote

THANK YOU!!! Another question… I had this working 2 weeks ago by adding this reference. Is there something I need to do to the module or the database prior to closing to it so that it’s preserved?

Reply | Quote

No, a reference, once set, should stay set until you clear it. I can’t explain why the reference should have become unset.

Reply | Quote

No, a reference, once set, should stay set until you clear it. I can’t explain why the reference should have become unset.

Reply | Quote

THANK YOU!!! Another question… I had this working 2 weeks ago by adding this reference. Is there something I need to do to the module or the database prior to closing to it so that it’s preserved?

Reply | Quote

In the Visual Basic Editor, select Tools | References.
Locate Microsoft DAO 3.6 Object Library, tick the corresponding check box and click OK.
You will have to do this for every database containing this code. In Access 2000 and up, there is no default reference to the DAO library (there was in Access 97 and before.)

Reply | Quote

Compile error:
User-defined type not defined.

It fails everytime before it runs anything …highlighting the dim statement.

The frustrating part is that I copied this from one database I’m using (where it works every week). Plus when I ran this two weeks ago in this one & it worked.

Reply | Quote

How does it fail? Do you get an error message, and if so, what does it say?

Does the code export a few queries and then fail, or does it cause an error immediately?

Can you discover a pattern in when or where the code fails?

Reply | Quote

You can output only a non-action query to Excel. Since Append, Update and MakeTable queries do not return rows, there is nothing there to export. Could that be your problem?

Reply | Quote

You can output only a non-action query to Excel. Since Append, Update and MakeTable queries do not return rows, there is nothing there to export. Could that be your problem?

Reply | Quote