| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA and Sendkeys Office 2003 under XP vs Office 2007 under V
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel VBA and Sendkeys Office 2003 under XP vs Office 2007 under V
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel VBA and Sendkeys Office 2003 under XP vs Office 2007 und
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" wrote: 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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel VBA and Sendkeys Office 2003 under XP vs Office 2007 und
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 "BAC" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel VBA and Sendkeys Office 2003 under XP vs Office 2007 underV
Try to use this Independent SendKeys that offers more control than VBA built-in one: http://cpap.com.br/orlando/SendKeysMore.asp?IdC=help On Apr 10, 12:06*pm, BAC wrote: 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 Office2007under 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 and2007, 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 Office2007and 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 toExcel * * 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| office 2007 and old office 2003 excel files | Excel Discussion (Misc queries) | |||
| Opening Office 2003 excel file in Office Excel 2007 | Excel Discussion (Misc queries) | |||
| Opening an Office 2007 Excel file in Office 2003 | Excel Discussion (Misc queries) | |||
| Problem with Interop.Excel after uninstalling Office 2007 and installing Office 2003 | Excel Programming | |||
Linear Mode
