+ Reply to Thread
Results 1 to 4 of 4

Using a Date when Saving a file using VBA

  1. #1
    Registered User
    Join Date
    02-04-2019
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    41

    Using a Date when Saving a file using VBA

    Good afternoon all,

    I have a spreadsheet, where I would like the code to automatically use 2 fields to create the file name for saving the file to a SharePoint site.
    The code I have is as follows:

    Please Login or Register  to view this content.
    Cell G6 is a Date field and usually would be in dd/mm/yyyy format but I am aware that ' / ' are not allowed when saving a file in Excel. I then tried to use a custom format dd-mm-yyyy in the cell but VBA is still recognising this as a Date format and fails.
    If I remove the requirement for SubDte to be used in the file name, it saves perfectly to my test environment. (e.g. FileName = Title & "-" ' & SubDte)

    Ultimately, my goal is to create the file name based on 2 fields - one being a text string and the other being the submit date of the request.
    Any help in correcting my code to achieve this is very much appreciated.

    Thank you,
    Chris

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Using a Date when Saving a file using VBA

    I can think of two ways to do this. My preferred way is to do the date formatting in the code, which is not dependent on how the date is displayed in the worksheet:

    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    02-04-2019
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    41

    Re: Using a Date when Saving a file using VBA

    Thank you very much 6StringJazzer, was not an option I was aware of but works perfectly.

    Chris

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Using a Date when Saving a file using VBA

    You're welcome, and thanks for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Saving MASTER file, automatically remove part of file name and append current date?
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-10-2014, 08:13 AM
  2. Replies: 3
    Last Post: 08-18-2010, 12:58 PM
  3. Saving a file with a date
    By antony moseley in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-22-2009, 01:54 PM
  4. Replies: 4
    Last Post: 05-26-2006, 03:10 PM
  5. [SOLVED] saving file name with the date
    By Baha in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-29-2006, 08:20 AM
  6. Saving file with date on it
    By mehta_agm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-18-2006, 02:40 AM
  7. [SOLVED] Saving file as a date
    By Jamie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-19-2005, 08:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1