Toggle between Excel and PDF file

[plans]

Hello,
I have a text selected in a PDF file; at the same time I have an open Excel file.
I want a macro which would Copy the selected text from the PDF and Paste it in my active cell in Excel.
Is this possible with an excel macro?
Thanks

[Kevin#]

Before trying this - see note below the code
Code assumes that you have
1) selected relevant text and hit ctrl + C in your pdf program, and
2) re-activated your workbook

Please Login or Register  to view this content.

NOTE
(from http://excelhelphq.com/)
You can access and retrieve the data in the clipboard into a variable in Excel VBA, using the Microsoft Forms library. In addition to using the above code , you must follow the these steps and activate Microsoft Forms 2.0 Object Library for the code to work. You only need to do it once as long as this macro stays in the same file.

On the Macro editor menu bar on the top, click on “Tools”
In the menu, click on “References”
Click the “Microsoft Forms 2.0 Object Library”
Press OK
If you don’t follow these steps, you will get a “Compile error: User-defined type not defined” error… Lots of people have been stumped on this part.

(you are my first post on this site!!)

[plans]

Kevin#,
Thanks for the response; it works however, if I have to manually reactivate my workbook, than I just could do Ctrl + Paste, instead of running the macro.
I am wondering if, after doing the Ctrl + Copy on the selected PDF text, VBA could automatically reactivate the workbook and than do the "paste".
The way I picture this is:
- While I am in the excel sheet, I run he macro from there.
- VBA would jump into the PDF window where my text is already selected.
- VBA would do the Ctrl + C
- VBA jump back into the Excel and paste value.
I know there are automation programs which could do this but I am wondering if this is achievable without that.
Thanks

[Kevin#]

Simplest solution would appear to be:
- select and copy PDF text to clipboard
- open workbook
- save clipboard text to a a special variable in VBA
- run any other other sub-routines
- paste variable value (ie PDF clipboard text) to relevant cell within relevant sub-routine
- continue ...

How does that sound?

[plans]

Meantime, I have tried an other approach..
using from your web site the SendKey idea;
this could do the work, but interestingly, if I do the Alt+Tab manually, than the approach seems to work but within the VBA code, the SendKeys "%{TAB}" does nothing.
Here is my entire code, I can't figure out why is not doing the job:

Please Login or Register  to view this content.

[Kevin#]

Looks like it could also work provided you can ensure you tab to the correct place.
Will test your code tomorrow.
How about activating the workbook window instead of tabbing back?

Please Login or Register  to view this content.

[plans]

The "workbook activate" works fine; the only problem is the

Please Login or Register  to view this content.

Thanks!

[Kevin#]

Have made good progress.
Everything works on amended code EXCEPT the copying to the clipboard. (I will investigate next)
Before running this, close all windows except PDF and Excel windows (and only have the one Excel file open)

The code would be more robust (and allow other windows to remain open) if we knew the name of the PDF window - could then replace Application.WindowState = xlMinimized with Windows("YourPDFFile.xlsm").Activate
Will the PDF name be the same each time?

Please Login or Register  to view this content.

[Kevin#]

Will you be running several procedures or just a single one (in Excel) after selecting your PDF text?
If it is only a single procedure, then this should do the trick.
(Otherwise must declare strPDF as a public variable to enable VBA to remember its value as the other procedures are run)

Test this without any amendments first and then insert the rest of your code and test again
1st: open pdf window, select text AND copy to clipboard and
2nd: toggle to excel window and execute the macro

Please Login or Register  to view this content.

[Kevin#]

I have (briefly) tested another approach which involves opening a pdf file from within the macro. And this has borne fruit - doing it this way has enabled sendkeys ^a, ^c & ^v - the contents of the whole pdf was successfully copied and pasted automatically into the worksheet.

I now await your comments on posts #08 and #09 before I do anything else.
Also are you using Adobe Reader to open the pdf file?
And will the pdf FILENAME and/or FILEPATH vary?

thanks

[Kevin#]

PDF file is opened and ALL text copied to a temporary worksheet (sub ONE)
User can select multiple ranges of text for pasting to chosen cell (sub TWO)
VBA runs so fast that it gets ahead of itself – hence line Application.Wait (Now + TimeValue("0:00:01"))

Text selection is made in Excel rather than pdf. (amend to use Word if easier).
Amend code for your pdf application and pdf file name. (sub ONE)
Amend chosen cell/sheet (sub ONE)

Please Login or Register  to view this content.

[Kevin#]

Have just noticed this solution on MrExcel's site.
This appears to do exactly what you want, in the way you want to do it. But it requires Adobe Acrobat Professional rather than Acrobat Reader.
http://www.mrexcel.com/forum/excel-q...ions-code.html

[plans]

Kevin#,
Thank for your help.
I was able to make it work; it was helpful mainly the ("^c"); my mistake was that I had the parenthesis within the " sign;
Here is my code:
[CODE][CODE]Sub PasteFromPDF()
Dim CurrentWS As String
Dim keys As String
Dim objData As New MSForms.DataObject
Dim strText As String

'you must add the reference: Tools/References/ “Microsoft Forms 2.0 Object Library”

AppActivate "Microsoft Excel"
CurrentWS = ActiveSheet.name
AppActivate "Adobe Reader"
For i = 1 To 100000000
'delay
Next i
keys = ("^c")
Set wsh = CreateObject("WScript.Shell")
wsh.SendKeys keys, True
Worksheets(CurrentWS).Activate
For i = 1 To 100000000
'delay
Next i
objData.GetFromClipboard
strText = objData.GetText()
ActiveCell.value = objData.GetText()
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
End Sub[CODE][CODE]


Thanks again!