Incrementing Output Files

[Guest]

Everyday I import a table into Access (an exported query from People Soft).
The data is manipulated and then a report is exported to an Excel file. I
have a Macro performing this with a push of a button on a form that is on the
Start Up menu. Because it is automated, the name of the exported report is
the same each day and is therefore overwritting the prior days report.

My question: Is there a way to increment the name of the report to export.
I know that People Soft and some others have this process of incrementing the
name. I hope I have given enough details.

Thanks.

 

[Guest]

Hi Gina -- If the report is run once each day, you can concatenate the name
and the date to form the name of the file. This is probably the easiest
option. If you need more specific help, post your code you use to export
your report.

 

[Guest]

xRoachx,

Thank you for your reponse.

Although, I have used some VB Scripting, I am not a programmer (learning a
little more each day) and in this particular case - I have not utilized any
VB scripting.

This database's sole purpose is to import a single table overwritting the
prior one, from that table a report is generated and exported using the
following path.

F:\Collections\Collectors\Payment History Reports\Payment History.xls

I guess I was thinking that something could be written within the path (some
coding) that would tell it to increment. I suppose I'm way off, however, if
you feel that VB scripting will accomplish this... Any guidance in the right
direction is most appreciated.

 

[Guest]

I misread your post, sorry about. I assumed you were using code.

How are you exporting the report? If you are going to File-->Export, then
you can simply edit the file name.

If you're exporting through the query, do not except the overwrite when
prompted and rename the file. If you are exporting using a macro, you will
need to either rewrite this function in VBA or use one of the previously
mentioned methods.

 

[Ron2006]

Where the file name is right now try the following:

"F:\Collections\Collectors\Payment History Reports\Payment History " &
year(date()) & format(month(date()),"00") & format(day(date()),"00") &
".xls"

 

[Guest]

xRoachx,

Thanks for the quick response.

During the Macro run, it will prompt you to overwrite, giving you the
opportunity to give it a different name. Because many users will be using
this, they will not know what another user has named it (without going to the
actual folder and looking at each file). So it was my hope to automate the
incrementing of the name and thereby leaving the end user out of the
equation. Do you happen to have an example of a VB script that I could adapt
to my DB.

 

[Guest]

Ron,

Thanks for your assistance.

I tried your format, however, it gave an error msg. 'path is too long'. So
I shorten the path down to the first folder 'Collections'. Although the path
is no longer too long, it still was unable to perform the export.
Unfortunately, its the 'format' part it doesn't seem to recognize.

I am putting this in a Macro - Output to - under Output File (file path).

However, if there is a better way, I'm open for suggestions.

Thanks!

 

[Ron2006]

The format part was an attempt at hving the date always format as

asdfbb 20060530.xls

If you can live with

asdfbb 2006530.xls you can take the format out.

Or on the form where you have the button
1)create a txtbox - named myfilename and make it visible=false if you
want.
2) in the click event where you call your macro, place
me.myfilename = "F:\Collections\Collectors\Payment History
Reports\Payment History " & year(date()) & format(month(date()),"00") &
format(day(date()),"00") & ".xls"
3) in the macro, in place of the file name place
Forms![formnamewhere button is]![myfilename]

(watch for wraping in this posting.)

Try that
Ron