Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    sam.eade is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    29

    Lightbulb Exporting Query into excel with Todays Date in the file name

    Hi,

    I've been trying to export a query using the event builder with the exportwithformatting function. I am trying to include todays date in file name when the macro is run.

    So far I have looked at a few forums and has tried these two file names but neither has worked.

    "c:\Access Data\Expenses" & Date() & ".xlsx"

    "c:\Access Data\Expenses" & (Format(Date(),"yymmdd") & ".xlsx"

    This is to go in the Output File field.

    I did find a piece of code that worked but it didn't include any formatting.

    Dim mnth As String
    Dim dy As String
    Dim yr As String

    Dim file_nme As String



    mnth = Month(Now())
    dy = Day(Now())
    yr = Year(Now())



    file_nme = "c:/" + mnth + "-" + dy + "-" + yr + ".xls"


    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Your Table Name", [file_nme]


    Can anyone help me find a solution to this?

    Thank you

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    FYI: Though the "+" will sort of work as a concatenation character it can have strange results on occasion. The string concatenation character in VBA is the "&" character and will work as you would expect all of the time.

  3. #3
    sam.eade is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    29
    Quote Originally Posted by RuralGuy View Post
    FYI: Though the "+" will sort of work as a concatenation character it can have strange results on occasion. The string concatenation character in VBA is the "&" character and will work as you would expect all of the time.
    Thanks for the info! Again it was someone else's code that I found online, do you know how to modify it to export with formatting?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    This *should* work:
    "c:\Access Data\Expenses" & (Format(Date(),"yymmdd") & ".xlsx"
    ...but you have an extra "(" in front of Format...
    "c:\Access Data\Expenses" & Format(Date(),"yymmdd") & ".xlsx"



  5. #5
    sam.eade is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    29
    Quote Originally Posted by RuralGuy View Post
    This *should* work:
    "c:\Access Data\Expenses" & (Format(Date(),"yymmdd") & ".xlsx"
    ...but you have an extra "(" in front of Format...
    "c:\Access Data\Expenses" & Format(Date(),"yymmdd") & ".xlsx"


    Didn't work just keeps saying "Can't save the output data to the file you've selected" and its argumentn is "file1, Rob Client Updates Today, ExcelWorkbook(*.xlsx), "c:\Access Data\Expenses" & Format(Date(),"yymmdd") & ".xlsx", Yes, , , 0"

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Are you trying to put your file in the "Expenses" folder in the "Access Data" folder? If so, you need another "\"
    "c:\Access Data\Expenses\" & Format(Date(),"yymmdd") & ".xlsx"

  7. #7
    sam.eade is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    29
    No that was going to be the name of the file. Even so, it would create a file called expenses next to the expenses folder surely?? confused :s

    Is there another way to go about it apart from using the event builder? I can manage to export it using that code but it has no formatting, and looks a bit dull! If you can format that then it would be another solution.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'll have to be honest and let you know I don't use Macros, only code. So the New Filename was going to be:
    "c:\Access Data\ExpensesYYMMDD.xlsx", right?

  9. #9
    sam.eade is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    29
    Quote Originally Posted by RuralGuy View Post
    I'll have to be honest and let you know I don't use Macros, only code. So the New Filename was going to be:
    "c:\Access Data\ExpensesYYMMDD.xlsx", right?
    Yes. any luck?

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Not yet. You are using a Macro, correct? Or are you using code?

  11. #11
    sam.eade is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    29
    I was using the macro "ExportwithFormatting" because it is very simple. Just stumbling over this date situation.

    I tried using some code from a forum that I ripped and posted above ^^ which inserted the name but it wasn't formatted at all and looked dull.

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Any chance you could post a small db that demonstrates the issue after removing any sensitive data. It will give me something to play with.

  13. #13
    sam.eade is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    29
    Quote Originally Posted by RuralGuy View Post
    Any chance you could post a small db that demonstrates the issue after removing any sensitive data. It will give me something to play with.
    Sure thing, I can do it when I get to my computer tmoro, or I can quickly create a small database on my laptop with a similar problem. I will post it up asap.

  14. #14
    sam.eade is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    29
    Quote Originally Posted by RuralGuy View Post
    Any chance you could post a small db that demonstrates the issue after removing any sensitive data. It will give me something to play with.
    Here I have made a simplistic database with a table called "Saleslog" it is a table where each salesman will log their sale under their name.

    A manager will go on the system at the end of the day and will export a query using the buttons on the menu page. The criteria of this query is to select each seller individually by name.

    The buttons on the left hand side of the menu both have macros attached that will export the data to my local pathname (on my personal laptop). They currrently do not make any attempt to include the date in their names, but if you click the button twice, then the file will attempt to overwrite the previous file because it has the same name.

    The manager wishes to export this data daily, which is why only a date needs to be put into the filename but it is still important to keep records of daily sales, which is why the filename must be unique. Also, note the nicely formatted spreadsheet that is produced (that my boss likes very much )

    I think if you can solve this problem then it will solve mine in turn. P.S. feel free to disable macros if you want, I only put them in there to try and exercise my point. Also if you have to solve the situation using code then feel free, I would just need to be instructed where to place it as my VBA is fairly limited.

    Thanks RuralGuy
    Attached Files Attached Files

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Thanks. I'll stay tuned.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Exporting a query to Excel File
    By crowegreg in forum Import/Export Data
    Replies: 2
    Last Post: 08-08-2013, 05:25 PM
  2. Change Name of File with todays Date
    By tcheck in forum Access
    Replies: 1
    Last Post: 12-18-2012, 12:32 PM
  3. Exporting to formatted Excel file
    By Xerin in forum Access
    Replies: 4
    Last Post: 10-21-2011, 03:33 PM
  4. Exporting query to Excel file with password?
    By jvera524 in forum Access
    Replies: 0
    Last Post: 12-06-2010, 11:24 AM
  5. Exporting to Excel Date Stamped File
    By BED in forum Import/Export Data
    Replies: 1
    Last Post: 08-07-2010, 05:53 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums