+ Reply to Thread
Results 1 to 6 of 6

How to retrieve part of a filename?

  1. #1
    Registered User
    Join Date
    05-23-2012
    Location
    Japan
    MS-Off Ver
    Excel 2007
    Posts
    42

    How to retrieve part of a filename?

    I am opening several excel files in a folder and then compare the said file on another file on a different folder. So i thought i could do it like this since those 2 files has almost the same filename only differs on date.

    Ex.
    Folder1
    Tokyo_201204_Generic.xls <----this is always the format of the filenames City Name _ Date _ Generic
    Hiroshima_201204_Generic.xls

    Folder2
    Tokyo_201205_Generic.xls
    Hiroshima_201205_Generic.xls

    So if I open [Tokyo_201204_Generic.xls] I need to compare it to [Tokyo_201205_Generic.xls]. To be able to do it I need to get "Tokyo_" and compare if there is a file on folder2 with filename that starts with "Tokyo_". I can't include the date to the comparison since the date varies every month. Folder1 and Folder2 paths are user inputs. How do I do this with macro?

    Any help would be greatly appreciated.

  2. #2
    Registered User
    Join Date
    10-13-2011
    Location
    pune,india
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How to retrieve part of a filename?

    Hi,

    You can use mid function

    dim test as string
    test = your file name

    if ((mid(test,5))= "Tokyo") then 'this 5 will vary as per lenght of yor city name
    msgbox("matchfound")
    else
    msgbox("not found")
    end if

    I hope this will help you

  3. #3
    Registered User
    Join Date
    05-23-2012
    Location
    Japan
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: How to retrieve part of a filename?

    Thank you gaurisneha but I think it won't work that way.
    Each folder contains 100+ files and City Names have different lengths
    is there a way that you can make a macro search for an "_" and then
    copy the characters before it. that way I can get the city name...I think

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,195

    Re: How to retrieve part of a filename?

    how's this?
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-23-2012
    Location
    Japan
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: How to retrieve part of a filename?

    Thank you very much,...works fine..

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,195

    Re: How to retrieve part of a filename?

    Glad it works thanks for the feedback, here's another idea, little shorter:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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