macro to match part of PDF file name in cell with folders & subfolders to open it

[Ali M]

Hi guys
I search for macro to match part of file name in cells for column C across sheets with file names in folders & subfolders
the PDF files names will be like this in column C for each sheet
REFERENCE OPERATION NUMBER TK_12346757
DEL PROCESS REF HJ123345
CHECK VERSUS GT-1234556

the PDF files names will be TK_12346757,HJ123345,GT-1234556 in folders & subfolders .
so when match the part of PDF file name in column C for each sheet with file name in folders & subfolders should be based on the last item because the last item is matched . then should hyperlink the cells in column C for each sheet and open it when click cell , if the last item is not matched when match the part of PDF file name in column C for each sheet with file name in folders & subfolders should pops message "the file is not found ".
the files will be in folders & subfolders like this
C:\Users\RAMDANI\Desktop\Folder\PRT
C:\Users\RAMDANI\Desktop\Folder\PRT\output\
C:\Users\RAMDANI\Desktop\Folder\PRT\output\operation\
thanks in advance

 

[Ali M]

I see the problem from Arabic Language . there are folders names by Arabic , it doesn't open the files where are in Arabic folders names , but if I change folder name to English will open without any problem .
honestly this is the first time I face like this problem from Arabic language despite of I use many macros like this with Arabic language for folders names . this is big headache now .
any idea you have?

 

[John_w]

Record a macro whilst you create a hyperlink to a PDF file in the folders with Arabic characters and post the code here.

 

[Ali M]

You mean before run your code where hyperlink should run record macro after that I run your code and will hyperlink in column C ?
but when I enter inside vba editor the sub Macro1 () is empty !
this is what I got

Sub Macro1()
'
' Macro1 ماكرو
'

'
End Sub

there is no any procedure .

 

[John_w]

You mean before run your code where hyperlink should run record macro after that I run your code and will hyperlink in column C ?

No. Totally separate from my macro. I want to see what Arabic characters look like in a hyperlink address.

1. Start the macro recorder.
2. Click any empty cell, right-click and choose Link to display the Insert Hyperlink dialogue. Browse to and select a PDF file in the Arabic characters folder path.
3. Stop the macro recorder.

For example, my recorded macro is:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("C2").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "C:\Temp\Excel\1001.pdf", TextToDisplay:="Test link"
End Sub

 

[Ali M]

here is what I got

Sub Macro1()
'
' Macro7 ماكرو
'

'
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "pdf%20Folder\اغسطس\مبيعات\DF%20100045.pdf", TextToDisplay:= _
        "pdf Folder\اغسطس\مبيعات\DF 100045.pdf"
End Sub

 

[John_w]

Thanks - I've reproduced the problem.

The problem is caused by this DIR command not outputting foreign characters (Unicode/UTF-8):

    command = "cmd /c DIR /S /B " & Chr(34) & folderPath & Chr(34)
    files = Split(WSh.Exec(command).StdOut.ReadAll, vbCrLf)

I also tried setting the code page at the same time:

    command = "cmd /c chcp 65001 > nul & DIR /S /B " & Chr(34) & folderPath & Chr(34)

and redirecting the output to a text file, but neither had any effect.

The problem is described on these SO pages: How to do proper Unicode and ANSI output redirection on cmd.exe? and How to make Unicode charset in cmd.exe by default?

Therefore, a different approach is needed to create the hyperlinks containing Arabic characters in the folder path (or file name). This uses FileSystemObject to recursively scan the folder tree to find each file. And because it uses VBA strings (which are stored as Unicode), without recourse to external commands, the foreign characters are seen correctly.

Public Sub Find_Files_Create_Hyperlinks()

    Dim mainFolder As String
    Dim ws As Worksheet
    Dim Ccell As Range
    Dim foundFile As String
   
    mainFolder = "C:\Users\RAMDANI\Desktop\Folder\PRT\"
       
    'Loop all sheets

    For Each ws In ThisWorkbook.Worksheets
       
        'Loop column C cells starting at C2
       
        For Each Ccell In ws.Range("C2", ws.Cells(ws.Rows.Count, "C").End(xlUp))
       
            'Find the last part of this cell's value (the file name being sought) in mainFolder and its subfolders
           
            Ccell.Hyperlinks.Delete
            foundFile = FindFile(mainFolder, Mid(Ccell.Value, InStrRev(Ccell.Value, " ") + 1) & ".pdf")
           
            If foundFile <> "" Then
                ws.Range("L1").Value = foundFile
                Ccell.Worksheet.Hyperlinks.Add Anchor:=Ccell, Address:=foundFile, TextToDisplay:=Ccell.Value
            Else
                'File not found
                If MsgBox("File not found." & vbCrLf & vbCrLf & _
                       "Sheet: " & ws.Name & ", cell: " & Ccell.Address(False, False) & vbCrLf & _
                       "File: " & Mid(Ccell.Value, InStrRev(Ccell.Value, " ") + 1) & ".pdf" & vbCrLf & vbCrLf & _
                       "Click OK to continue or Cancel to quit.", vbExclamation + vbOKCancel) = vbCancel Then Exit Sub
            End If
           
        Next
       
    Next
      
End Sub


Private Function FindFile(folderPath As String, findFileName As String) As String

    Static FSO As Object
    Dim FSfolder As Object
    Dim FSsubfolder As Object
    Dim FSfile As Object
   
    If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
   
    FindFile = ""
   
    Set FSfolder = FSO.GetFolder(folderPath)
    For Each FSsubfolder In FSfolder.SubFolders
        If FindFile <> "" Then Exit For
        For Each FSfile In FSsubfolder.files
            If FindFile <> "" Then Exit For
            If StrComp(FSfile.Name, findFileName, vbTextCompare) = 0 Then FindFile = FSfile.Path
        Next
        'Search subfolders if file not found yet
        If FindFile = "" Then
            FindFile = FindFile(FSsubfolder.Path, findFileName)
        End If
    Next
   
End Function

 

[Ali M]

thanks again
show strange problem ,I don't have explanation for that !
the code refuses hyperlink some cells despite of I'm pretty sure the file name is the same thing for last part as in cells C3,C4,
surprisingly accept hyperlink in C2 and open it despite of cells C2,C3,C4 all of are existed in the same folder !

I'm still survive where is my bad , I don't find out anything so far

 

[Ali M]

Hi John
I think your code just deals with file names don't contain spaces among character in cells for column C
for examples
REF_NN123444
then will hyperlink and open without any problem , but if there are file names contain spaces among character in cells for column C
example:
REF NI12000 MM200
SN MM2000
ADF WE1200 VB 500
then will show the message file is not found for theses files names contain spaces among character despite of they are existed in folders and subfolders and matched with the same name .
so I hope your idea to fix that
thanks again .

 

[John_w]

As per your OP, this part of the code reads the characters after the last space in the cell value as the PDF file name to seek in the folder tree.

Mid(Ccell.Value, InStrRev(Ccell.Value, " ") + 1) & ".pdf"

but if there are file names contain spaces among character in cells for column C
example:
REF NI12000 MM200
SN MM2000
ADF WE1200 VB 500

For the above, what are the actual PDF file names to seek? And what is/are the rule/rules that should be programmed to extract those file names from the cells and the file names of the examples in your OP?

 

[Ali M]

For the above, what are the actual PDF file names to seek? And what is/are the rule/rules that should be programmed to extract those file names from the cells and the file names of the examples in your OP?

the file name could be whole item without spaces for instance SN_1000

and there files names contains three or four parts as maximum for each file name , will contain space after each part except last part as I posted in OP and last post .

the files name will contains letters, numbers,_ or - .

so the problem in files names contain spaces among character for each part from the whole file name .