Results 1 to 15 of 15
  1. #1
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80

    Evaluating Files in a Folder via fso

    Situation: A folder contains small excel files, normally one per day, but sometimes multiples are created on the same day. Need to somehow use fso to look in the folder and count how many files already exist with a "date created" of today, so that each successive filename can have a flag such as (1), (2), (3), etc., to prevent overwriting or "file already exists" warnings. Have played around with the getFolder and getFile methods but no luck yet, so any advice welcome (including an easier alternative to the (1),(2),(3) thing if there is one). Main thing is, I don't want the user to be bugged about changing the filename because it already exists, so some kind of flag for successive runs on the same day is needed.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If your code is creating the filename then why not just append + 1 to it without warning anyone? Or maybe advising and continuing vs warning.
    Sorry if I'm not understanding the process as there's room for interpretation here.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    The code creates the filename, and the first file on any given date would be myfilename (1).xlsx. But on rare occasions there may be a second or third or even fourth file generated on the same day, so what number goes inside the parentheses depends on how many files already exist in the destination folder. When building the new filename, I need to look at the folder and see how many files are already there for this date. If none it gets (1), if one it gets (2), etc. Hope that clarifies.

    FWIW, I've been able to get a count of ALL files in the folder using the fso.getFolder method. I can also get at the properties of any given file (including its name or creation date) via fso.GetFile, but the latter only works for one specific file. What I need is something that simply says "Okay, how many files in that folder were created today?" That number would tell me whether this is the first run of the day, or the second, or what.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Would you post your code for creating the files? Or the DB?

    Edit: I'm looking to see how you are creating the file name......

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Have a look at my extended file properties example database http://www.mendipdatasystems.co.uk/e...ies/4594398115
    This can be used on a single file or all files of a specific format in a folder e.g. .Xlsx files
    The code uses GetDetailsOf to retrieve all extended file properties including those you want and saves them to a table
    You could then run queries on selected properties and hopefully get what you need.

    Hope that helps
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Seems between writing a novel and making dinner, I took too long. Oh well, not going to dump all of this now...

    I know of no way to replicate or call on Windows ability to append the next number to a set of files with the same name. Like a lot of other tasks there's probably several approaches you could take and the suitability of any of them would be based on your process.

    One might be to simply kill the last file and create the new one, assuming the prior one is now out of date.

    Another might be to store only the last filename in a table, over writing the value each time but increment by +1, in which case you don't need to count files.

    Another logic might be
    - create a loop from 1 to n number of files. Loop through files in folder.
    - Mid function gets remainder of string after where Instr finds "(" (e.g. 2).xlsx
    - Val gets the number 2
    - if that value is greater than the prior value (stored in a variable) make that the new variable value
    - move to next file and check again
    - when finished, +1 to the variable provides the number for the new file name
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    With fso.getFolder("path of folder").Files I can get a count of ALL files in the folder -- not good enough, I need a count of only the files created today.
    With fso.getFile("filepathname") I can get the DateCreated property of any one file -- not good enough, I need to evaluate the DateCreated property of ALL the files in the folder and count only those created today.

    Hope that further clarifies.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I need a count of only the files created today.
    Unless things changed from long ago I'd be leery about relying on Windows file date properties much. I have seen the same date/times in several properties such as created and modified yet I knew they were not the same. So I mentioned storing the file info in a table. Also mentioned looping through the files although it wasn't for dates. None of that helped?

  9. #9
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    Did the looping thing and, as usual, it turned out to be way simpler than I expected. Thanks to all.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Did you look at using extended file properties? No looping required.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    The code creates the filename
    What about appending the date as part of the file name?

  12. #12
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    For isladogs, I did look at your extended properties thing after the fact, impressive but overkill for what I need right now. Thanks for pointing me to it, though, might be useful down the road.
    For moke123, as a matter of fact the filename already includes the date, e.g., "mysalesfile 12/5/19.xlsx. But if the user runs a SECOND file on 12/5, I need it to NOT overwrite the previous one, hence the solution of adding (1) or (2) or whatever after the date. Solved that by looping (For Each/Next) through all files in the folder and looking at the DateCreated property. If it equals today a counter gets bumped by one, and at the end of the process that number + 1 tells me what the next filename needs to be. It was actually pretty simple, only half a dozen lines of code, once I managed to think my way through the logic.

    Again, thanks to all who chimed in, and happy holidays.

  13. #13
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    And just in case somebody else might benefit from it, here's the code that finally worked:

    Code:
    Public Sub Test1() Dim fso As Object, objFiles As Object, lngFileCount As Long, Fl As Object lngFileCount = 0 Set fso = CreateObject("Scripting.FileSystemObject") Set objFiles = fso.GetFolder("C:\Test1").Files For Each Fl In objFiles If DateValue(Fl.DateCreated) = Date Then lngFileCount = lngFileCount + 1 Next Fl Set objFiles = Nothing Set fso = Nothing End Sub

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just a suggestion for renaming your files. I would omit the forward slashes and The bracketed (1) etc.
    Instead include the time as well as the date using a yyyymmddhhnnss format e.g. 20191205223619
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I guess I don't/didn't understand what you wanted.

    It sounded to me like you could have many Excel files with different names on 12/5/19 in the same sub folder.
    So if there was a 2nd file created with the same name as another file, created on the same day, you wanted to have a sequence number added to the file name. I don't see where you check to file name........
    but if it is working for you, kudos...


    Good luck with your project......

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

Similar Threads

  1. Files in A Folder
    By DMT Dave in forum Access
    Replies: 1
    Last Post: 03-06-2019, 03:32 AM
  2. Replies: 10
    Last Post: 09-09-2015, 03:25 AM
  3. Replies: 1
    Last Post: 05-15-2015, 10:58 AM
  4. How to get name of files in a folder
    By behnam in forum Programming
    Replies: 1
    Last Post: 09-18-2014, 07:46 AM
  5. Moving ALL Files in a Folder
    By JoeM in forum Programming
    Replies: 4
    Last Post: 04-19-2013, 01:59 PM

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