Loop Through Queries

ozPATT · December 7th, 2005, 10:07 AM

Hi All,

I am trying to work out how to export all queries in a database that start with "Lookahead Report", to Excel, with the click of a button.

I have worked out how to export them using vba, but I don't yet know how to loop through all the queries to see if they start with "Lookahead Report".

Would someone be able to help me out with this?

Thanks

Patrick

Visit my site: http://www.drybonesuk.com

echovue · December 7th, 2005, 10:58 AM

Hi ozPatt,

There is a system table that holds the names of all of your queries, table etc. I would go to tools, options and then have it show the System tables, then just do a query that pulls all objects of a certain type (Whatever the queries are listed as) and where the name begins with your specific text.

Let me know if you need more information than this,

Mike

Mike
EchoVue.com

ozPATT · December 7th, 2005, 11:16 AM

HI, thanks very much for your reply. While I was just copying some code for you, I realised what I had done wrong. I forgot to set one of the objects. The code I have used is as follows:

Code:

'EXPORT LOOKAHEAD REPORTS TO EXCEL
Public Sub ExportLookaheads()
    'locals
    Dim obj As AccessObject
    Dim dbs As Object
    'set object
    Set dbs = Application.CurrentData
    'loop through and export
    For Each obj In dbs.AllQueries
        If Left(obj.Name, 16) = "Lookahead Report" Then
            DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:=obj.Name, OutputFormat:=acFormatXLS, OutputFile:=conRootDir & "Lookahead Reports\" & obj.Name & ".xls"
        End If
    Next obj
End Sub

thanks very much for your response, without it I wouldn't have been replying and may not have found the problem!

Thanks again

Patrick

Visit my site: http://www.drybonesuk.com

echovue · December 7th, 2005, 11:19 AM

No Worries! Looks like I learnt something new as well.

Thanks

Mike

Mike
EchoVue.com