Paul-

Thank you again for your response to this post. I just did a test run with the new changes it the price update part worked! I am really excited to have this part working, but there are still a couple follow up pieces I could use your help with if possible.

The part of the script that is suppose to highlight the retail price field if any changes are made did not work. I am guessing this is because all of those fields are cleared out prior to them updating so there is no way to actually tell if its a new price based on the old information. If there a way we can fix this to still display in "red" if there is a sales price update?

Let me know if there is anything I can do to help out with recoding the script so that it does not wipe out the "SLO" fields. I am guessing you could change the ".Range("K6:K" & LRow).ClearContents" to an IF, THEN statement to ignore the fields that contain "SLO" in the cell?



SLO prices is price point given on certain products by the reps that is cheaper than the retail shelf pricing. This is not available to the public so it has to be order and priced in a different way. Most establishments have a limited number of these items to it is something they can look up manually.

It would be amazing to have the script down download the PDF file and Adobe Acrobat conversion as well! That is definitely the ideal solution! The PDF is update once a month (on the 6th I believe) and it is always updated at the same url so this should be static for the most part - http://www.lcbapps.lcb.state.pa.us/w...uctCatalog.PDF

On a side note, let me know if there is anything I can do to return the favor for both of you helping me out with this project!

Umm, that's probably because there isn't any. You asked for code to identify if the new price is a sales price, and that's what I've provided.

As I indicated in my last post, anything that involves working with the old prices would entail significant re-coding. That's because all those data are cleared at the outset. As I'm travelling at the moment, re-coding's not something I could contemplate for a few weeks.

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

Paul-

Thank you again for your help with this project. As long as their is a way to identify that something is a sales price then that will work fine. I have tried running through a couple different scenarios with this scrip and it never seems to update the sales price in Red. Maybe the setup is incorrect on my end?

The current PDF I have downloaded has Bacardi Rock Coconut as a sale price - "3235 BACARDI ROCK COCONUT RUM 750 ML 12/3/12 1/6/13 $14.99 $16.99". I was trying to figure out the line of code that is to change that value to red if it pulls a sales price and I was not able to understand it completely. Is it saying that if the price follows a date format (01/01/01) then its suppose to highlight it in red? I believe its something to do with this line:

If Trim(Split(StrData, "$")(UBound(Split(StrData, "$")))) <> Trim(Split(StrData, "$")(1)) Then .Font.ColorIndex = vbRed

I also looked back over the situation with the SLO pricing and nothing needs to be recoded for that. There are no issues with the "retail price" fields being blank for the products that are SLO. The "LID price" field will retain the SLO price and that is the important one.

I did some research and figured out how to automatically download the file from the website using the attached code. The only piece I am unsure out in that code is how to specify to save the file in the location the script is run from. The current script saves it in the "My Documents" directory.

strFileToSave = "ProductCatalog.pdf"

I am currently researching how to automate the conversion to a text file process, but I have not had any luck just yet. I will let you know if I come up with anything, but I could def use some help putting everything together regardless.

One of the follow up items I have been thinking about is the requirement for having to have the full version of Adobe Professional in order for this script to work. Its around $140 for that piece of software right now so I am just wondering if there is another way to convert the file without it. Definitely not the end of the world if that software is required as I already have access to a copy of it. I will be more than happy with this program even if that is a requirement.

I know you are traveling so no hurry on getting back to me. Have a safe trip and I hope to hear from you soon!


Bob-

Thank you for your help with all of this as well. As you can see I am still having some issues understanding some of the code. I am continuing to do some research on my own, but please let me know if you can help shed any light on the situation. Thanks!

-Shane

Attached Files

DownloadFile.txt (1.7 KB, 9 views)

As I said in post #19 (https://www.msofficeforums.com/excel...html#post56910), you can download Adobe Acrobat Pro 8 for free - and that can be automated. Just click on the link in that post.

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