Shane, most of Adobe's documentation seems to be written with the assumption that it'll be used by C and Java programmers. But I did find the value "com.adobe.acrobat.plain-text", after some googling around, so I should think you're on the right track.

Any chance the problem is simply that the value is case-sensitive? You have the "acrobat" part of it capitalized.



(I agree putting the three parts together should be easy....once you have each part working.)

Paul-

I figured out what was wrong with the conversion script. It was trying to save it as plain text instead of accessible text. I fixed it by changing the code to read:

jsObj.SaveAs ThisWorkbook.Path & "\" & "ProductCatalog.txt", "com.adobe.acrobat.accesstext"

All three scripts now successfully run if you run them individually in order. The last step is to put all three of these scripts together into a single macro. There is a brief delay when downloading the file and also when converting the script since its a 800 page PDF file so I am not sure how this needs to be reflected when combining all three. Is this something you can help with?

Just a side note: I took "com.adobe.acrobat.access-text" to mean text that is in some way more useful to MS Access rather than is the regular text. I didn't have any idea what that was, and anyway you were trying to import it into Excel not Access, so I didn't mention it.

But if you happen to know that "...access-text" really does mean "accessible", let me know where you saw the definition. I was just assuming, but I'd much rather know. Someday it may matter.

I don't mean to keep rushing in ahead of Paul; I just see an issue and start talking. But if you'd rather Paul worked with you on melding the three pieces together into the final product, maybe I should send him a private message; if he's like me, maybe he sees someone else's name on the answers and assumes it's taken care of, without realizing you're asking for his help specifically.

Bob-

Thank you for your response to this message. If you do a File, Save As in Adobe Acrobat, the two text options are "Text (Accessible)(*.txt)" and "Text (Plain)(*.txt)"

I am willing to work with either one of you on putting the files together, but it does bring up a good point that finalizing this part of the project might be best to be handled in a private message or via email so that it is not readily available to the public. Let me know what works best for either of you. Thanks!

Paul, do you have a preference?

(You're right, Shane; it must mean "accessible". I just don't know how that differs from "plain". But if one worked for you and the other didn't, I'll file that away and maybe it'll save me trouble someday down the road. Thanks.)

From Adobe's site:

"You can also export a PDF to plain text or accessible text. Accessible text follows the reading order preference selected in the Reading preferences, and includes comments and form fields in its output. Accessible text also includes some formatting, such as line breaks. Any alternate text in the document tags is used in place of images and figures. Plain text follows the structure order of text in the document and ignores all artifacts and figure elements in the conversion. Hard hyphens are preserved, and soft hyphens are removed."

The simplest way of combining the three modules might be to use another one to call each of the others. For example:
If there are any timing issues, such that the 'Convert_PDF_To_Text_File' sub runs before the DownloadFile sub finishes its processing, for example, it shouldn't be too difficult to insert some code to loop until the downloaded file is present; the same applies with ensuring the 'ProductCatalog.txt' text is present before allowing the UpdatePrices sub to run. This raises another issue: you should add code to delete (kill) or rename the old copies at the start of each run, so they don't confuse issues.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hey guys. Many apologies for the delay on getting back to you. Paul, the script you put together to call all three files worked great! Thank you for making that so simple.

I am trying to add in code to delete the old file prior to downloading the new one, but I am having some issue getting it to run with that script. Here is the code I am trying to add in:

Dim aFile As String
aFile = "ThisWorkbook.Path & "\" & "ProductCatalog.pdf"
If Len(Dir$(aFile)) > 0 Then
Kill aFile
End If

I am trying to figure out to to incorporate it into the attached file. If there is an easier way to do this, I am all ears. Thanks again guys.

Try:
Having defined strPDF and strTXT this way, you don't need to redefine those references later on - you can simply reference the existing ones.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Paul-

Thank you for getting back to me so quickly. I have actually run into two small problems.

When trying to run the code you specified above, I get a Compile Error: Syntax Error on this line- "
strPDF = "ThisWorkbook.Path & "\" & "ProductCatalog.pdf"".

The part of the scrip that deletes the two old files might actually not be needed because the couple time I tested the script, it automatically deleted the files already.

The second issue I have come across is when running the Convert_PDF_To_Text_File script in a production
environment. It runs as planned in my test environment, but when I try to run it on the full version of
my Inventory/Cost sheet it gives me the follow error: Compile error: User-defined type not defined and it points to this line "Dim AcroXApp As Acrobat.Acroapp"

I tried to recreate this scrip a number of times and made sure that it exactly matched the one I am running in my test environment which it does. Any thoughts on why this would run in one location and not the other?

Did you define strPDF the way I said to (i.e. with the Dim statement outside the code module)?
Is Acrobat Pro installed in the production environment? And, if you installed the code there, rather than installing the workbook containing it, did you set a reference to Acrobat, via Tools|References in the VBE?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Paul-

You were right about there needing to be a reference to Acrobat in that specific file. I corrected that and now the program runs correctly.

I did define the strPDF with the Dim statement outside the code module, but I still got that error. I am not worried about that part though because all of the testing I have done shows that both files get overwritten automatically.

Thank both of you a ton for helping me to get this file working properly. I really appreciate all of the time and effort you put into it and as to keep saying, please let me know if there is anything I can do to return the favor!

I am not sure if you guys are up for another challenge with this project or not, but I have some other enhancements I am working on adding.

1.) It would be beneficial to see what the actual discount is ($1, $3, etc.) for the items that have a sale price. I was thinking about adding another column to the right of the "Retail" field called "Discount" where it calculates the dollar amount of the actual discount. Is this something that could be simply added to the "UpdatePrices" script we created?

2.) I am also thinking about adding another tab to this spreadsheet called "price comparison" that updates pricing when this scripts runs. This will allow the comparison (sorted lowest price to highest price) all spirit types based on what brand they are. Wine is probably too much to include in this but the majority of other categories would be beneficial to see the low to high cost. Here are the most important ones:

Vodka
Rum
Whiskey
Bourbon
Cordials
Curacao - Triple Sec
Gin
Schnapps
Tequila

Would this be possible to be done by brand or would we have to input each LCB# a head of time so the script knows what to pull? Maybe it would be best if this was a separate script from the "UpdatePrices" script and then the "Refresh" script could call this one as well?

Isn't that just the difference between the retail & LID prices? If so, you can do that calculation with a simple formula (eg =[@Retail]-[@[LID Cost]]) without any code.

That would be a lot of extra work, aside from which I'm not sure whether you mean, for example, 'Jim Beam' as a brand (which it is) vs 'Bourbon' as a brand (which it isn't, even though your worksheet lists it as such).

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

1.) The script we put together previously pulls the sales price (if there is one) from the downloaded pdf. If that is the price it pulls, it highlights it in red. If there is not a sales price, it pulls the regular retail price.

It would be beneficial to know the difference in the sales price from the retail price when there is an actual sale. The difference between the LID price and the retail price is a simple 10% markdown.

2.) I am trying to think of the simplest way to do this. It would be ideal to compare everything, but I agree that it would take a lot of work. I could just manually create another tab and then input the top 10 types of each brand and the have the script do a sort based on price.


I would still have to learn the details of the script you wrote previously in order to recreate it for a new tab with new columns.