VBA Functions in Excel

Question

I have an Excel spreadsheet. One of the columns in the spreadsheet contains the name of a pdf file; page number (name of file;5). I would like help with writing a VBA function so that when the user clicks in any of the cells in that column the name of the file and the page number are passed as variables and the pdf file opens to the page specified. Any help greatly appreciated.

Answer 1

To achieve this:

1. Create an event handler for the worksheet's SelectionChanged event.
2. Create a parsing function for the cell's value.
3. Create a subroutine that launches Acrobat with the page number parameter.

See the following sample code. The constants should be changed according to your system. This code needs to be pasted in the worksheet's VBA macros editor.

Private Const MyPathColumn As Integer = 3
Private Const PathToAcrobatExe As String = _
              "C:\Program Files\Adobe\Reader 8.0\Reader\Acrord32.exe"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo errHnd
    If Target.Cells.Count = 1 Then
        If Target.Column = MyPathColumn Then
            If Len(Target.text) > 0 Then
                Dim filePath As String
                Dim pageNumber As Integer
                Call ParsePath(Target.text, filePath, pageNumber)
                Call RunPdf(filePath, pageNumber)
            End If
        End If
    End If
    Exit Sub
errHnd:
    Call MsgBox("Error Opening File", vbCritical)
End Sub

Private Function getShellCommand(filePath As String, _
                         pageNumber As Integer) As String
    getShellCommand = PathToAcrobatExe & " /A ""page=" _
                   & CStr(pageNumber) & """ """ _
                   & filePath & """"
End Function

Private Sub RunPdf(filePath As String, pageNumber As Integer)
    Call Shell(getShellCommand(filePath, pageNumber), _
               vbMaximizedFocus)
End Sub

Private Sub ParsePath(text As String, ByRef filePath As String, _
                      ByRef pageNumber As Integer)
    Dim parts() As String
    parts = Split(text, ";")
    filePath = parts(0)
    pageNumber = CInt(parts(1))
End Sub