#16
|
||||
|
||||
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#17
|
||||
|
||||
I'll be away for a few days, so I thought I'd post the following Excel macro as a potential solution:
Code:
Sub UpdatePrices() Application.ScreenUpdating = False Dim StrList As String, DataSet As String, StrData As String, StrItem As String Dim i As Long, j As Long, LRow As Long StrList = "," With ThisWorkbook DataSet = .Path & "\ProductCatalog.txt" If Dir(DataSet) <> "" Then With .Sheets("Master") LRow = .Range("A" & .Rows.Count).End(xlUp).Row .Range("K6:K" & LRow).ClearContents For i = 6 To LRow If Trim(.Range("C" & i).Value) <> "" Then StrList = StrList & .Range("C" & i).Value & "," Next i = UBound(Split(StrList, ",")) - 1 Open DataSet For Input As #1 Do Until EOF(1) Line Input #1, StrData StrItem = Split(StrData, " ")(0) If InStr(StrList, "," & StrItem & ",") <> 0 Then j = UBound(Split(Split(StrList, StrItem)(0), ",")) + 5 .Range("K" & j).Value = Trim(Split(StrData, "$")(1)) i = i - 1 End If If i = 0 Then Exit Do Loop Close #1 End With End If End With If i > 0 Then MsgBox "Done. However, " & i & " item(s) could not be matched.", vbExclamation Else MsgBox "Done.", vbInformation End If Application.ScreenUpdating = True End Sub As coded, the macro: • assumes the source file is a PDF saved as text in the same folder as the Excel workbook, with the name 'ProductCatalog.txt'. • updates the retail prices on the 'Master' sheet. For efficiency reasons, no comparison between new & old prices is done - they're simply updated regardless. • all existing retail prices are cleared before updating. • uses any 'sale' prices that might be on offer. If you want to update a different column, change the column K references to suit. If you want to keep values that don't get updated, delete/comment-out the line: [code].Range("K6:K" & LRow).ClearContents['code] If you want to use regular prices instead of sale prices, change: Code:
.Range("K" & j).Value = Trim(Split(StrData, "$")(1)) Code:
.Range("K" & j).Value = Trim(Split(StrData, "$")(UBound(Split(StrData, "$"))))
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#18
|
|||
|
|||
Paul, my apologies for the extended delay. I got caught up playing catch up after the holidays. I hope you had a good holiday and got to enjoy a vacation!
You are correct in saying that the LCB# is the only reliable data to match. The way you have your most recent macro setup should work from what I am trying to accomplishment. I am mainly just concerned with the actual price of the product at that time. If there is a sale it should take that price. If not, i should check to make sure the retail price is in fact accurate. As a small follow up, would it be possible to highlight if there is actually a sales price? I believe this is a simple addition to the script though. So I have the ProductCatalog.txt file located in the same folder as the .xlsm file I am running the macro from and I get the following error: "Run-time error '9': Subscript out of range" Clicking on Debug points to the line: "StrItem = Split(StrData, " ")(0)" In regard to your Note, the items marked as "SLO" where there LCB# is located is special pricing cases that will have to be ignored as there pricing comes directly from the liquor reps. These will just have to be updated manually. Is there a way that is "SLO" is the LCB# just to skip over that field instead of blanking out the retail price? As always, thank you very much for your efforts with this. I think we are getting really close to what I am looking for! |
#19
|
||||
|
||||
Quote:
Code:
.Range("K6:K" & LRow).ClearContents Code:
With .Range("K6:K" & LRow) .ClearContents .Font.ColorIndex = xlColorIndexAutomatic End With Code:
.Range("K" & j).Value = Trim(Split(StrData, "$")(1)) Code:
With .Range("K" & j) .Value = Trim(Split(StrData, "$")(1)) If Trim(Split(StrData, "$")(UBound(Split(StrData, "$")))) <> Trim(Split(StrData, "$")(1)) Then .Font.ColorIndex = vbRed End With Quote:
Code:
On Error Resume Next Code:
Do Until EOF(1) Quote:
Code:
.Range("K6:K" & LRow).ClearContents Since it's possible to obtain Adobe Acrobat Pro 8 as a free download (http://www.techspot.com/downloads/46...at-8-free.html - note the serial# mentioned there) and that program can be automated via VBA, it would be possible to write a macro that automates IE to download the file, then automates Acrobat to export the data to a text file that the macro I've already provided can process. Quite possibly, re-coding would allow the export to text to be skipped too. Acrobat Pro installs as a COM add-in. For automation code, see, for example: https://www.msofficeforums.com/excel...html#post35801
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#20
|
|||
|
|||
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! |
#21
|
||||
|
||||
Quote:
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] |
#22
|
|||
|
|||
Follow Up
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 |
#23
|
||||
|
||||
Hi Shane,
Perhaps you made an error with the changes to the original code I provided. It should now read: Code:
Sub UpdatePrices() Application.ScreenUpdating = False Dim DataSet As String, StrData As String, StrList As String, StrItem As String Dim i As Long, j As Long, LRow As Long StrList = "," With ThisWorkbook DataSet = .Path & "\ProductCatalog.txt" If Dir(DataSet) <> "" Then With .Sheets("Master") LRow = .Range("A" & .Rows.Count).End(xlUp).Row With .Range("K6:K" & LRow) .ClearContents .Font.ColorIndex = xlColorIndexAutomatic End With For i = 6 To LRow If Trim(.Range("C" & i).Value) <> "" Then StrList = StrList & .Range("C" & i).Value & "," Next i = UBound(Split(StrList, ",")) - 1 Open DataSet For Input As #1 On Error Resume Next Do Until EOF(1) Line Input #1, StrData StrItem = Split(StrData, " ")(0) If InStr(StrList, "," & StrItem & ",") <> 0 Then j = UBound(Split(Split(StrList, StrItem)(0), ",")) + 5 With .Range("K" & j) .Value = Trim(Split(StrData, "$")(1)) If Trim(Split(StrData, "$")(UBound(Split(StrData, "$")))) <> Trim(Split(StrData, "$")(1)) Then .Font.Color = vbRed End With i = i - 1 End If If i = 0 Then Exit Do Loop Close #1 End With End If End With If i > 0 Then MsgBox "Done. However, " & i & " item(s) could not be matched.", vbExclamation Else MsgBox "Done.", vbInformation End If Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#24
|
|||
|
|||
Paul-
Thank you for your quick response to this. The script now highlights in red so it must have been something I mistyped on my end. Thank you very much for fixing this part for me. I figured out how to specify the current directory as the download location using the script I posted previously so that part should be working correctly as well. It saves the PDF file as "ProductCatalog.PDF" in the same directory that the script was run from so it should be ready for conversion. The one piece I am still working on is converting that PDF File to text as part of the script and then putting all of the code together to run smoothly. All of the research I have done thus far on that part of the code looks as though the full version of Adobe Acrobat is required so I am not sure I am going to be able to get around that part. |
#25
|
||||
|
||||
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#26
|
|||
|
|||
Thank you both for your responses! I really appreciate you both putting time into helping me out with this project.
One of the last pieces I am trying to complete is the part of the script that automates the conversion from PDF to Txt. The script I am attaching is the one that will automate the PDF file download into the the sale directory the script is run from. I have done a decent amount of research online on this piece and found some scripts that get me very close to automating the conversion, but I haven't been able to finalize it. Would you guys be able to help me write the last piece and then to put all three parts together? Again, thank you both for all of your help with this! |
#27
|
||||
|
||||
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. |
#28
|
|||
|
|||
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 |
#29
|
||||
|
||||
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. |
#30
|
|||
|
|||
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! |
Tags |
adobe, conversion, pdf |
Thread Tools | |
Display Modes | |
|
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 |