Excel VBA and Sendkeys Office 2003 under XP vs Office 2007 under V

[BAC]

Every month I get 50 Adobe .PDF files with 8-15 pages of data. I need to
isolate and print only 1 of the pages. The following code works fine in
Office 2003 under XP, but in Office 2007 under Vista it stops with the cursor
flashing in the FileName box of the File Open dialog of the Adobe Reader.
This "freeze" occurs on the 1st pass through the "For each f in ff" , but the
code continues on to inserting the appropraitely named worksheet (without the
page image from Adobe) and actually stops with an "Invalid Procedure Call" on
the "AppActivate app_is" line the second time through (apparently it can't
activate the Adobe reader while it is frozen, awaiting a file name, from the
1st pass).

As I said this process works fine under XP/Office 2003. Has there been some
kind of change in the Sendkeys method between Office 2003 and 2007, or does
it have to do with XP vs Vista? And in either event, how do I get it running
as we will soon be moving to Vista with Office 2007 and I really don't want
to start doing this manually.

<<<<<<<<Code Begins>>>>>>>>>
Sub fn_PrintPDF()
Dim fs As Object, fp As Object, ff As Object
Dim app_is As Long, x As Long
Dim fname As String, dPath As String

Set fs = Nothing
Set fp = Nothing
Set ff = Nothing

'Enter the path to this month's .pdf files on Sheet1, Cells C3
dir_Name = Sheets("Sheet1").Cells(3, "C").Value

If Len(dir_Name) <= 1 Then
x = MsgBox("Please enter a valid directory name for the " & Chr(10) &
Chr(13) _
& "location of the files to be printed on Sheet1", vbOKOnly)
Sheets("sheet1").Activate
Cells(3, "C").Select
Exit Sub
End If

'Set path to pdf files
dPath = "\\crpatlfnp03\Accounting\" & dir_Name & "\"

Set fs = CreateObject("Scripting.FileSystemObject")
Set fp = fs.GetFolder(dPath)
Set ff = fp.Files

'Initiate Adobe Reader
app_is = Shell("C:\Program Files\Adobe\Acrobat 7.0\Reader\AcroRd32.exe",
vbNormalFocus)

For Each f In ff

'Validate Files are what we want:
If UCase(Right(f.Name, 4)) = ".PDF" Then
fname = f.Name

'Following AppActivate is highlighted line when Invalid Procedure Call error
occurs
AppActivate app_is
'Open file
Application.SendKeys "%FO" & dPath & fname & "{ENTER}", True
'Find 1st Occurence (in Table of Contents)
Application.SendKeys "^FSTATEMENT OF TRANSACTIONS{ENTER}", True
Application.SendKeys "{ESC}", True
Application.SendKeys "{ENTER}", True
' Find 2nd Occurrence in Title of desired page
Application.SendKeys "^FSTATEMENT OF TRANSACTIONS{ENTER}", True
Application.SendKeys "{ESC}", True
'Copy page to the Clipboard
Application.SendKeys "%EB", True

'Add new worksheet
ActiveWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
Range("A6").Select
'Paste Adobe Page to Excel
ActiveSheet.Paste
Range("A3").Select
'Put file name on sheet
ActiveCell.FormulaR1C1 = fname
'Rename sheet
ActiveSheet.Name = fname
Range("A4").Select

'Close file in Adobe Reader
AppActivate app_is
Application.SendKeys ("%FC"), True

End If

'Get next .pdf
Next f

'Exit Adobe Reader
AppActivate app_is
Application.SendKeys ("%FX"), True

'Save this month's file to this month's directory
ActiveWorkbook.SaveAs Filename:=dPath & "PDFExcelRead.xls"

Set fs = Nothing
Set fp = Nothing
Set ff = Nothing

End Sub
<<<<<<<<Code Ends>>>>>>>

Thanx a bunch
BAC

 

[RB Smissaert]

It is broken by Vista.
Here is some code that will get you a replacement SendKeys that
will work under Vista:
http://vb.mvps.org/samples/project.asp?id=SendInput

RBS

 

[BAC]

That link contains the following text:
This sample provides a drop-in replacement for the standard SendKeys
statement, and should work just fine in all the environments (VB5, VB6 IDE,
VBA other than Office 2007) where this broke

I'm confused about the "VBA other than Office 2007" part

I imported the code .bas file into my Office 2007 Project and I'm still
having some trouble figuring it out, but does the above mean I'm wasting my
time in 2007 and if so what's the alternative?

TIA
BAC

 

[RB Smissaert]

Not sure and I will see if I can try it with Excel 2007.
Otherwise ask in public.vb.general.discussion and I am sure the author,
Karl Peterson, will answer.

RBS

 

[2007FanFoe]

Try to use this Independent SendKeys that offers more control than VBA
built-in one:
http://cpap.com.br/orlando/SendKeysMore.asp?IdC=help