Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2004
    Location
    Miami
    Posts
    10

    Question Unanswered: Export Table to Excel

    I want to export a table from Microsoft Access to Microsoft Excel, but I want to use current date as the file name. I want it to look like this mm_dd_yy.
    I am using the "output to" macro right now, but I have to go and change the name of the file manually.
    Anyhelp will be appreciated

    Byrke

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Use the format function

    Format (date, "dd-mm-yyyy")
    Last edited by hammbakka; 09-23-04 at 02:43.

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Or use the DatePart function to get the parts of your date....

  4. #4
    Join Date
    Aug 2004
    Location
    Miami
    Posts
    10

    Cool

    Thank you for replying to my post. How should I use the format function?

  5. #5
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I'm not familiar with macros but you might try to use:
    format(date,"mm_dd_yy") & ".xls"

    as file name in outputto macro I think.
    Last edited by ghozy; 09-23-04 at 17:28. Reason: typo
    ghozy.

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Yes that is right, just let your file name called
    Format(date,"mm_dd_yy") & ".xls"
    i.e
    Dim exportFileName AS Strin

    exportFileName =Format(date,"mm_dd_yy") & ".xls"

  7. #7
    Join Date
    Aug 2004
    Location
    Miami
    Posts
    10
    I've tried to put (date,"mm_dd_yy") & ".xls" in the output format, it did not work. How do I create a module with this function? I just started using modules, details will be really appreciated.

    Byrke

  8. #8
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    Quote Originally Posted by byrke
    I've tried to put (date,"mm_dd_yy") & ".xls" in the output format, it did not work. How do I create a module with this function? I just started using modules, details will be really appreciated.

    Byrke
    create a button without using wizard. add the following code to button's on click event. don't forget to change tablename.

    Code:
    DoCmd.OutputTo acOutputTable, "tablenamehere", acFormatXLS, Format(Date, "mm_dd_yy") & ".xls"
    ghozy.

  9. #9
    Join Date
    Aug 2004
    Location
    Miami
    Posts
    10

    Exclamation

    Quote Originally Posted by ghozy
    create a button without using wizard. add the following code to button's on click event. don't forget to change tablename.

    Code:
    DoCmd.OutputTo acOutputTable, "tablenamehere", acFormatXLS, Format(Date, "mm_dd_yy") & ".xls"
    Now it's asking me for the macro. I've created one, it is still giving me an error. I forgot to mention that I am exporting a select query.

    Byrke

  10. #10
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    then you should change acOutoutTable parameter to acOutputQuery, and you should type queryname instead "tablenamehere". other than that this should work. one more thing, this code is not a macro. it is VBA code. therefore you can't use it in a macro event. you should create it with code builder, not macro builder. no macros involved with this code.
    ghozy.

  11. #11
    Join Date
    Aug 2004
    Location
    Miami
    Posts
    10
    Thanks a lot, the code works perfectly. Now let say I want the file to be exported in a specific folder, how do I add that to the code?

    Byrke

  12. #12
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    change Format(Date, "mm_dd_yy") & ".xls" section to
    "C:\yourfolder\" & Format(Date, "mm_dd_yy") & ".xls"

    I might not able to answer anymore questions for awhile because hurricane coming this way. so I hope this solves your problem.
    ghozy.

  13. #13
    Join Date
    Aug 2004
    Location
    Miami
    Posts
    10

    Thumbs up

    Thanks a lot. Everything running smooth. I deeply appreciate it.

Posting Permissions

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