Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    United Kingdom
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding current date to filename when exporting (Access 97 SR-2)

    Please help me... I need to be able to add the current date to the end of a filename when exporting it as an Excel 97 spreadsheet, I'm using this line of code:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, t.Name, "c:" & t.Name & (Date) & ".xls", -1

    Obviously I need to add in a bit where I have put (Date) to actually put in the date but, as usual, I keep getting errors - I'm new to VBA for Access ;-)
    Any help much appreciated!

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding current date to filename when exporting (Access 97 SR-2)

    The problem is probably caused by the Short Date format on your computer. If it contains slashes /, you'll get an error message because they are not allowed in file names.

    Try using a format without slashes, for instance

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, t.Name, "c:" & t.Name & Format(Date, "mmddyy") & ".xls", True

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Adding current date to filename when exporting (Access 97 SR-2)

    Close! You could probably get away with:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, t.Name, "c:" & t.Name & Date() & ".xls", -1
    but you'll get a little better control with:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, t.Name, "c:" & t.Name & Format(Date(), "mm-dd-yyyy") & ".xls", -1
    You can obviously change the date formatting as you require.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    Apr 2002
    Location
    United Kingdom
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding current date to filename when exporting (Access 97 SR-2)

    Thank you both very much, that put me on the right track and it now works perfectly!

Posting Permissions

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