0

My code prompts the user to select a file. I want to set a variable to the folder name of the file location but the file is located in a subfolder.

I have this code to open the file.

fileAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.csv), _ 
*.csv", Title:="Select a file")
If fileAndPath = False Then Exit Sub

This is the filepath C:\Store Location\Employees\Contact Information\Phone Numbers\11373

I want to extract the 11373 part and store it as a variable

Community
  • 1
  • 1
Garett
  • 19
  • 1
  • 7
  • Possible duplicate of [Find the directory part (minus the filename) of a full path in access 97](https://stackoverflow.com/questions/418622/find-the-directory-part-minus-the-filename-of-a-full-path-in-access-97) – JNevill Dec 04 '18 at 21:42

1 Answers1

0

Okay, So you want to set a default folder for getOpenFileName. Precede getOpenFileName with ChDir "D:\Test". If the drive is other than C: then precede ChDir with ChDrive "DriveLetter:"

Sub test()
ChDrive "D:"
Application.DefaultFilePath = "D:\Test"

fileAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.csv),*.csv", Title:="Select a file")
If fileAndPath = False Then
Exit Sub
End If
folderPath = Left(fileAndPath, InStrRev(fileAndPath, "\") - 1)
MsgBox (Mid(folderPath, InStrRev(folderPath, "\") + 1))
End Sub
MD AZAD HUSSAIN
  • 202
  • 1
  • 6
  • Hi, I updated my question since I realized that the file is located in a subfolder. I tried your code but it returned the whole filepath which I think is because the file is located within a folder within a folder within a folder if that makes sense. Do you know how to get around that – Garett Dec 04 '18 at 22:18
  • Updated my answer as you required. Kindly mark as answer if it helps. – MD AZAD HUSSAIN Dec 04 '18 at 22:40
  • Hmm, it still returns the whole filepath – Garett Dec 04 '18 at 22:53
  • What do you mean by `folder name of the file location` and `main folder where the file is located`. Kindly post path names and what do you want to return. – MD AZAD HUSSAIN Dec 04 '18 at 23:00
  • This is the filepath C:\Store Location\Employees\Contact Information\Phone Numbers\11373, I just want to extract the 11373 part and store it as a variable – Garett Dec 04 '18 at 23:12
  • Update the answer again. Check now it should give you 11373 in the msgbox. – MD AZAD HUSSAIN Dec 04 '18 at 23:28