VBA to check if user has pdf file open

[Rerock]

Hi,
The following code works great as long as you have entered a manual file path.

Please Login or Register  to view this content.

How can this be amended to work with "ThisWorkbook.Path", "ActiveWorkbook.Path", "Range("YOUR_NAMED_RANGE").Value", or some other type of variable?

[Leith Ross]

Hello Rerock,

This macro will search all open PDF documents. If the given document is found then it is closed.

The document name matching is not case sensitive. You may use either the full document name or part of the name to match an open document.

Copy this code into a VBA Module in your workbook.

Macor Code to Close an Opened PDF File

Please Login or Register  to view this content.

Example of Using the Macro

Please Login or Register  to view this content.

[Rerock]

Hi,
Wow; that's incredible!

How do I modify it for 64 Windows?

All of the Private declarations are red in text...

[Leith Ross]

Hello Rerock,

Here is the 64 bit version of the macro.

Please Login or Register  to view this content.

[Rerock]

Hi,
Thank you! I pasted the code in to a new Module, and tried to assign the macro to a button. For some reason, the macro doesn't appear in the list as an option to select. So, I tried to manually step through the macro (F8), and Windows just makes a dinging noise and does nothing; no debug error, nothing.

Any ideas?

[Leith Ross]

HEllo Rerock,

VBA will ding if Excel is in Edit Mode. Check that the cursor is not in the Formula Bar.

[Rerock]

Hi, Attached is an image of what I'm seeing. When I try to assign the macro to a button, I'm not seeing the macro available. When I click on a cell (B25 in the screenshot) and try to manually step through the macro, Excel just dings.
Untitled.jpg

Perhaps there is an Excel and/ or VBA setting I need to adjust in order for it to work?

[Leith Ross]

Hello Rerock,

Any Sub or Function that takes one or arguments will not show up the available Macros List. But, it is there. Just use the name in your code.

There seems to have been a problem with the picture uploading. It will not display due to an error. Try uploading it again.

You will need to View Code for the button and add a call to the macro manually.

For example:

Please Login or Register  to view this content.

[Rerock]

Hi,
The image upload is now irrelevant due to your last post... Not sure why it didn't work; it was just a jpg screenshot.

At any rate, I added the 'buttoon1_click' macro, and that seems to work just fine. However, I got a "Compile Error: Type Mismatch" error on this line of the code. "ret" was highlighted. Any idea what may be causing that error?

Please Login or Register  to view this content.

Thanks for all of your help; I really appreciate it!

[Leith Ross]

Hello Rerock,

Change the Dim statement for ret to this...

Please Login or Register  to view this content.

[Rerock]

HI,
It looks like that solved that issue, now there is a new one.

Please Login or Register  to view this content.

GetWindowText is highlighted, and I'm getting "Compile Error: Sub or Function not defined".

[Leith Ross]

Hello Rerock,

Okay, I need two ret variables. One for Long data types and a second for LongPtr data types.

Recised Macro Code

Please Login or Register  to view this content.

EDIT: The wrong API call was posted GetWindowNext should be GetWindowText. The change has been made.

[Rerock]

I really wish I knew how I could help you out with this; I feel bad.

Please Login or Register  to view this content.

"Compile Error: Type Mismatch" for FindWindow.

[Leith Ross]

Hello Rerock,

Did you see the correction I made to Post #12?

[Rerock]

Hi,
Yes, it looks like you edited the code at 2:22, and I copied the code a couple of minutes before I posted at 2:37. I presume that I copied/ tested the corrected code?

[Leith Ross]

Hello Rerock,

The code you copied should work. I don't know why you would get "Type Mismatch" for FindWindow. The return value is a Long Pointer and hWnd is dimensioned as a LongPtr data type.

[Rerock]

Yes, I am running 64 bit Office 2010 on 64 bit Win 7 Enterprise.

I have an XLSM inside of a folder on our network path. In the folder with the XLSM is a PDF named "Signed Agreement".

I wish I could help troubleshoot it, but I have no idea what this means:

The return value is a Long Pointer and hWnd is dimensioned as a LongPtr data type.

[Kenneth Hobson]

Leith's approach lets you have good control.

For what you posted initially, just poking a value into a cell and getting it worked fine. I put one more routine in it that I use for text files sometimes.

What else did you need?

Please Login or Register  to view this content.

[Rerock]

Hi,
Thanks for the reply, I appreciate you taking the time!

I tried the code you posted, and the msgbox always says "File is not open" regardless if the file is actually open or not.




I'm simply trying to get the macro to check and see if - ThisWorkbook.Path & "\Signed Agreement.pdf" - is currently open or not.

If it is open, it'd be cool if it could close it (no need to save), but a msgbox stating it's open works too.
If it's not open, then do nothing.

The code in my first post is what I found googling, and it works fine as long as the file path is embedded in the code; which won't work for this purpose.

[Kenneth Hobson]

It is left to the user to change the value of strPDFPath if you are not going to use it the way I built it based on your first post. IF you had checked the value of A1 or observed the way it was built in the code, you would have seen that. Obviously, if want to check that file, you can do it like this.

Please Login or Register  to view this content.