Incrementing Output Files

#1 (**permalink**) 28-Jul-2006, 08:14 AM

Everyday I import a table into Access (an exported query from People Soft).
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10602-incrementing-output-files.html
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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10602
I know that People Soft and some others have this process of incrementing the
name. I hope I have given enough details.

Thanks.
--
Gina

#2 (**permalink**) 28-Jul-2006, 08:14 AM

Hi Gina -- If the report is run once each day, you can concatenate the name
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10602
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.

"Gina" wrote:

> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10602
> 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.
> --
> Gina

#3 (**permalink**) 28-Jul-2006, 08:14 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10602
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.

--
Gina

"xRoachx" wrote:

> 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.
>
> "Gina" wrote:
>
> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10602
> > 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.
> > --
> > Gina

#4 (**permalink**) 28-Jul-2006, 08:14 AM

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.

"Gina" wrote:

> 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.
>
>
>
> --
> Gina
>
>
> "xRoachx" wrote:
>
> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10602
> > your report.
> >
> > "Gina" wrote:
> >
> > > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10602
> > > 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.
> > > --
> > > Gina

#5 (**permalink**) 28-Jul-2006, 08:14 AM

Where the file name is right now try the following:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10602

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

#6 (**permalink**) 28-Jul-2006, 08:14 AM

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.
--
Gina

"xRoachx" wrote:

> 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.
>
> "Gina" wrote:
>
> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10602
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10602
> > 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.
> >
> >
> >
> > --
> > Gina
> >
> >
> > "xRoachx" wrote:
> >
> > > 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.
> > >
> > > "Gina" wrote:
> > >
> > > > 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.
> > > > --
> > > > Gina

#7 (**permalink**) 28-Jul-2006, 08:14 AM

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!
--
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10602
Gina

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10602
"Ron2006" wrote:

> 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"
>
>

#8 (**permalink**) 28-Jul-2006, 08:15 AM

The format part was an attempt at hving the date always format as
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10602

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"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10602
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

>