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.