Adding the current date to the end of exported queries

[ninrulz]

I have created a database that has over 70 queries and over 40k
records. I used the OutputTo action to export the queries to Excel. I
would like the functionality of adding the date (Month-YY) to each of
the Excel file names as they are exported. It seems that the OutputTo
action only allows for predetermined names or a prompt for each file. I
do not want to type the file names 70+ times and renaming each of the
..xls files afterwards is a pain. Can anyone help me to achieve this
type of output?

Query_name1 - Month-YR.xls
Query_name2- Month-YR.xls
Query_name3- Month-YR.xls
Etc.

I think this formula closely fits my needs:

"MyFileName" & "_" & Format(Date(), "yyyymmdd") * ".xls"

Unfortunately, I know nothing about Visual Basic. If the only method of
achieving my goal is using VB, I could use a few pointers.

Many thanks,
Mark

[pietlinden]

How about something like this?

Public Sub ExportQueriesToExcel()

Dim qdf As DAO.QueryDef

'---walk the querydefs collection (the queries in your db)
For Each qdf In DBEngine(0)(0).QueryDefs

'---I'm leaving out the ones I don't want to export:
'----Export only select queries.
If Left$(qdf.Name, 1) <"~" And Left$(qdf.SQL, 6) = "SELECT"
And _ InStr(1,
qdf.SQL, "INTO") = 0 Then

Debug.Print "Exporting " & qdf.Name & "..."
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, qdf.Name, "C:\test\" & qdf.Name & "-" &
Format$(Date, "mm-dd-yyyy") & ".xls"

End If

Next qdf

Set qdf = Nothing
MsgBox "Done" ' not necessary... just feedback.
End Sub

Not pretty, but it works. the testing the SQL property is just to make
sure no action queries get run by accident.

HTH,
Pieter

[Tom van Stiphout]

On 20 Jul 2006 20:33:50 -0700, "ninrulz" <ni********@hotmail.com>
wrote:

Check out DoCmd.TransferSpreadsheet. It allows you to specify a
filename.
Dim varQueries As Variant
Dim varQuery As Variant

varQueries = Array("Query_name1 - Month-YR.xls", "Query_name2-
Month-YR.xls", "Query_name3- Month-YR.xls") 'etc.
For Each varQuery In varQueries
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
varQuery, varQuery & Format$(Date, "yyyymmdd") & ".xls", True
Next

-Tom.

>I have created a database that has over 70 queries and over 40k
records. I used the OutputTo action to export the queries to Excel. I
would like the functionality of adding the date (Month-YY) to each of
the Excel file names as they are exported. It seems that the OutputTo
action only allows for predetermined names or a prompt for each file. I
do not want to type the file names 70+ times and renaming each of the
.xls files afterwards is a pain. Can anyone help me to achieve this
type of output?

Query_name1 - Month-YR.xls
Query_name2- Month-YR.xls
Query_name3- Month-YR.xls
Etc.

I think this formula closely fits my needs:

"MyFileName" & "_" & Format(Date(), "yyyymmdd") * ".xls"

Unfortunately, I know nothing about Visual Basic. If the only method of
achieving my goal is using VB, I could use a few pointers.

Many thanks,
Mark

[Tom van Stiphout]

On 20 Jul 2006 21:08:00 -0700, pi********@hotmail.com wrote:

Or rather than testing for the word "Select" at the beginning of the
SQL property (which breaks down if you have specified the data types
of your parameters), why not test the Type property:
if qdf.Type = dbQSelect or qdf.Type = dbQSetOperation then
'it's a Select or Union query

-Tom.

>How about something like this?

Public Sub ExportQueriesToExcel()

Dim qdf As DAO.QueryDef

'---walk the querydefs collection (the queries in your db)
For Each qdf In DBEngine(0)(0).QueryDefs

'---I'm leaving out the ones I don't want to export:
'----Export only select queries.
If Left$(qdf.Name, 1) <"~" And Left$(qdf.SQL, 6) = "SELECT"
And _ InStr(1,
qdf.SQL, "INTO") = 0 Then

Debug.Print "Exporting " & qdf.Name & "..."
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, qdf.Name, "C:\test\" & qdf.Name & "-" &
Format$(Date, "mm-dd-yyyy") & ".xls"

End If

Next qdf

Set qdf = Nothing
MsgBox "Done" ' not necessary... just feedback.
End Sub

Not pretty, but it works. the testing the SQL property is just to make
sure no action queries get run by accident.

HTH,
Pieter

[pietlinden]

Oh, cool! Thanks Tom!

[ninrulz]

Thanks for the quick responses,
Is there an easy way to format the date differently? I would like to
express the date as a Month-YR. Like this "File_name - July 06.xls"
-Mark

Tom van Stiphout wrote:

On 20 Jul 2006 20:33:50 -0700, "ninrulz" <ni********@hotmail.com>
wrote:

Check out DoCmd.TransferSpreadsheet. It allows you to specify a
filename.
Dim varQueries As Variant
Dim varQuery As Variant

varQueries = Array("Query_name1 - Month-YR.xls", "Query_name2-
Month-YR.xls", "Query_name3- Month-YR.xls") 'etc.
For Each varQuery In varQueries
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
varQuery, varQuery & Format$(Date, "yyyymmdd") & ".xls", True
Next

-Tom.

I have created a database that has over 70 queries and over 40k
records. I used the OutputTo action to export the queries to Excel. I
would like the functionality of adding the date (Month-YY) to each of
the Excel file names as they are exported. It seems that the OutputTo
action only allows for predetermined names or a prompt for each file. I
do not want to type the file names 70+ times and renaming each of the
.xls files afterwards is a pain. Can anyone help me to achieve this
type of output?

Query_name1 - Month-YR.xls
Query_name2- Month-YR.xls
Query_name3- Month-YR.xls
Etc.

I think this formula closely fits my needs:

"MyFileName" & "_" & Format(Date(), "yyyymmdd") * ".xls"

Unfortunately, I know nothing about Visual Basic. If the only method of
achieving my goal is using VB, I could use a few pointers.

Many thanks,
Mark

[ninrulz]

Nevermind...

I am very pleased with the result "file_name - mmyyyy.xls"

Thanks for your help

ninrulz wrote:

Thanks for the quick responses,
Is there an easy way to format the date differently? I would like to
express the date as a Month-YR. Like this "File_name - July 06.xls"
-Mark

Tom van Stiphout wrote:

On 20 Jul 2006 20:33:50 -0700, "ninrulz" <ni********@hotmail.com>
wrote:

Check out DoCmd.TransferSpreadsheet. It allows you to specify a
filename.
Dim varQueries As Variant
Dim varQuery As Variant

varQueries = Array("Query_name1 - Month-YR.xls", "Query_name2-
Month-YR.xls", "Query_name3- Month-YR.xls") 'etc.
For Each varQuery In varQueries
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
varQuery, varQuery & Format$(Date, "yyyymmdd") & ".xls", True
Next

-Tom.

>I have created a database that has over 70 queries and over 40k
>records. I used the OutputTo action to export the queries to Excel. I
>would like the functionality of adding the date (Month-YY) to each of
>the Excel file names as they are exported. It seems that the OutputTo
>action only allows for predetermined names or a prompt for each file. I
>do not want to type the file names 70+ times and renaming each of the
>.xls files afterwards is a pain. Can anyone help me to achieve this
>type of output?
>
>Query_name1 - Month-YR.xls
>Query_name2- Month-YR.xls
>Query_name3- Month-YR.xls
>Etc.
>
>I think this formula closely fits my needs:
>
>"MyFileName" & "_" & Format(Date(), "yyyymmdd") * ".xls"
>
>Unfortunately, I know nothing about Visual Basic. If the only method of
>achieving my goal is using VB, I could use a few pointers.
>
>Many thanks,
>Mark