problem compile error "method of data are not found" to open file based on cell

KalilMe · Jun 29th 2021

hi experts

I face showing this error ' compile error' in word open in this line

Code

AcroApp.Open searchFolder & fileName

the code should open file from pdf extensions when write in A1

any help to fix it

Code

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim searchFolder As String, fileName As String

   Dim AcroApp As Acrobat.CAcroApp
    
    searchFolder = "C:\search\files\reports\"
    
    If Right(searchFolder, 1) <> "\" Then searchFolder = searchFolder & "\"
    
    If Target.Address = "$A$1" Then
        fileName = Dir(searchFolder & Target.Value & ".pdf")
        If fileName <> vbNullString Then
            If MsgBox(fileName & " exists.  Do you want to open it?", vbYesNo + vbInformation, "Open PowerPoint file?") = vbYes Then
                If AcroApp Is Nothing Then Set AcroApp = CreateObject("AcroExch.App")
                AcroApp.Open searchFolder & fileName
            End If
        End If
    End If
    
End Sub

Display More

**rory** · Jun 29th 2021

AFAIK, that's not how you open a PDF file using Acrobat. Have a look at this: https://www.excelguru.ca/forum…-VBA-interacting-with-PDF

KalilMe · Jun 29th 2021

actually I did not see where is the location the file in range

**rory** · Jun 29th 2021

I don't understand what you mean?

My point is that you are using the wrong object to open the file.

**royUK** · Jun 29th 2021

Maybe like this

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim searchFolder As String, fileName As String
    Dim oShell As Object
      If Target.Address = "$A$1" Then
    searchFolder = "C:\search\files\reports\"
'    If Right(searchFolder, 1) <> "\" Then searchFolder = searchFolder & "\"
        fileName = Dir(searchFolder & Target.Value & ".pdf")
        If fileName <> vbNullString Then
            If MsgBox(fileName & " exists. Do you want to open it?", vbYesNo + vbInformation, "Open PowerPoint file?") = vbYes Then
                Set oShell = CreateObject("WScript.Shell")
                oShell.Run searchFolder & fileName
            End If
        End If
    End If
    
End Sub

Display More

KalilMe · Jun 29th 2021

royUK thanks but nothing happens and no error

**royUK** · Jun 30th 2021

Have you looked at Rory's suggestion?

KalilMe · Jul 3rd 2021

royUK actually your code works perfectly. my mistake is writing file extension . it shouldn't be when write the file name .

thanks so much for your solution

KalilMe · Jul 3rd 2021

royUK do me favor,please ? if it's possible add warning message when write extension then should show the message" please, write only file name"

the aim of this to overcome for this problem when write extension and the code doesn't work

thanks again

**royUK** · Jul 3rd 2021

See if this works

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim searchFolder As String, fileName As String, str As String
    Dim oShell As Object
    With Target
        If .Address = "$A$1" Then
            searchFolder = "C:\search\files\reports\"
            ' If Right(searchFolder, 1) <> "\" Then searchFolder = searchFolder & "\"
            If UCase(Right(.Value, 3)) = UCase(pdf) Then str = Mid(.Value, 1, InStr(1, .Value, ".") - 1)
            fileName = Dir(searchFolder & .Value & ".pdf")
            If fileName <> vbNullString Then
                If MsgBox(fileName & " exists. Do you want to open it?", vbYesNo + vbInformation, "Open PowerPoint file?") = vbYes Then
                    Set oShell = CreateObject("WScript.Shell")
                    oShell.Run searchFolder & fileName
                End If
            End If
        End If
    End With
End Sub

Display More

KalilMe · Jul 3rd 2021

royUK nothing changes still I have to write the file without extension

**royUK** · Jul 4th 2021

Attach an example of the workbook

KalilMe · Jul 4th 2021

here is the file

**royUK** · Jul 4th 2021

You need to change this line back to your folder path.

Try this

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim searchFolder As String, fileName As String, str As String
    Dim oShell As Object
    With Target
        If .Address = "$A$1" Then
            searchFolder = "C:\temp\"
            ' If Right(searchFolder, 1) <> "\" Then searchFolder = searchFolder & "\"
            If UCase(Right(.Value, 3)) = UCase("pdf") Then str = Mid(.Value, 1, InStr(1, .Value, ".") - 1)
            fileName = Dir(searchFolder & str & ".pdf")
            If fileName <> vbNullString Then
                If MsgBox(fileName & " exists. Do you want to open it?", vbYesNo + vbInformation, "Open PowerPoint file?") = vbYes Then
                    Set oShell = CreateObject("WScript.Shell")
                    oShell.Run searchFolder & fileName
                End If
            End If
        End If
    End With
End Sub

Display More

KalilMe · Jul 4th 2021

may you explain me why I have to change directory despite of it's the same drive ?

**royUK** · Jul 4th 2021

This line points to a temp folder that I created in my C Drive. You need to put the path to your file instead.

Code

searchFolder = "C:\temp\"

**royUK** · Jul 4th 2021

This is the path in your first post.

searchFolder = "C:\search\files\reports\"

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim searchFolder As String, fileName As String, str As String
    Dim oShell As Object
    With Target
        If .Address = "$A$1" Then
            searchFolder = "C:\search\files\reports\"
            ' If Right(searchFolder, 1) <> "\" Then searchFolder = searchFolder & "\"
            If UCase(Right(.Value, 3)) = UCase("pdf") Then str = Mid(.Value, 1, InStr(1, .Value, ".") - 1)
            fileName = Dir(searchFolder & str & ".pdf")
            If fileName <> vbNullString Then
                If MsgBox(fileName & " exists. Do you want to open it?", vbYesNo + vbInformation, "Open PowerPoint file?") = vbYes Then
                    Set oShell = CreateObject("WScript.Shell")
                    oShell.Run searchFolder & fileName
                End If
            End If
        End If
    End With
End Sub

Display More

KalilMe · Jul 4th 2021

it just open my computer not open my file

**royUK** · Jul 4th 2021

How can it open your computer?

This works for me

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim searchFolder As String, fileName As String, str As String
    Dim oShell As Object
    With Target
        If .Address = "$A$1" Then
            searchFolder = "C:\temp\"
            ' If Right(searchFolder, 1) <> "\" Then searchFolder = searchFolder & "\"
            If UCase(Right(.Value, 3)) = UCase("pdf") Then str = Mid(.Value, 1, InStr(1, .Value, ".") - 1)
            fileName = Dir(searchFolder & str & ".pdf")
            If fileName <> vbNullString Then
                If MsgBox(fileName & " exists. Do you want to open it?", vbYesNo + vbInformation, "Open PowerPoint file?") = vbYes Then
                    Set oShell = CreateObject("WScript.Shell")
                    oShell.Run searchFolder & fileName
                End If
            End If
        End If
    End With
End Sub

Display More

problem compile error "method of data are not found" to open file based on cell

Participate now!

Share