#31
|
||||
|
||||
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.) |
#32
|
|||
|
|||
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? |
#33
|
||||
|
||||
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. |
#34
|
|||
|
|||
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! |
#35
|
||||
|
||||
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.) |
#36
|
|||
|
|||
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." |
#37
|
||||
|
||||
The simplest way of combining the three modules might be to use another one to call each of the others. For example:
Code:
Sub Refresh() Call DownloadFile Call Convert_PDF_To_Text_File Call UpdatePrices End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#38
|
|||
|
|||
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. |
#39
|
||||
|
||||
Try:
Code:
Option Explicit Dim strPDF As String, strTXT As String Sub Refresh() strPDF = "ThisWorkbook.Path & "\" & "ProductCatalog.pdf" strTXT = "ThisWorkbook.Path & "\" & "ProductCatalog.txt" If Dir(strPDF) <> "" Then Kill strPDF If Dir(strTXT) <> "" Then Kill strTXT Call DownloadFile Call Convert_PDF_To_Text_File Call UpdatePrices End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#40
|
|||
|
|||
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? |
#41
|
||||
|
||||
Quote:
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#42
|
|||
|
|||
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! |
#43
|
|||
|
|||
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? |
#44
|
||||
|
||||
Quote:
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#45
|
|||
|
|||
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. |
Tags |
adobe, conversion, pdf |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
[Excel 2007] Building Power Point Slides from data in an Excel Table | bremen22 | Excel Programming | 1 | 08-07-2013 11:01 AM |
Paste special an Excel range into Outlook as an Excel Worksheet | charlesh3 | Excel Programming | 3 | 02-04-2013 04:33 PM |
Excel 2011 can't open old Excel 98 or Excel X files | FLJohnson | Excel | 8 | 05-09-2012 11:26 PM |
Excel 2007 custom ribbon not showing in Excel 2010 | Paulzak | Excel | 2 | 02-17-2012 06:35 PM |
saving data in excel 2010 from excel 2003 | johnkcalg | Excel | 1 | 02-06-2012 07:33 PM |