PDF to Excel

macropod · #1 12-27-2013, 07:27 PM

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

You'll find the above runs much faster than the previous solution, which entail converting the entire 800-page file to an Excel workbook. The new code simply reads every line, without doing any conversions, looking for the items that match your LCB#s and parsing what it needs from those lines.

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))

to:

Code:

.Range("K" & j).Value = Trim(Split(StrData, "$")(UBound(Split(StrData, "$"))))

Note: Your 'Master' sheet already has four items that won't get regular/sale prices added to them, because they don't have valid LCB#s. Having blanks prices for them (which is what you get with the current coding) is a good way of identifying the problem ones.

shanemarkley · #2 01-08-2014, 09:18 PM

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!

macropod · #3 01-08-2014, 09:56 PM

Quote:

Originally Posted by shanemarkley

As a small follow up, would it be possible to highlight if there is actually a sales price?

You could do that by changing:

Code:

      .Range("K6:K" & LRow).ClearContents

to:

Code:

      With .Range("K6:K" & LRow)
        .ClearContents
        .Font.ColorIndex = xlColorIndexAutomatic
      End With

and changing:

Code:

          .Range("K" & j).Value = Trim(Split(StrData, "$")(1))

to:

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

and using whatever colour you might prefer if you don't like vbRed.

Quote:

I get the following error:

"Run-time error '9': Subscript out of range"

Clicking on Debug points to the line: "StrItem = Split(StrData, " ")(0)"

The code runs without error for me, unless I add an empty dummy line to the data file. You could force the macro to ignore such errors by inserting:

Code:

On Error Resume Next

before, say:

Code:

Do Until EOF(1)

Quote:

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?

Not without a fair bit of re-coding, as the macro starts of by clearing the entire column in preparation for the new data. That's currently done by the line:

Code:

      .Range("K6:K" & LRow).ClearContents

In any event, when I reviewed the data, I found that your 'SLO' items all seem to have valid LCB# values - and are at least sometimes cheaper than your liquor rep prices (e.g. Cordial Soco & Lime SLO $20.97 -vs- 3337 SOUTHERN COMFORT AND LIME 750 ML $17.99. It appears these are the same product).

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

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