get a file path using vba

  • I've searched, but can't find what I'm looking for. I'm using this code to import a file, but I want to be able to save just the path (with no file name) to a variable to use for the output file. This code saves the path and file.

    FileName = Application.GetOpenFilename _
    (FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)

    Any help would be greatly appreciated. I didn't know but what there was a function that I could apply to the 'FileName' variable that would remove the file name...don't know. So...any help is very appreciated!

    Thanks guys!


  • Sorry, maybe I wasn't clear enough. I guess you can never be clear enough huh...

    I am reading in a file with the code I posted in the first post. I want to take that file path and store it in a variable. Yes Magbool, like that, but it's a dynamic path as it will change with different import files. I want to write a file back out to the path of the file that I read in. It won't necessarily be the same path as the workbook as you stated Richie. I actually found that, but didn't know how or if I could apply it to what I wanted.

    So...for example, the main excel file will reside on my desktop. I will import a file from c:\...\my documents and I want the file I write to go to the same location, not to the desktop where the excel file is. I hope this makes sense. Thanks for the help guys!


  • Hi Dave,

    I deliberately used ActiveWorkbook rather than ThisWorkbook - make sure that you understand the difference ;)

    Consider the following (and choose a workbook from a different directory to see the different path names):

    An alternative, depending on what you intend doing, would be to assign the newly opened file to a workbook variable so that you could use that, eg wbkName.Path.

    EDIT :
    You may also be interested in the ExtractPathName function that JW shows here:


  • From the code you posted, it looks like the file is already open. Did you open it with VBA or are you starting the macro after it's already open? That is, is there earlier code that can be used to trap the file's full path, or are you trying to get the path to the directory where a file already open came from origianlly?

  • Thomach, I open the excel file. From the excel file, I import a .csv file to the excel file and then I write a new file out. I want the output to go to the same path that the imput file came from. Is that what you were asking?

  • Quote

    I open the excel file. From the excel file, I import a .csv file to the excel file

    Are you importing the CSV file from the VBA code (which would mean that the path is already in your code) or are you importing the file manually before starting the VBA macro?

    If the latter, I'm not sure you can retrieve its original file location from the csv data that are now part of the Excel file. If you have opened the .csv file before importingits data, then perhaps it can be done (my guess is that it can be since somewhere Windows knows its "Save" location) but I'm not familiar with the commands that would retrieve this path string.

  • I got it figured out. Yes, I import a file using the vba code I included in my first post. I had the whole file's path, but I wanted just the path so that I could write a file to the same path. This code allowed me to remove the file name using the InStrRev function. It actually was what I was looking for, just didn't know such a function existed. The strIn is the path file string. It works like a charm!

    iPos = InStrRev(strIn, "\", , vbTextCompare)
    If (iPos = 0 Or iPos = Null) Then
    MsgBox ("There is an error in file name specified. (NoFileName)")
    NoFileName = Left(strIn, iPos - 1)
    End If

    Thanks everyone for your help! I can see this coming in handy for many other applications.

    Ritchie, do you know how I would make the .Path work. It sounds like it would be easier, but I wasn't sure how to go about it. Thanks again!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!