Consulting

Results 1 to 6 of 6

Thread: VBA code to search on folder and sub folder for file named in worksheet

  1. #1

    Exclamation VBA code to search on folder and sub folder for file named in worksheet

    Dear VBA express Members,
    I have and excel file includes records which in column H as range H1 = 360001 H2 = 360002 H3 = 360003 ETC
    I asked my clients to save a document in shared folder names with what mentioned above, as a copy of invoice for 360001 named 360001 and the same with the rest
    all what i need to make the VBA give me a file path if it found the invoice in the folder.
    the result should be shown in column I as a path and if i clicked it opens the invoice copy.
    invoices come with extension PDF or tif
    N.B the data numbers of invoices are variable which change every day so i need to check that i have a copy for each invoice and can review it.
    many thanks.ISSUE.jpg

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    See Help on VBA Dir and Excel & VBA Hyperlink
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    you can create a Sub to check if the same invoice exists on the Shared folder
    and create a Hyperlink on your worksheet if it does.
    Private Sub t()
        'put the shared path here. dont forget to add the "backslash"
        Const THE_SHARED_PATH As String = "\\PC_NAME\SHARE_FOLDER\"
        Dim lngLastRow As Long, strFile As String, i As Long
        With Worksheets(1)
            lngLastRow = .Cells(1, 8).End(xlDown)
            If lngLastRow > 1 Then
                For i = 2 To lngLastRow
                    strFile = Dir$(THE_SHARED_PATH & .Range("H" & i) & ".*")
                    If Len(strFile) <> 0 Then
                        .Hyperlinks.Add Anchor:=.Range("I" & i), _
                        Address:=THE_SHARE_PATH & strFile, _
                        ScreenTip:="Invoice number " & .Range("H" & i), _
                        TextToDisplay:=strFile
                    End If
                Next
            End If
        End With
    End Sub

  4. #4
    many thanks for your help,
    now i can't add the file name, so i changed it to be on my own PC as "D:\loma New"
    kindly if we can have quick meeting to show you what I'm facing.

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    Location
    Quote Originally Posted by Ahmed emad View Post
    kindly if we can have quick meeting to show you what I'm facing.
    Attach a sample file to your post so we can see what you are facing.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    Many thanks for your efforts and time.The code worked as forgot to add the last backslash. I noticed that the code takes a long time to get the result, but it gets the results, much appreciated.




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •