Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: VBA - Open PDF file

  1. #1
    Board Regular
    Join Date
    Jul 2010
    Posts
    721
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - Open PDF file

    Hello All,

    How can we open this pdf file with VBA macro.


    C:\Users\i ' M\Desktop\PDF\test file 123-234.pdf

    Any help would be appreciated

    Regards,

    Humayun
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA - Open PDF file

    Try:

    Code:
    Sub test()    ActiveWorkbook.FollowHyperlink "C:\Users\i ' M\Desktop\PDF\test file 123-234.pdf"
     End Sub
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Jul 2010
    Posts
    721
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Open PDF file

    Thanks DanteAmor for the reply

    I will try the solution you provided and will get back to you on Monday as before that I won’t be having access to my computer. I will keep you posted

    Regards,

    Humayun
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  4. #4
    Board Regular
    Join Date
    Jul 2010
    Posts
    721
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Open PDF file

    Hi Dante,

    I tried your code and its working PERFECT.

    I modified the code a bit to suit my needs.

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)      
                Dim fName As String
                fName = "C:\Users\i ' M\Desktop\PDF\" & Selection.Value & ".pdf" 
    
    
        lrB = Cells(Rows.Count, "B").End(xlUp).Row
    
    
        If Not Intersect(Target, Range("B4:B" & lrB)) Is Nothing Then
        
        ActiveWorkbook.FollowHyperlink fName 
       
    End If
    
    
    End Sub
    Just 2 Issues

    1) I get a system message that the file I am trying to open might not be safe. I want to get rid of this message. I tried application.displayalerts=false but its still giving the message.

    2) When a file is not found - I would want a msgbox to appear stating that "File Not Found" instead of debug message (run time error)

    Regards,

    Humayun
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA - Open PDF file

    Quote Originally Posted by hrayani View Post

    Just 2 Issues

    1) I get a system message that the file I am trying to open might not be safe. I want to get rid of this message. I tried application.displayalerts=false but its still giving the message.

    I'm not sure if it's a Trust Center Settings option or a message from your antivirus.

    2) When a file is not found - I would want a msgbox to appear stating that "File Not Found" instead of debug message (run time error)

    Regards,

    Humayun
    Try this

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      Dim fName As String, lrB As Long
      Cancel = True
      fName = "C:\Users\i ' M\Desktop\PDF\" & Selection.Value & ".pdf"
      lrB = Cells(Rows.Count, "B").End(xlUp).Row
      If Dir(fName) <> "" Then
        If Not Intersect(Target, Range("B4:B" & lrB)) Is Nothing Then
          ActiveWorkbook.FollowHyperlink fName
        End If
      Else
        MsgBox "File Not Found"
      End If
    End Sub
    Regards Dante Amor

  6. #6
    Board Regular
    Join Date
    Jul 2010
    Posts
    721
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Open PDF file

    Hi Dante,

    Sorry for coming back late.

    Very near to the solution - I guess. Actually I am using this BeforeDoubleClick procedure for two things

    Here is the full code

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)  
        Application.Calculation = xlCalculationAutomatic
        
        Dim lrA As Long
        lrA = Cells(Rows.Count, "A").End(xlUp).Row
    
    
     If Not Intersect(Target, Range("A4:A" & lrA)) Is Nothing Then
       
       Sheets("RUNNING ORDER STATUS").Unprotect Password:="merchant"
       
       Sheets("CURRENT PRODUCTION STATUS").Visible = True
       Sheets("CURRENT PRODUCTION STATUS").Select
       
       Sheets("RUNNING ORDER STATUS").Select
       Selection.copy
       
       Sheets("CURRENT PRODUCTION STATUS").Select
       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
       
       Sheets("RUNNING ORDER STATUS").Visible = False
       
         Sheets("RUNNING ORDER STATUS").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFormattingColumns:=True, AllowFormattingRows:=True _
            , Password:="merchant"
     
     Application.Calculation = xlCalculationAutomatic
    
    
    End If
    
    
    
    
      Dim fName As String
      Dim lrB As Long
    
      Cancel = True
    
      fName = "C:\Users\i ' M\Desktop\PDF\" & Selection.Value & ".pdf"
    
      lrB = Cells(Rows.Count, "B").End(xlUp).Row
      If Dir(fName) <> "" Then
    
        If Not Intersect(Target, Range("B4:B" & lrB)) Is Nothing Then
    
          SendKeys "{TAB}"  ' I have added these keys to overcome the system message issue
          SendKeys "{ENTER}" 
    
          ActiveWorkbook.FollowHyperlink fName
    
        End If
      Else
        MsgBox "File Not Found"
      End If
    
    
    End Sub
    The problem is that I get the msgbox whenever I click any cell on the sheet. Whereas I would want the msgbox to appear only when a cell is clicked in Column B and the file is not found. I get the msgbox even if the upper part of the code is running which is linked with Column A.

    Regards,

    Humayun
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA - Open PDF file

    Try this

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      Dim lrA As Long
      lrA = Cells(Rows.Count, "A").End(xlUp).Row
      If Not Intersect(Target, Range("A4:A" & lrA)) Is Nothing Then
        Application.Calculation = xlCalculationAutomatic
        Sheets("RUNNING ORDER STATUS").Unprotect Password:="merchant"
        Sheets("CURRENT PRODUCTION STATUS").Visible = True
        Sheets("CURRENT PRODUCTION STATUS").Select
        Sheets("RUNNING ORDER STATUS").Select
        Selection.Copy
        Sheets("CURRENT PRODUCTION STATUS").Select
        Selection.PasteSpecial Paste:=xlPasteValues
        Sheets("RUNNING ORDER STATUS").Visible = False
        Sheets("RUNNING ORDER STATUS").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
          AllowFormattingColumns:=True, AllowFormattingRows:=True, Password:="merchant"
        Application.Calculation = xlCalculationAutomatic
      End If
      '
      Dim fName As String, lrB As Long
      lrB = Cells(Rows.Count, "B").End(xlUp).Row
      If Not Intersect(Target, Range("B4:B" & lrB)) Is Nothing Then
        If Target.Value = "" Then Exit Sub
        Cancel = True
        fName = "C:\Users\i ' M\Desktop\PDF\" & Target.Value & ".pdf"
        If Dir(fName) <> "" Then
          SendKeys "{TAB}"  ' I have added these keys to overcome the system message issue
          SendKeys "{ENTER}"
          ActiveWorkbook.FollowHyperlink fName
        End If
      Else
        MsgBox "File Not Found"
      End If
    End Sub
    Regards Dante Amor

  8. #8
    Board Regular
    Join Date
    Jul 2010
    Posts
    721
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Open PDF file

    Hi Dante,

    Its working totally opposite.

    Its giving file not found on all columns except column B and also when the upper part of the code is running.

    I want msgbox to be only displayed if a cell in column B is clicked and there is no file with that name

    Regards,
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA - Open PDF file

    Ooops
    Try with this

    Code:
    Dim fName As String, lrB As Long
      lrB = Cells(Rows.Count, "B").End(xlUp).Row
      If Not Intersect(Target, Range("B4:B" & lrB)) Is Nothing Then
        If Target.Value = "" Then Exit Sub
        Cancel = True
        fName = "C:\Users\i ' M\Desktop\PDF\" & Target.Value & ".pdf"
        If Dir(fName) <> "" Then
          SendKeys "{TAB}"  ' I have added these keys to overcome the system message issue
          SendKeys "{ENTER}"
          ActiveWorkbook.FollowHyperlink fName
       
      Else
        MsgBox "File Not Found"
      End If
    End if
    Regards Dante Amor

  10. #10
    Board Regular
    Join Date
    Jul 2010
    Posts
    721
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Open PDF file

    Hi Dante

    Thanks a lot.. works great
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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