+ Reply to Thread
Results 1 to 7 of 7

Set/Write Folder Times using VBA

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Question Set/Write Folder Times using VBA

    I am trying to set the Date Created property on folders using VBA.

    Chip Pearson had the most promising search result ( http://www.cpearson.com/excel/FileTimes.htm ) however after testing, it appears to be for files only (not folders)

    I am now using FSO to retrieve the DateCreated value ( https://msdn.microsoft.com/en-us/lib.../gg264090.aspx ) however this is read-only - it cant be used to write the property.

    I am hitting a brick wall. Does anyone know how this can be achieved?
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Set/Write Folder Times using VBA

    Change the folder created time to what? If it's right now, then a workaround you might try:

    1) rename the existing folder to something else
    2) Create a new folder with the same original name, this will have the current create timestamp
    3) Copy the contents of the folder into the newly created folder
    4) Delete the old folder
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Set/Write Folder Times using VBA

    Quote Originally Posted by JBeaucaire View Post
    Change the folder created time to what?
    Any datetime! As long as it is more recent than the previous folder I processed.


    A bit of background
    The reason why I want to do this is because certain systems insist on sorting folders in order of DateCreated. I find this annoying as I want the sort order to be by FolderName. So this creates a problem when sort order by DateCreated <> sort order by FolderName. I figure all I need to do is write VBA to read the folder names into an array and then loop the array to programatically change the DateCreated on each folder. I couldn't care what the datetime value assigned is provided that the value is more recent than the folder I have just processed. (e.g. I could assign a random datetime for the first folder and then increment this by say 10 seconds for each folder processed afterwards)


    Re your workaround
    I have no doubt it will work however I expect this would take a lot longer to process than setting a date property. I will hold out for a 'write folder time' solution and keep your suggestion in mind if my desired solution is impossible (Which I don't think it is. Surely there would have to be a Windows API to set folder times? )
    Last edited by mc84excel; 10-11-2016 at 09:57 PM. Reason: formatting

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Set/Write Folder Times using VBA

    What systems sort by Date Created? (Windows doesn't even display that by default).

    For folders, you need a couple of minor alterations to Chip's code. You need to add this declaration:

    Please Login or Register  to view this content.
    and then alter the SetFileDateTime to use:

    Please Login or Register  to view this content.
    and:

    Please Login or Register  to view this content.
    and you should be good to go.
    Rory

  5. #5
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Set/Write Folder Times using VBA

    Quote Originally Posted by rorya View Post
    What systems sort by Date Created? (Windows doesn't even display that by default).
    It's not Windows. (Personally I have never seen any desktop OS sort by creation date by default)

    I have come across this inane behaviour on some old mp3 players and also on car sound systems (reading files/folders from USB). None of the examples I came across had an option/setting to change the sort method.

    Further explanation - It was for an mp3 project. The music files are in folders and it frustrated me that the folders would not always display alphabetically on every system they were read on. So I figured if I could alter the creation date so that the order matched the alphabetical order then it would display the same regardless whether the OS sorts by foldername or sorts by creation date.

    Thanks for this rorya. I will dig out the project later this month and let you know how it goes!

    UPDATE: It worked! I had given up hope that this would ever be solved! Thank you Rory.
    Last edited by mc84excel; 09-18-2018 at 05:57 PM.

  6. #6
    Registered User
    Join Date
    01-19-2013
    Location
    Italia
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Set/Write Folder Times using VBA

    I modified Chip's code as you suggested, and it generally works fine.
    Neverthless, on some folders the CreateFile function fails with Err=5 and Desc=Denied access.
    Do you have any suggestion on why this issue happens and how to bypass it?
    Thanks in advance, Rorya
    Bruno

  7. #7
    Registered User
    Join Date
    04-20-2021
    Location
    Warwick, RI
    MS-Off Ver
    16
    Posts
    1

    Re: Set/Write Folder Times using VBA

    Thanks. Your edits to Chip's code worked great.

    After making the changes you suggested, I set up an interim Function to make SetFolderDateTime() a bit more human readable.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    * I did skip the GMT setting.
    Last edited by HowdyKeith; 02-08-2023 at 02:40 PM.

+ 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] Manage/ write to same cells in multiple sreadsheets in a folder.
    By ids in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-25-2015, 04:37 AM
  2. is it possible to write DSUM which calculate only the latest 15 times ?
    By netanel99 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-29-2013, 06:50 PM
  3. copy a file several times, rename it and place in a specific folder
    By exceliscool in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-24-2013, 07:35 AM
  4. Trying to Write a Macro to SaveAs Cell "P3" in a Permanent Folder
    By jtal in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-04-2013, 08:09 PM
  5. Replies: 4
    Last Post: 02-25-2012, 08:54 PM
  6. How to write download automatically at fixed times
    By teeger in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-09-2011, 10:46 AM
  7. How to find files in a folder and write the path to the spreadsheet
    By KrNpRiDe in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-25-2009, 06:59 PM

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