Code for Assigning the Name of a File in a Specific Folder to a String Variable

I would like to capture the name of a specific file in specific folder in a string variable (MyString) such that the string variable contains only the first two words in the name of the document and does not include the full path of the file or the file extension. To explain it a little further

If I have a file C:\Important Files\Investigation Results.doc
I would like MyString to be equal to Investigation Results

If I have a file C:\Important Files\Test Results\Blood Results.doc
I would then like MyString to be equal to Blood Results

Thanks for your help in anticipation.
LVL 1
FaheemAhmadGulAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Patrick MatthewsConnect With a Mentor Commented:
Dim TestString As String, TestArr As Variant, MyString As String

TestString = Dir("C:\Important Files\*.doc") 'modify as needed
If TestString <> "" Then
    TestString = Mid(InStrRev(TestString, "\") + 1)
    TestString = Left(TestString, InStr(1, TestString, ".") - 1)
    TestArr = Split(TestString, " ")
    If UBound(TestArr) > 0 Then
        MyString = TestArr(0) & " " & TestArr(1)
    Else
        MyString = TestArr(0)
    End If
Else
    'code for file not found
End If
0
 
borgunitCommented:
Is your issue getting the filename or how to parse it once you have the whole path and file?
0
 
Patrick MatthewsCommented:
Dim TestString As String, TestArr As Variant, MyString As String

TestString = "C:\Important Files\Investigation Results.doc"
TestString = Mid(InStrRev(TestString, "\") + 1)
TestString = Left(TestString, InStr(1, TestString, ".") - 1)
TestArr = Split(TestString, " ")
If UBound(TestArr) > 0 Then
    MyString = TestArr(0) & " " & TestArr(1)
Else
    MyString = TestArr(0)
End If
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
FaheemAhmadGulAuthor Commented:
Many thanks for your response to my question. I am sorry I have realized that I have not asked my question properly. The code I want should be such that it first gets the name of the only file that exists in a given folder and then parses that file name such that it assigns the first two words from the name of the only file that exists in that  specific folder to MyString. That is the code writer knows the name of the folder and also knows that there is only one word document that exists in that folder, but he does not know the name of the file that exists in that folder.

So there is a folder called C:\Important Files\    and there is only one word document in this file the name of which consists of two words and the extension name which would be .doc. The code should get those two words that make the file name and assign it to MyString.
As I am revising the question, I am raising the points for this question to 500.
0
 
FaheemAhmadGulAuthor Commented:
Thank you for your response. I get an error at the following line
TestString = Mid(InStrRev(TestString, "\") + 1)

The error message says Compile error. Argument Not Optional
0
 
Patrick MatthewsCommented:
Sorry :)

TestString = Mid(TestString, InStrRev(TestString, "\") + 1)
0
 
FaheemAhmadGulAuthor Commented:
Please also note that I am using this code in a Visual Basic for Applications Project.
0
 
FaheemAhmadGulAuthor Commented:
Many thanks. This worked perfectly. I am very grateful.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.