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
6  2629 
How about something like this?
Public Sub ExportQueriesTo Excel()
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.TransferS preadsheet acExport,
acSpreadsheetTy peExcel9, 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
On 20 Jul 2006 20:33:50 -0700, "ninrulz" <ni********@hot mail.com>
wrote:
Check out DoCmd.TransferS preadsheet. It allows you to specify a
filename.
Dim varQueries As Variant
Dim varQuery As Variant
varQueries = Array("Query_na me1 - Month-YR.xls", "Query_name 2-
Month-YR.xls", "Query_name 3- Month-YR.xls") 'etc.
For Each varQuery In varQueries
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel9,
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"
Unfortunatel y, 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
On 20 Jul 2006 21:08:00 -0700, pi********@hotm ail.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 ExportQueriesTo Excel()
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.TransferS preadsheet acExport,
acSpreadsheetT ypeExcel9, 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
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********@hot mail.com>
wrote:
Check out DoCmd.TransferS preadsheet. It allows you to specify a
filename.
Dim varQueries As Variant
Dim varQuery As Variant
varQueries = Array("Query_na me1 - Month-YR.xls", "Query_name 2-
Month-YR.xls", "Query_name 3- Month-YR.xls") 'etc.
For Each varQuery In varQueries
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel9,
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
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********@hot mail.com>
wrote:
Check out DoCmd.TransferS preadsheet. It allows you to specify a
filename.
Dim varQueries As Variant
Dim varQuery As Variant
varQueries = Array("Query_na me1 - Month-YR.xls", "Query_name 2-
Month-YR.xls", "Query_name 3- Month-YR.xls") 'etc.
For Each varQuery In varQueries
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel9,
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"
>
>Unfortunatel y, 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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: ToysNTreasures |
last post by:
Hi,
I'm working on a class project in which I have to create a simple hotel
reservation database using Access. I've created a couple of tables and
queries that allow a user to determine room availability on a queried
range of dates.
What I now want to add to that query is a statement that will not
accept an input date prior to the current date of greater than a year
from the current date. Can someone please help me with this?
...
|
by: James P. |
last post by:
Hello there,
In my asp.net page using VB, I have a date text field in mm/dd/yyyy
format. When a date is entered, I'd like to validate it to make sure
the date is greater than or equal to the current date. If not, I'd
like to display the error message to ValidationSummary.
It seems to make sense to me to use CompareValidator but the problem
is put the current date into CompareValidator. So, I created a hidden
text field in my aspx. ...
|
by: vijayk |
last post by:
Hi all,
I have a field which has data as YYYYMMDD, and I have to find the age
of the person by substracting it from current date. can you please
please advice...
thanks
|
by: markryde |
last post by:
Hello,
I am trying to add the current date to a file name in python script
like thus:
import os
import sys
import rpm
import time
import datetime
|
by: Shawger Lager |
last post by:
Hi, I am new to DB2 (and SQL) and I am having some problems with the current date. I am trying to make sure the date in the table review is not past the current day using a constriant. I have tried creating the table this way.
CREATE TABLE Reviews(
isbn ISBN NOT NULL,
reviewer CHAR30,
rating INT,
date DATE,
comment VARCHAR(500),
FORIEGN KEY(isbn) references...
| | |
by: shiznaw |
last post by:
Private Function rprtdate()
Dim rprtdates As Date
Dim 1mnth As Integer
1mnth= Month(Date) + Month(DateSerial(Year(Date), Month(Date) + 1, Day(Date)))
Select Case Frame35.Value
'the following if end user choses one month from the day chosen for reporting period
Case Is = 3
rprtdates = Format(Calendar1.Value, "mm/dd/yy", vbSunday)
|
by: bruce24444 |
last post by:
I have a database which assigns warranty claims to people with a main screen showing number of files assigned to each person. The number assigned shows day, week, month and year numbers so they can be evenly distributed.
The problem I'm having is getting the query to return a number of files for the current date. Week, month and year appear to work fine.
Below are the SQL's for both day and week.
Any suggestions as to what's wrong...
|
by: nagarwal |
last post by:
Hi All,
I am having prblm in adding the no. of days passed to the current date, in a class fuction.
This is an immediate requirment plz help..
I am using java.util.Date object for the current date..
Thanks in advance:)
|
by: debasisdas |
last post by:
This article consistes of some of the frequently asked date related queries.
Hope the users find it useful.
==========================
1.Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month
------------------------------------
Select next_day(trunc(sysdate,'mm')-1,'SUNDAY') First_Sunday, next_day(last_day(trunc(sysdate,'mm'))-7,'SUNDAY') Last_Sunday from dual
For other days please modify the name of...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
