+ Reply to Thread
Results 1 to 3 of 3

Problem using a loop for file directory

  1. #1
    Registered User
    Join Date
    10-03-2014
    Location
    Montreal
    MS-Off Ver
    2010
    Posts
    16

    Problem using a loop for file directory

    I am currently running a macro that uses a unique reference number every day. This specific number is contained in the most recent file from a folder. In order to illustrate this problem, I will give you an example.

    Suppose that you are in a file directory

    "File name2014-10-05-000011485"
    "File name2014-10-04-000011475"
    "File name2014-10-03-000011465"

    In order to run my macro, I need to add the most RECENT reference number. As you can see, the file is always the same except for the last 4 numbers in the file name as well as the date. Everyday the last numbers in the file name increase by an amount of 5-10. As such, I was thinking of writing a code that would copy the last day's reference number and create a loop to find the most recent file.

    I was thinking of doing something like:
    Dim i as string
    set i= 5 to 10

    Could someone please enlighten me with regards to how I write to add a loop to the last day's number until it gets the most recent number?

    Once I get that number, I would like to paste it in my excel sheet and use is as my new reference number and redo the exact same process the day after.

    Thank you very much,
    Mike

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Problem using a loop for file directory

    Why not just rename the file?
    save the file in a folder
    Put the path in the macro
    Click on the button


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 10-06-2014 at 05:13 PM.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Problem using a loop for file directory

    Hi,

    Here's a function I use to find the latest time stamped file in a directory
    You just need to pass the folder name to search through as a string. You can also remove the reference to FileSpec if all the files are the same extension.

    You'll need to change the last line to

    Please Login or Register  to view this content.
    to return the last number.


    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ 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. Replies: 7
    Last Post: 05-30-2014, 04:10 PM
  2. Replies: 0
    Last Post: 03-13-2013, 09:08 PM
  3. Need VBA code to search file in directory and sub-directory and show result
    By johnchencanada in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2012, 11:13 PM
  4. Saving file in current directory after accessing file in another directory
    By vuxsa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-28-2011, 11:37 AM
  5. Replies: 3
    Last Post: 06-11-2009, 07:08 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