close pdf file before closing excel
 

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

close pdf file before closing excel
 

"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

close pdf file before closing excel
 

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

close pdf file before closing excel
 

"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

close pdf file before closing excel
 

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

close pdf file before closing excel
 

"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

close pdf file before closing excel
 

I had a look this morning you might not need the sdk, have a look under
toolsreferences and see if you have the adobe acrobat type library.

All the references and code snippets i found for this were for Visual Studio

Set PDFApp = CreateObject("AcroExch.App")
created an error "ActiveX Componant can't create object"

"urkec" wrote:

"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

close pdf file before closing excel
 

"James Barrass" wrote:

I had a look this morning you might not need the sdk, have a look under
toolsreferences and see if you have the adobe acrobat type library.

All the references and code snippets i found for this were for Visual Studio

Set PDFApp = CreateObject("AcroExch.App")
created an error "ActiveX Componant can't create object"

"urkec" wrote:

"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

Problem is, I don't have the full version of Acrobat. I loaded acrord32.dll
into the object browser and tried creating Acrobat.AcroApp but no luck. I
also looked under HKEY_CLASSES_ROOT in the registry and there are several
AcroExch keys but not AcroExch.App. And, as you mention, all samples I could
find use that. All I could find was the information that Acrobat Reader
object model is different than Acrobat, nothing more. Too bad, it would be
nice to be able to automate working with pdf files.

--
urkec