Announcement

Collapse
No announcement yet.

VBA Macro To Find Specific File & Open

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Re: Reference Relative File Path

    Code:
    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

    Comment


    • #3
      Re: VBA Macro To Find Specific File & Open

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

      Comment


      • #4
        Re: VBA Macro To Find Specific File & Open

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

        Comment


        • #5
          Re: VBA Macro To Find Specific File & Open

          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.

          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\

          Comment


          • #6
            Re: VBA Macro To Find Specific File & Open

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

            Regards,

            Kevin

            Comment


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

              Comment


              • #8
                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.

                Comment


                • #9
                  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
                  Code:
                  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

                  Comment


                  • #10
                    Re: Reference Relative File Path

                    Hi Dave, I get this error, please help:

                    Run-time error '445':

                    Object doesn't support this action

                    Comment


                    • #11


                      Re: VBA Macro To Find Specific File & Open

                      Please do not post questions in threads started by other members.

                      Start your own thread, give it an accurate and concise title as required by the rules here and explain your issue fully.

                      If you think this thread can help illustrate your issue, you can include a link by copying the URL from the address bar of your browser and pasting into your message.

                      You have previously been warned about this (http://www.ozgrid.com/forum/showthre...172#post523172) - even though that thread just about 3 years old, with 2 posts to your credit you should have been aware.

                      Comment

                      Working...
                      X