Announcement

Collapse
No announcement yet.

get a file path using vba

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!

    Dave...

  • #2
    Hello there:
    If I understand you correctly, you can use the following to store your directory path in a variable.


    dim strDir as string

    strDir = "C:\MyDocuments\MyFiles\"


    Maqbool

    Comment


    • #3
      Hi Dave,

      How about the Path property?

      EG: MsgBox ActiveWorkbook.Path

      HTH
      Cross-poster? Read this: Cross-posters
      Struggling to use tags (including Code tags)? : Forum tags

      Comment


      • #4
        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!

        Dave...

        Comment


        • #5
          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):
          Code:
          Sub Test()
              Dim filename As Variant
              
              MsgBox "ThisWorkbook path is : " & ThisWorkbook.Path
              
              filename = Application.GetOpenFilename(Title:="Test")
              If filename = False Then Exit Sub
              
              Workbooks.Open filename:=filename
              MsgBox "The ActiveWorkbook path is : " & ActiveWorkbook.Path
          
          End Sub
          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:
          http://j-walk.com/ss/excel/tips/tip93.htm


          HTH
          Cross-poster? Read this: Cross-posters
          Struggling to use tags (including Code tags)? : Forum tags

          Comment


          • #6
            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?
            Best Regards,
            Tom
            ---------------------------
            Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

            Comment


            • #7
              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?

              Comment


              • #8
                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.
                Best Regards,
                Tom
                ---------------------------
                Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

                Comment


                • #9
                  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)")
                  Else
                  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!

                  Comment


                  • #10
                    Originally posted by mr. white
                    Richie, 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!
                    Hi Dave,

                    Yep - see my last post
                    Cross-poster? Read this: Cross-posters
                    Struggling to use tags (including Code tags)? : Forum tags

                    Comment


                    • #11


                      Re: get a file path using vba

                      use this instead
                      dec = ThisWorkbook.Path & "\"

                      Comment

                      Working...
                      X