Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Output using VBA not consistent (2000 SR1)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Output using VBA not consistent (2000 SR1)

    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?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Output using VBA not consistent (2000 SR1)

    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?
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output using VBA not consistent (2000 SR1)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Output using VBA not consistent (2000 SR1)

    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.)

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output using VBA not consistent (2000 SR1)

    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?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Output using VBA not consistent (2000 SR1)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •