Results 1 to 9 of 9

Export file with date

  1. #1
    adnancanada is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    90

    Export file with date

    Hi I am using this VBA code it is working fine. I have 3 different queries in MS Access and I am exporting xlsx file in my C drive as 1 file contains 3 tabs but I need date end of the file.

    Private Sub Command3_Click()

    Dim xl As Object
    DoCmd.TransferSpreadsheet acExport, , "Daily Delivered", "C:\Input\Western Perishable File.xlsx", True
    DoCmd.TransferSpreadsheet acExport, , "Dock Volume", "C:\Input\Western Perishable File.xlsx", True
    DoCmd.TransferSpreadsheet acExport, , "Dock Volume", "C:\Input\Western Perishable File.xlsx", True
    Set xl = CreateObject("Excel.application")
    xl.workbooks.Open ("C:\Input\Western Perishable File.xlsx")
    xl.Visible = True


    Set xl = Nothing


    End Sub
    It create files Western Perishable File.xlsx in my c drive contains all above three tabs.

    But I want to add date at the end of this file, it should be like this Western Perishable File-Nov-17-2017.xlsx

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    11,827
    Alter your code eg

    "C:\Input\Western Perishable File.xlsx"
    to
    "C:\Input\Western Perishable File-" & Format(Date, "MMM-DD-YYYY") & ".xlsx"
    Last edited by orange; 11-17-2017 at 01:15 PM. Reason: spelling

  3. #3
    adnancanada is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    90
    Quote Originally Posted by orange View Post
    Alter your code eg

    "C:\Input\Western Perishable File.xlsx"
    to
    "C:\Input\Western Perishable File-" & Format(Date, "MMM-DD-YYYY") & ".xlsx"
    You are awesome thank you very much.

  4. #4
    adnancanada is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    90
    Quote Originally Posted by orange View Post
    Alter your code eg

    "C:\Input\Western Perishable File.xlsx"
    to
    "C:\Input\Western Perishable File-" & Format(Date, "MMM-DD-YYYY") & ".xlsx"
    Hi Orange,
    I have another situation if you could solve this also. I tried your same logic in Sendobject but does not work.


    DoCmd.SendObject acSendQuery, "VdrDeliveredtoDock" & Format(Date, "MMM-DD-YYYY") & ".xlsx", acFormatXLSX, "abc@gmail.com, xyz@yahoo.com, "Dry Vendor delivered to DOCK" & Format(Date, "long date"), "Good Morning," & vbNewLine & "Attached please find the subject report." & vbNewLine & "Thanks,"

    is possible ?

    Error message " The Microsoft Access database engine could not find the object "VendorDeliveredtoDock0612/2017.xls'. Make sure object exists

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,678
    The object it is referring to is the name of your query, which is the second parameter.

  6. #6
    adnancanada is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    90
    Quote Originally Posted by aytee111 View Post
    The object it is referring to is the name of your query, which is the second parameter.
    is there any solution for that? Can I add date to my object or not ?

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    11,827
    I think the query is still called "VdrDeliveredtoDock" -so remove the stuff you have in red.
    Try it and see what you get.

  8. #8
    adnancanada is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    90
    Quote Originally Posted by orange View Post
    I think the query is still called "VdrDeliveredtoDock" -so remove the stuff you have in red.
    Try it and see what you get.
    I removed it. it works fines as attach VdrDeliveredtoDock.xlsx in my outlook but I want to add date with this excel file. Is it possible ?

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,678
    Doesn't seem that it is possible. You would need to create the file first using TransferSpreadsheet and then send an email with the file as an attachment. You can see how to do that in the last example provided here:
    https://msdn.microsoft.com/en-us/vba...-method-access

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-03-2017, 08:25 PM
  2. Export File Name with Today's Date
    By sombiatz in forum Macros
    Replies: 2
    Last Post: 07-30-2014, 07:43 AM
  3. Replies: 13
    Last Post: 12-12-2013, 06:22 PM
  4. Replies: 3
    Last Post: 06-18-2013, 12:14 PM
  5. Date/time export to CSV file
    By kaptin in forum Import/Export Data
    Replies: 3
    Last Post: 05-08-2011, 05:57 AM

Posting Permissions

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