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: VBA Macro To Find Specific File & Open

  1. #1
    Join Date
    18th November 2006
    Posts
    36

    VBA Macro To Find Specific File & Open

    Hi - I've got a VBA macro set up in one Master excel file that manipulates a number of other files. Both the Master and the other files are all in a folder which is currently called
    C:\Documents and Settings\username\Desktop\Pipeline\ and my VBA script has this defined as the filepath and works exactly as I need it to. I will be moving the folder "Pipeline" to a shared drive on my server so my staff can access it, and will be sending it to colleagues who will be installing it on their own servers so they and their staff can use it. Is there a way to amend the code so that the VBA in the Master file looks in the folder in which it is locate, "Pipeline", no matter what the filepath leading to the "Pipeline" folder is?

    Thanks,

    Kevin

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,707

    Re: Reference Relative File Path

    VB:
    Sub FindMaster() 
        Dim wbResults As Workbook 
         
        With Application.FileSearch 
            .NewSearch 
            .LookIn = "C:\" 
            .FileType = msoFileTypeExcelWorkbooks 
            .Filename = "Master.xls" 
            If .Execute > 0 Then 'Workbooks in folder
                Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0) 
            Else 
                MsgBox "Not found" 
            End If 
        End With 
    End Sub 
    
    

  3. #3
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,322

    Re: VBA Macro To Find Specific File & Open

    You can use
    VB:
    ThisWorkbook.Path 
    
    
    instead of your hard-coded path.
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,707

    Re: VBA Macro To Find Specific File & Open

    Both the Master and the other files are all in a folder
    DOH!

  5. #5
    Join Date
    18th November 2006
    Posts
    36

    Re: VBA Macro To Find Specific File & Open

    Quote Originally Posted by Dave Hawley
    Both the Master and the other files are all in a folder. Doh!
    Little snarky there, Dave, aren't you? The statement isn't so "Doh" when you read the whole thing - the point being the file path and not the fact that they are all in a folder.

    Quote Originally Posted by KWorkman
    Both the Master and the other files are all in a folder which is currently called C:\Documents and Settings\username\Desktop\Pipeline\

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th November 2006
    Posts
    36

    Re: VBA Macro To Find Specific File & Open

    Thanks to both of you for your help, I appreciate it.

    Regards,

    Kevin

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th November 2006
    Posts
    36

    Re: VBA Macro To Find Specific File & Open

    Dave - I think I may have misunderstood and overreacted to your "Doh" post. Reading over it, I think you may have been hitting your own forehead after realizing the files were all in a folder and didn't need the full code you had suggested. If so, I apologize for misreading it to read that I was some kind of idiot for saying the files were in a folder.

    Thanks again for the assistance and regards,

    Kevin

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,707

    Re: VBA Macro To Find Specific File & Open

    --------------------------------------------------------------------------------

    Dave - I think I may have misunderstood and overreacted to your "Doh" post. Reading over it, I think you may have been hitting your own forehead after realizing the files were all in a folder and didn't need the full code you had suggested.
    Yes, you did misunderstand and jump the gun. But, no problem, I just thought you would have known about ThisWorkbook.

  9. #9
    Join Date
    2nd February 2008
    Posts
    3

    Re: VBA Macro To Find Specific File & Open

    Kevin,

    Hopefully I'm not too far off the beaten path but here goes:

    I noticed in the code for the file search it is still references
    VB:
    With Application.FileSearch 
        .NewSearch 
        .LookIn = "C:\" 
    
    
    "C:\" as the base search location. My experience has been whenever a file is located on a 'shared server' the root drive is no longer "C:\" .. it becomes "\\servername\pipeline\Master.xls" .. the letter assignment becomes arbitrary.

    I have also come across where others have mapped a network drive and assigned another letter viarable to the drive name, (Ie. I call my shared drive letter V:\ but my coworder decided to name it W:\) if that is the case the code my dump on you (and or the ender user) by keeping the "C:\"

    If you know the 'actual name' of the server, replace it with the C:\ and it might help future problems down the road.

    Hope this helps,

    Jason

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    22nd September 2010
    Posts
    2

    Re: Reference Relative File Path

    Hi Dave, I get this error, please help:

    Run-time error '445':

    Object doesn't support this action

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Measure Time Taken To Find File & Open
    By bradles in forum EXCEL HELP
    Replies: 4
    Last Post: August 27th, 2007, 20:08
  2. Find And Open File In Specific Folder
    By bpepper82 in forum EXCEL HELP
    Replies: 4
    Last Post: March 13th, 2007, 02:41
  3. Open File at a specific time everyday.
    By jonespa10 in forum EXCEL HELP
    Replies: 1
    Last Post: April 26th, 2006, 23:18
  4. Open specific File depending on date
    By lolo70 in forum EXCEL HELP
    Replies: 2
    Last Post: February 13th, 2006, 02:03
  5. Open to specific worksheet after password macro
    By SSoutherland in forum EXCEL HELP
    Replies: 5
    Last Post: August 9th, 2005, 02:14

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