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" <(E-Mail Removed)> wrote in message
news:AC5F9AC3-0189-47F7-8556-(E-Mail Removed)...
> "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" <(E-Mail Removed)> wrote in message
> news:AC5F9AC3-0189-47F7-8556-(E-Mail Removed)...
> > "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" <(E-Mail Removed)> wrote in message
> > news:AC5F9AC3-0189-47F7-8556-(E-Mail Removed)...
> > > "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

I had a look this morning you might not need the sdk, have a look under
tools>references 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

"James Barrass" wrote:

> I had a look this morning you might not need the sdk, have a look under
> tools>references 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