Home VBA code executes but only if I step thru it
Reply: 1

VBA code executes but only if I step thru it

davidnt
1#
davidnt Published in 2018-01-12 18:39:21Z

I used some code from Close an opened PDF after opening it using FollowHyperlink to create the following code to open a pdf file and rename it. The code runs fine but only if I break execution at MsgBox "Break Here" and step into it with the F8 key. Any ideas on why it won't execute automatically?

Sub OpenPDF()
    'Opens PDF Scaned file & saves it to another folder
    '***ErrorHandler***
    On Error Resume Next
    '***Declare Objects****
    Dim objectWMI As Object
    Dim objectProcess As Object
    Dim objectProcesses As Object
    Dim Path As String
    Dim MyDir As String
    '***Opens a new workbook if there are no active workbooks***
    '***There must be an active workbook for FollowHyperlink to function***
    nowbs = Application.Workbooks.Count
    If nowbs = 1 Then
        Application.Workbooks.Add
    Else
    End If
    '***Saves current Excel path
    MyDir = CurDir
    '***Sets path to Ricoh Scans
    PDFDir = "S:\Ricoh Scans"
    ChDir PDFDir
    '***Gets filename for PDF scan
    Path = Application.GetOpenFilename(filefilter:="PDF file (*.pdf), *.pdf")
    '***Opens PDF file***
    ActiveWorkbook.FollowHyperlink Path
    '***Sets Excel as active application
    AppActivate "Microsoft Excel"
    '***Prompts for PO number****
    MyPONum = InputBox("Enter PO Number", "PO Editor", "30500")
    '***If user selects Cancel on inputbox then xl closes Acrobat and exits sub
    If MyPONum = vbNullString Then
        GoTo EndAll
    Else
    End If
    '***Replaces scanned filename with inputbox filename
    PathLen = Len(Path)
    OldName = Mid(Path, 16, PathLen - 19)
    NewName = "S:\Materials Management\Purchase Orders\PO " & MyPONum & ".pdf"
EndAll:
    '***Set Objects***
    Set objectWMI = GetObject("winmgmts://.")
    Set objectProcesses = objectWMI.ExecQuery("SELECT * FROM Win32_Process  WHERE Name = 'Acrobat.exe'") '< Change if you need be ** Was AcroRd32.exe**
    '
    '
    'Code executes fine up to here but must Ctrl + Break at this line
    'and F8 step thru balance of code or it will not work
    '
    '
    MsgBox "Break Here"
    '***Terminate all Open PDFs***
    For Each objectProcess In objectProcesses
        Call objectProcess.Terminate
    Next
    '***Clean Up***
    Set objectProcesses = Nothing
    Set objectWMI = Nothing
    '***Renames scanned file and moves it to Materials Management folder***
    Name Path As NewName
    '***Resets current directory
    ChDir MyDir
End Sub
davidnt
2#
davidnt Reply to 2018-01-15 14:45:09Z

Thanks to all for your input. I'm not a programmer and as I said I used code that had been posted elsewhere on this site. It was a timing issue and this edit works.

    '***Terminate all Open PDFs***
    For Each objectProcess In objectProcesses
    objectProcess.Terminate
    Next
    '***Clean Up***
    Set objectProcesses = Nothing
    Set objectWMI = Nothing
    '***************
    Application.Wait (Now + TimeValue("00:00:02"))
    '***Renames scanned file and moves it to Materials Management folder***
    Name Path As NewName
    '***Resets current directory
    ChDir MyDir

    End Sub
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.359912 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO