I have an excel model from where users can open a user guide in pdf format.
When a user closes the excel model, I would like excel to check whether the
pdf file is open, and if it is, then excel should close the pdf file first.

Can anyone help me with this?

Thanks

"Ken" wrote:

I have an excel model from where users can open a user guide in pdf format.
When a user closes the excel model, I would like excel to check whether the
pdf file is open, and if it is, then excel should close the pdf file first.

Can anyone help me with this?

Thanks

I don't know how you open the pdf file from Excel.For test I used Shell
function:

Shell "cmd /c C:\docs\MyGuide.pdf"

The pdf is opened by Acrobat Reader 7 and the created process contains the
path to the file (in CommandLine property). Then I can use this code to check
if the pdf file is open and close it:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Set objSWBemServices = GetObject _
("WinMgmts:Root\Cimv2")

Set colPDFs = objSWBemServices.ExecQuery _
("Select * From Win32_Process " & _
"Where Name = 'acrord32.exe' " & _
"And CommandLine Like '%C:\\Docs\\MyGuide.pdf%'")

For Each objPDF In colPDFs
objPDF.Terminate
Next

End Sub

This code first connects to WMI service on local computer, checks for
processes named Acrord32.exe that have the path to the pdf file in their
command line (C:\docs\myguide.pdf) and terminates such processes. I didn't
test this much, but it seems to work. (this will not work in Windows 2000 and
earlier, you would need to use Instr function instead of Like operator to
check for the file path)

I hope this helps.


--
urkec

I tested this same approach earlier and found that on my system it shut down
all instances of acrobat reader, even though I specified the command line as
you did. I also suspect that it might not work if a user is running the
full Adobe program as opposed to opening pdf files with acrobat reader
(something I can't check on my system).

Steve


"urkec" wrote in message
...
"Ken" wrote:

I have an excel model from where users can open a user guide in pdf
format.
When a user closes the excel model, I would like excel to check whether
the
pdf file is open, and if it is, then excel should close the pdf file
first.

Can anyone help me with this?

Thanks

I don't know how you open the pdf file from Excel.For test I used Shell
function:

Shell "cmd /c C:\docs\MyGuide.pdf"

The pdf is opened by Acrobat Reader 7 and the created process contains the
path to the file (in CommandLine property). Then I can use this code to
check
if the pdf file is open and close it:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Set objSWBemServices = GetObject _
("WinMgmts:Root\Cimv2")

Set colPDFs = objSWBemServices.ExecQuery _
("Select * From Win32_Process " & _
"Where Name = 'acrord32.exe' " & _
"And CommandLine Like '%C:\\Docs\\MyGuide.pdf%'")

For Each objPDF In colPDFs
objPDF.Terminate
Next

End Sub

This code first connects to WMI service on local computer, checks for
processes named Acrord32.exe that have the path to the pdf file in their
command line (C:\docs\myguide.pdf) and terminates such processes. I didn't
test this much, but it seems to work. (this will not work in Windows 2000
and
earlier, you would need to use Instr function instead of Like operator to
check for the file path)

I hope this helps.


--
urkec

"Steve Yandl" wrote:

I tested this same approach earlier and found that on my system it shut down
all instances of acrobat reader, even though I specified the command line as
you did. I also suspect that it might not work if a user is running the
full Adobe program as opposed to opening pdf files with acrobat reader
(something I can't check on my system).

Steve


"urkec" wrote in message
...
"Ken" wrote:

I have an excel model from where users can open a user guide in pdf
format.
When a user closes the excel model, I would like excel to check whether
the
pdf file is open, and if it is, then excel should close the pdf file
first.


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Set objSWBemServices = GetObject _
("WinMgmts:Root\Cimv2")

Set colPDFs = objSWBemServices.ExecQuery _
("Select * From Win32_Process " & _
"Where Name = 'acrord32.exe' " & _
"And CommandLine Like '%C:\\Docs\\MyGuide.pdf%'")

For Each objPDF In colPDFs
objPDF.Terminate
Next

End Sub



You are right. This doesn't work with more than one PDF document open
because there is only one acrord32.exe process. Command line contains the
path to the first document open, so it is also possible not to close any
documents. Sorry, I should have tested this more carefully.

--
urkec

The Adobe SDK is free and Contains an AVDOC object representing a pdf document
This has IsValid and Close which might be helpful in this case,
Sorry I haven't tested this
"urkec" wrote:

"Steve Yandl" wrote:

I tested this same approach earlier and found that on my system it shut down
all instances of acrobat reader, even though I specified the command line as
you did. I also suspect that it might not work if a user is running the
full Adobe program as opposed to opening pdf files with acrobat reader
(something I can't check on my system).

Steve


"urkec" wrote in message
...
"Ken" wrote:

I have an excel model from where users can open a user guide in pdf
format.
When a user closes the excel model, I would like excel to check whether
the
pdf file is open, and if it is, then excel should close the pdf file
first.


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Set objSWBemServices = GetObject _
("WinMgmts:Root\Cimv2")

Set colPDFs = objSWBemServices.ExecQuery _
("Select * From Win32_Process " & _
"Where Name = 'acrord32.exe' " & _
"And CommandLine Like '%C:\\Docs\\MyGuide.pdf%'")

For Each objPDF In colPDFs
objPDF.Terminate
Next

End Sub



You are right. This doesn't work with more than one PDF document open
because there is only one acrord32.exe process. Command line contains the
path to the first document open, so it is also possible not to close any
documents. Sorry, I should have tested this more carefully.

--
urkec

"James Barrass" wrote:

The Adobe SDK is free and Contains an AVDOC object representing a pdf document
This has IsValid and Close which might be helpful in this case,
Sorry I haven't tested this

That would be the best way to do this, but I haven't be able to make it
work. I found some samples, but all for the full version of Acrobat, none of
them worked with Acrobat Reader, it seems they have different object model.
If you have any samples or links that would be great. I will also need to
check the Adobe site for the SDK.

--
urkec