PDF to Excel

macropod · #15 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.

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