Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: get a file path using vba

  1. #1
    Join Date
    17th September 2003
    Posts
    66

    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...

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th January 2003
    Location
    India
    Posts
    1,039
    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745
    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

  4. #4
    Join Date
    17th September 2003
    Posts
    66
    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...

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745
    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):
    VB:
    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

  6. #6
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    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.

  7. #7
    Join Date
    17th September 2003
    Posts
    66
    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?

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    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.

  9. #9
    Join Date
    17th September 2003
    Posts
    66
    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!

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745
    Quote 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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. User Input For File & Path For File Copy
    By G8tor4life in forum EXCEL HELP
    Replies: 4
    Last Post: January 26th, 2008, 01:28
  2. Record Date, Time & File Path/Name of File Opened
    By Michael Simpson in forum EXCEL HELP
    Replies: 1
    Last Post: July 15th, 2007, 15:57
  3. Trim Full File Name & Path to File Path Only
    By Bryan021 in forum EXCEL HELP
    Replies: 4
    Last Post: September 27th, 2006, 23:00
  4. Opened file name or file path using FileDialog
    By TroyArthur in forum EXCEL HELP
    Replies: 1
    Last Post: October 17th, 2005, 10:54

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