Automate Filenames based on Parameter Specified

[ArKineX]

I have this database in Access 200 where it opens various queries and then outputs them to a file in Excel format on a datashare. The problem is that I have to update the filenames in the "Output to" section in the macro everytime I have to run it to correspond to the Fiscal Year the report is run for.

I would like to be able to Automate this to where it would prompt me for a number and it automatically insert itself within the filename with the correct fiscal week. Each of the Paths are different, so I need it to be something to where it retains the path, but just simply changes the filename.

Is there a way to do this?

Also is there a way to e-mail all of the saved files to a specific user or users after the macro completes?

Please help me with this problem. Thank you in advance!

-- ArKineX

 

[rich.barry]

Your filename in the output statement can be a string that is build up of concatenated data

eg

dim filename as string
dim week as string

week=cstr(int(day(now)/7)+1) 'not sure where you want to get your week & year from - example only

filename = InputBox("Enter File Number")
filename = "c:\" & filename & "_" & week ".xls"
DoCmd.OutputTo acOutputTable, "Employees", acFormatXLS, filename, True

 

[ArKineX]

Thank you Rich! That helps quite a bit, however I may have not come across as clear as I needed to.

I have a Macro which is setup to Open a Query, then output it to file in Excel format. Then it opens another query and also outputs it to an Excel file. This goes on for about 70 different queries. Currently, I have to go to each line manually and change the filename in each "Output To" statement within the macro each time I run the report to reflect the current Fiscal Week I am running the report for which is a pain in the neck.

My goal is to Automate this to where when the Macro Starts and it pops up a box asking me "Which Fiscal Week?". I enter the number 32 for example to reflect Fiscal Week 32. Then after I enter the number, it will automatically open each Query and then change the number within the filename of each of the "Output To" to the correct number (in this case 32) that I specified in the pop-up.

In each of the 70 or output filenames, the paths are different and some of the filenames are different, but the only thing that is going to change is the number. For example, the path and filename "C:\data\TSDReportsFW29.xls". The only thing that is going to change is the 29 in the filename.

Lastly, after the macro completes, I would like the last action to e-mail the saved Excel Spreadsheets to certain e-mail address (in which the e-mail addresses are different for each of the 70 files and may include more than one e-mail address, however the subject of the e-mail will be the same).

I know this sounds like a lot, but this is what I'm trying to accomplish. I know there has got to be a way to do this in VBA, but I ahve limited VB experience. If you do have a solution in VBA, please let me know how to incorporate it. Thank you!

 

[Chris RR]

Why don't you build yourself a table to contain some of this stuff? It could have 3 fields: the constant part of the path name (not including the week number), the query that is run to create it, and the email address to be notified.

Then, you write some code...

First, you have your code prompt you for the week number (or create a form to run this all from, and put the week number in an unbound text field on the form.)

Then I'd loop through the report table (think recordset here). Build final output name by concatenating the path name from the table, with the week number. Run the query to output the dataset, then send the email.

This way, you can easily maintain your list of queries, paths and recipients without messing with the code each time something changes.


[This message has been edited by Chris RR (edited 08-29-2001).]

 

[ArKineX]

Well, I can make the table as you specified with no problem. Writing the code, I simply do not know how to do at all.

If you don't mind and could give me some direction or even paste example code of what are trying to explain so that I incorporate it would be nice. Just saying to write code to do this and that is much easier than actually doing it =).

Many thanks in advance!

 

[Chris RR]

Didn't mean to be flip. This is not going to be lightweight code, but if you can pull it together, it will be very useful and flexible.

The following code is completely untested and highly suspect, but it might give you an idea as to the structure that I intended.

There is also a very full (and more complex) example of a similar process on Dev Ashish's Access Web, at www.mvps.org/access/queries/qry0014.htm

But here goes. I'm assuming you have a table named TESTER, and are able to pick up the week from a form.
Sub Looper()
Dim db As Database
Dim rs1 As Recordset
Dim strRunFor As String
Dim strPath As String
Dim strMail As String

' Turn the hourglass on
DoCmd.Hourglass True
' Turn off warnings
DoCmd.SetWarnings False

' we will assume you are picking up the week number from a form
strRunFor = [Forms]![fMyForm]![Report_Week]

Set db = CurrentDb
Set rs1 = db.OpenRecordset("TESTER", dbOpenSnapshot)

Do Until rs1.EOF
' Build strPath, which is where we want to put the file
' Assume StartOfPath is a field in the TESTER table
strPath = StartOfPath & strRunFor & ".rtf"

' Assume Qname is the query name in the TESTER table
DoCmd.OutputTo acOutputQuery, QName, "RichTextFormat(*.rtf)", strPath, False

' Assume MailRecip is the person who gets the email, from TESTER
' Now send the email...we use a sub called Sendmail, you need to change this
strMail = MailRecip
Call SendMail(strMail, "File is Ready", "Good Morning! YourFileIsReady")
rs1.MoveNext
Loop

Set rs1 = Nothing
Set db = Nothing

' Turn the hourglass off
DoCmd.Hourglass False
' Turn warnings back on
DoCmd.SetWarnings True
End Sub

 

[ArKineX]

Thank you very much Chris! Your skeleton VB code is almost perfect! There is a few questions however.

1) Is there a way to say the file in Excel format and then ZIP it before it e-mailed out?

2) The E-Mail Subject is not going to change except for the Fiscal Week number for example, "Here is the TSD Report for Fiscal Week 32". Is there a way to make the this number change just like the number in the filename?

Thank you in advance for the excellent Access help!

-- ArKineX

 

[Chris RR]

Hi,
1. There are other formats available from the Outputto. Remember to change the file extension to match:
acFormatASP
acFormatDAP
acFormatHTML
acFormatIIS
acFormatRTF
acFormatSNP
acFormatTXT
acFormatXLS

Sorry, never tried zipping from Access, maybe someone else has.

Are you sure that you need to send the file? With internal users, often all I need to do is to store the file on a network drive, then send the user the name. (Have your network guys give the users read-only access to the shared folder, giving you write & update...then the user can pick it up, but not clobber it for others...)

2. Very similar to changing the file name:

'Dim some string to hold the email subject
dim strSubject as string

'then, after you set up strRunFor:
strSubject = "Here is the TSD Report for Fiscal Week " & strRunFor

'In my example, the send is like this, but you will probably use a Send command or something
Call SendMail(strMail, strSubject, "Good Morning! YourFileIsReady")


[This message has been edited by Chris RR (edited 08-31-2001).]