CODEWITHSUNDEEP
vbaexcelpdf
Matt Taylor
Matt Taylor

Reputation: 671

excel loop thru cells to open pdf

I can get my code to open the selected cells PDF. How can I loop this to run from A9 to finalrow? This is what I have working but only for selected cell.

Function OpenAnyFile(strPath As String)
Set objShell = CreateObject("Shell.Application")
objShell.Open (strPath)
End Function

Sub Cutsheets()
Application.ScreenUpdating = False
On Error GoTo whoa

Dim ordersheet As Worksheet
Dim I As Integer
Dim finalrow As Integer
Dim pdfPath As String
Set ordersheet = Sheet1

finalrow = Cells(Rows.Count, 1).End(xlUp).Row
pdfPath = "P:\ESO\1790-ORL\OUC\_Materials\Material Cutsheets\" & ActiveCell & ".pdf"

'loop through the rows to find PDFs
For I = 9 To finalrow
    If Cells(I, 1) = Application.Intersect(ActiveCell, Range("A9:A" & finalrow)) Then
    Call OpenAnyFile(pdfPath)
    End If
    ordersheet.Select
Next I

whoa:
Exit Sub

Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 189

Answers (1)

user3598756
user3598756

Reputation: 29421

Sub Cutsheets()
    Application.ScreenUpdating = False
    On Error GoTo whoa

    Dim I As Integer
    Dim finalrow As Integer
    Dim pdfPath As String

    'loop through the rows to find PDFs
    With Sheet1
        finalrow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For I = 9 To finalrow
            pdfPath = "P:\ESO\1790-ORL\OUC\_Materials\Material Cutsheets\" & Cells(I, 1).Value & ".pdf"
            Call OpenAnyFile(pdfPath)
        Next I
    End With

whoa:
    Application.ScreenUpdating = True

End Sub

Upvotes: 3

Related Questions