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.

To output the discount value in column O -
After:
insert:
and change:
to:
As for the comparisons, you could simply do a sort of the data by brand & retail price, without needing to create any new sheets.

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