Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Unanswered: Autonaming an Outputted Spreadsheet via Access Macro

    Hello all,

    I have a macro that runs and exports data to a spreadsheet on the users machine, is there any way to autoname the spreadsheet so that it corresponds to the current date. Currently the file name is Product_Inventory.xls, I would like to make it Product_Inventory 01-30-08 - where the date is the date that the access macro exported the data into the spreadsheet. Any help would be GREATLY appreciated.

    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    "Product_Inventory" & Format(Date(),"mm-dd-yy") & ".xls"

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Not sure it can be done via MACROs though. Could be... I don't use Macros for much.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Go to your macro, right click it and chose "save as module".
    Then you can edit the code in the VBA editor and do what you require
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can I make a suggestion that you make your file name "Product_Inventory20080130" instead of "Product_Inventory 01-30-08"

    the reason the files will then be easy to sort in date order irrespective of when changes are made.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed with healdem on that.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    May I suggest having a look at this - it does what you ask and far more - it will be a steep learning curve from Macro's though:
    http://www.dbforums.com/showpost.php...81&postcount=4

    Also, the specifics of naming a file on the filesystem (if you continue with what you have now) will involve either shelling a DOS command to the OS or using the filesystem object. The way you rename a file is by using the move method as I recall. One of VBA's file methods might be able to do it too but I can't remember.....

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No, rename a file with the Name command... believe it or not.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by StarTrekker
    No, rename a file with the Name command... believe it or not.
    Coo - ta. I assumed that was a read only property. You can effectively rename a file using move but name is much cleaner.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I didn't believe the command was right either... with a name like Name.

    Name, the rename command, works in all versions of Access too
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •