Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Incrementing Output Files

Discussion in 'Information Technology' started by Gina, Jul 28, 2006.

  1. Gina

    Gina
    Expand Collapse
    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.
    --
    Gina
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Punjab wheat output to be highest in 7 years (Reuters via Yahoo! India News) Breaking News May 15, 2008
    Sikh News Punjab fears shortfall of 2.4m tons in wheat output (Dawn) Breaking News Apr 7, 2008
    Sikh News Punjab fastener makers trim output by 30% on rising cost (The Hindu) Breaking News Mar 10, 2008

  3. xRoachx

    xRoachx
    Expand Collapse
    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.

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

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



    --
    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
     
  5. xRoachx

    xRoachx
    Expand Collapse
    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.

    "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
    > > 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
     
  6. Ron2006

    Ron2006
    Expand Collapse
    Guest

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

    Gina
    Expand Collapse
    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.
    --
    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
    > > 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
     
  8. Gina

    Gina
    Expand Collapse
    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!
    --
    Gina


    "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"
    >
    >
     
  9. Ron2006

    Ron2006
    Expand Collapse
    Guest

    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

    >
     

Share This Page