VBA is instructed to multiply the values from these two cells, and then append them as text to the rest of the text. This concludes the basic VBA training that you will need in order to access the RIT API. You are now able to write a simple set of instructions (a procedure) in VBA using a predesigned function (MsgBox). The ExportAsFixedFormat method is used to publish a workbook to either the PDF or XPS format. Expression.ExportAsFixedFormat (Type, FileName, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish, FixedFormatExtClassPtr) expression A variable that represents a Workbook object.
I have the following code, which looks at each cell in column A of my spreadsheet, searches for the text it finds there in the specified PDF and then extracts the page where it finds the text as a PDF, naming it with the value in the cell of the spreadsheet. The code works but is rather slow, I may need to search for as many as 200 words in a PDF which could be as long as 600 pages. Is there a way to make the code faster? Currently it loops through each cell searches through each page looping through each word until it finds the word in the cell.
Many thanks in advance.
Emma LavallinEmma Lavallin
3 Answers
Loops are definitely excellent for some things, but can tie down processing with these higher queries. Recently, a colleague and I were doing a similar task (not pdf-related though), and we had much success with using a range.find method instead of a loop executing instr on each cell.
Some points of interest: -To mimic the “loop cells” functionality when using the .find method, we ended our range statement with .cells, as seen below:
activesheet.usedrange.cells.find( )
Where the desired string goes within the ( ).
-The return value: “A Range object that represents the first cell where that information is found.”
Once the .find method returns a range, a subsequent subroutine can extract the page number and document name.
-If you need to find the nth instance of an occurrence, “You can use the FindNext andFindPrevious methods to repeat the search.” (Microsoft)
Microsoft overview of range.find:https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-find-method-excel
So with this approach, the user can use a loop based on a count of cells in your list to execute the .find method for each string.
Downside is (I assume) that this must be done on text within the excel application; also, I’ve not tested it to determine if the string has to inhabit the cell by itself (I don’t think this is a concern).
‘
Another suggestion that might be beneficial is to first bulk-rip all text from the .pdf with as little looping as possible (direct actions at the document object level). Then your find/return approach can be applied to the bulk text.
I did a similar activity when creating study notes from a professor’s PowerPoints; I grabbed all the text into a .txt file, then returned every sentence containing the instance of a list of strings.
‘
A few caveats: I admit that I have not executed parsing at the sheer size of your project, so my suggestions might not be advantageous in practice.
Also, I have not done much work parsing .pdf documents, as I try to opt for anything that is .txt/excel app first, and engage it instead.
Good luck in your endeavors; I hope I was able to at least provide food for thought!
James Andrew BushJames Andrew Bush
![Pdf vba api how to search for a word Pdf vba api how to search for a word](/uploads/1/2/4/8/124865959/345203646.png)
Sorry to post a quick, incomplete answer, but I think I can point you in a good direction.
Instead of making the system look up the two terms hundreds of billions of times, then make hundreds of billions of comparisons, put your search terms into an array, and the text of each page into a long string.Then it only has to do one look up and 200 comparisons per page.
This is still cumbersome because you have to open each page in a new document. If there is a good way to determine which page you're on purely by text (such as the page number at the bottom of page a, followed immediately by the header at the top of page b) then you might look at copying the entire text of the document into one string, then using the clues from the text to decide which page to extract once you find a match. That would be a lot faster I believe.
jlookupjlookup
Emma LavallinEmma Lavallin
Not the answer you're looking for? Browse other questions tagged excelvbaexcel-vbapdf or ask your own question.
Currently this is what I have.
I understand how to ExportAsFixedFormat PDF but what I need to know how to do is to access the Create PDF function under Acrobat (As show in the picture below) using VBA. If I do ExportAsFixedFormat the links get flattened. Acrobat 'Create PDF' would allow me to convert an Excel to PDF with hyperlinks included.
How would I do that?
I am using Excel 2016 and Adobe Pro DC
These are my adobe references
Michael Downey
Michael DowneyMichael Downey31533 gold badges99 silver badges3434 bronze badges
4 Answers
Acrobat Reference should work
Here is the guide from Adobe
Once added, you may use the following codeTip: It may lead you to correct coding -I'm not quite sure since I coded it 'blindly' because I don't have Acrobat in my PC-. Debug step by step to see what's doing.
Here is the guide from Adobe
Once added, you may use the following codeTip: It may lead you to correct coding -I'm not quite sure since I coded it 'blindly' because I don't have Acrobat in my PC-. Debug step by step to see what's doing.
Following pages may provide better assistance: Link1, Link2
SgdvaSgdva2,11911 gold badge1010 silver badges2222 bronze badges
Anders Tornblad13.7k77 gold badges4242 silver badges5959 bronze badges
David SyriacDavid Syriac
jellz77jellz77
You can publish any Excel Range as a PDF using ExportAsFixedFormat. There is no need to set a refernce to Acrobat.
user6432984