loop through queries and export to Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Patrick - 07 Dec 2005 12:30 GMT

Hi all,

Each week, I need to take about 30 queries from access, and export them
to Excel. I then need to format the excel spreadsheets and email them
to various people.

I would like to automate this process so that I can just click a button
and it does everything for me. I have automated the formatting of the
spreadsheets fine, from within Excel, and I know I will be able to call
that procedure from access, the problem is exporting all the queries in
the first place.

Could someone explain how I loop through the queries in the database
and export any that start with "Lookahead Report" ?

Much appreciated.

Patrick

Reply to this Message

JAA149 - 07 Dec 2005 12:46 GMT

Dear Patrick,

How about you use MS Query. Select & copy the SQL code in Access, Go to MS
Query & save it. Now right click on the query & select with "Open in Excel".
The result/data set wil be exported to Excel. Now there is a link between the
Excel file, the Query file & the MS Access Databse. Any changes in the
databse will go to the Quey and from there go to the Excel file. You can
aloso retain you seeting in Excel.

Regards

=============

> Hi all,
>
[quoted text clipped - 14 lines]
>
> Patrick

Reply to this Message

Patrick - 07 Dec 2005 13:20 GMT

hi, thanks for the quick reply.

I have just had a look at your suggestion, and apart from the fact that
I am not too clued up on MS Query, there are 4 people at the moment
that need to be able to run this, will the queries need to be set up on
all computers?

The database is held on a share, which is why I would like to get it to
be able to do everything from within the database. That way, everyone
has access to it, and everyone is on a level playing field...

Is there also a way to loop through the queries and export, or is the
MS Query thing the only route?

Thanks

Patrick

Reply to this Message

JAA149 - 07 Dec 2005 14:21 GMT

Dear Patrick,

First of all, you do not need to set up queries on all computers. Lets take
an example.

1 - Say your database is located at
c:\Patrick\My Database.mdb

2 - Go to MS Query & start it. If you can not find it, seach for MS Query in
"C:\Program Files\Microsoft Office\OFFICE11\MSQRY32.EXE". If not start Excel,
go to Data, Import Extenal Data, New Database Query & it will launch MS Query.

3 - Select New Query, From The Databse Tab, Select MS Access*. Find you
file. The Query Wizard - Choose Columns will come & you can see the tables.
Select the table with their fileds.

4 - Now Join the table & define relationships between them just like MS
Access Query Wizard. You can also click SQL to edit or change the SQL
commands.

5 - Once done you will see the desired result/data set.

6 - Save the query *.dqy any where. Maybe where the databse is.

7 - Close everything. Go to the dqy file, right click & chose open in Excel.
The data will be imported in Excel

8 - Select any cell in the data. click, data & data range properties. YOU
WILL BE AMAZED AT THE OPRIONS THAT WILL Proivde. Your problems of re-doing
the format seeting will be completely solved here.

You have made only one query, with one Excel file. Thats all that needs to
be done. Whenever the data in the Access chnages, the query will change & so
will the Excel file. Other can have their own excel files with the same
procedure as above from the same query. You change the query any time or make
new ones.

MS Query is a smal SQL tool but I found it very powerful. Learn it. It will
take maybe 3 to 4 hours.

Regards

Reply to this Message

Klatuu - 07 Dec 2005 14:46 GMT

I am not that crazy about MS Query. I have had too many problems,
specifically with the location of the query. Here is a way in Access to
accomplish this:

Dim dbf As Database
Dim qdfs As QueryDefs
Dim qdf add QueryDef

Set dbf = CurrentDb
Set qdfs = dbf.QueryDefs
For each qdf in qdfs
If Instr(qdf.Name, "Lookahead Report") > 0 Then
DoCmd.TransferSpreadsheet(acExport, , qdf.Name,"C:\SomeFolder\"
& _
qdf.Name & ".xls", True
End If
Next qdf
Set qdf = Nothing
Set qdfs = Nothing
Set bdf = Nothing

> Hi all,
>
[quoted text clipped - 14 lines]
>
> Patrick

Reply to this Message

Patrick - 07 Dec 2005 15:18 GMT

hi, I realised what i was doing wrong with the code i had. i forgot to
set the object.

Thanks for your replies, in the end i used the following 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

Reply to this Message

fredg - 07 Dec 2005 17:33 GMT

> Hi all,
>
[quoted text clipped - 14 lines]
>
> Patrick

As long as all the queries you wish to export start with "Lookahead
Report", you can cycle through the query collection and use
TransferSpreadsheet to export to Excel.

Public Sub ExportQueries()

Dim dbs As Database, qdf As QueryDef
Set dbs = CurrentDb
For Each qdf In dbs.QueryDefs
If Left(qdf.Name, 16) = "Lookahead Report" Then
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, qdf.Name, "c:\YourPath\SpreadsheetName.xls",
True
End If
Next qdf
Set dbs = Nothing

End Sub

Each query will be placed on it's own worksheet. It's then up to you
to format the spreadsheet however you wish.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Patrick - 08 Dec 2005 09:35 GMT

thanks very much, it all works a treat now. :o) Really appreciate the
help I have received.

Patrick

Reply to this Message