So this logic gets you the download, Shane, and puts it in ProductCatalog.pdf if I'm not mistaken, but you're trying to figure out how to extract the relevant data from the download and put it into one or more Excel worksheets?

Well, I certainly wouldn't try to write a VBA program to do the conversion itself; the inside of a .pdf is just garbage. (Not literally, of course.) So you're talking about getting VBA to fire up a conversion program, then read in the translation.

To do that you need a program that'll do the conversion. Do you have one in mind? I remember you talked about this before, but I don't remember whether you decided anything.

Bob-

Thank you for your reply to this message. You are correct that the code I posted previously will download the file and save it in the root directory as "ProductCatalog.pdf". As Paul and I discussed before, I was planning on using Adobe Acrobat 8 Profession to do the conversion of the "ProductCatalog.pdf" to "ProductCatalog.txt" file. The VBA code that Paul put together for me pulls the needed data out of the "ProductCatalog.txt" file to update my Inventory/Cost Program.

I found a couple sources online that have code to do this conversion automatically, but nothing I can get to work correctly for me. Paul said previously that it is usually harder to diagnose issues with someone else's code over writing something fresh which is why I haven't referred to the code I found yet.

Here are the steps that I am looking to have completed:

1.) code to automatically download the file - done
2.) code to automatically convert the downloaded file from PDF to TXT - needed
3.) code to automatically update the prices from the converted TXT file to my Inventory/Cost Program - completed by Paul
4.) put all 3 parts above into a single working script

Please let me know what else I can do to help out with this. I am continuing to work on step 2 myself, but haven't had any luck yet. Thank again!

-Shane

Paul's right that it's harder to figure out someone else's thinking than to look through your own code; you know, after all, how you think, and you already know your own goal. But I don't see any way around it; show me what you've been trying, and I'll see what I can make of it. We're talking about only part 2, at this point.

I expect the general idea must be something like this: 1) Run Adobe 8 Professional. 2) In Adobe, read the .pdf file. 3) Use Adobe's Save-As feature to save the file in another format, .txt or .csv or whatever works best. 4) Quit Adobe. That's what a human would do; but the question is how to get VBA to tell Adobe to do all that? If there's an Adobe COM object, and we can find it—and the documentation for it—then it should be easy. If not, well, there are workarounds that might work. But I'm much rather find an Adobe object we can invoke from inside VBA.

So if you and Paul have found the Adobe object documentation, point me to that too, please, in addition to posting the workbook with your VBA code in it.

Paul-

Thank you for your response to this. I am trying to runt he script in the attached document. It successfully opens up Adobe Acrobat 8 Profession, but then stops with the following error:

Run-time error '1001':

UnsupportedValueError: Value is unsupported. ===> Parameter cConvID.


When I click "Debug" to show me what statement it is referring to, it points to this line:

"jsObj.SaveAs ThisWorkbook.Path & "\" & "ProductCatalog.txt", "com.adobe.Acrobat.plain-text""


There are a handful of posts in regards to the VBA code to automatically convert PDF to TXT, but I have not been able to find one that works for me. Here are a few other ones that I referenced:

http://www.reddit.com/r/excel/commen...rt_pdf_to_txt/

http://www.myengineeringworld.net/20...iles-into.html

http://forums.adobe.com/message/5429451

http://social.msdn.microsoft.com/For...t?forum=isvvba

Hopefully it is an easy fix to get the part of the script working and then to put all three steps together. Any light you could shed on this situation would be more than appreciated. Thanks!

Attached Files

ConvertFile.txt (637 Bytes, 13 views)

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.