PDF to Excel

shanemarkley · #1 12-24-2013, 04:58 PM

Macropod, that macro does to a pretty decent job at outputting the txt into an xls file. There are a couple fields that are still off (mainly the first item under each heading), but I don't think any of them are going to affect me.

My ultimate goal is to run a script that will pull all of the updated prices for each item and copy it to Cost/Inventory Sheet. Is this something that you could help me write as well? I am thinking it would go something like this:

1. Search through the outputted text for the item "code". Ex. Jim Beam would be "4079".
2. Once the item is found in the outputted text, see if there is any changes in the "Price" field based on the price in the Cost/Inventory Sheet.
3. If that price is changed, update the price in the Cost/Inventory Sheet and highlight it to show there was a change.
4. Repeat this process for each item in the inventory

Thanks again for all the help and I hope you have a good holiday!

BobBridges · #2 12-24-2013, 06:20 PM

The code is actually in the workbook, Shane. Open it up and then look at the VBA code—if you don't know how, ask and any one of us will be able to explain it—and you can see how it works.

Then again, if you don't know how, maybe you've never written a VBA macro and you won't see how it works, in which case you're starting from further back than I suspected. If so, we should settle down and discuss the program a piece at a time.

macropod · #3 12-24-2013, 06:25 PM

Quote:

Originally Posted by shanemarkley

There are a couple fields that are still off (mainly the first item under each heading)

Easily enough fixed:

Code:

Sub ParsePDFData()
Application.ScreenUpdating = False
With ActiveDocument.Range
  .Paragraphs.First.Range.Delete
  .Paragraphs.First.Range.Text = "Code" & vbTab & "Product" & vbTab & "Size" & vbTab & "Sales Start" & vbTab & "Sales End" & vbTab & "Price" & vbTab & "Old Price" & vbCr
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchWildcards = True
    .Text = "^13[!^13]@^13[!^13]@^13[!^13]@^13^12^13[!^13]@^13"
    .Replacement.Text = "^p"
    .Execute Replace:=wdReplaceAll
    DoEvents
    .Text = "[ ]{1,}^13"
    .Execute Replace:=wdReplaceAll
    .Text = "^13{2,}"
    .Execute Replace:=wdReplaceAll
    DoEvents
    .Text = "^13[0-9]{1,2}/[0-9]{1,2}/[0-9]{4} Page [0-9]{1,4}*notice.^13"
    .Replacement.Text = ""
    .Execute Replace:=wdReplaceAll
    .Text = "(^13)([A-Z][!^13]@)^13([A-Z0-9][!$^13]@)^13([0-9]{1,}) ([!$]@$[0-9.]{4,}>)"
    .Replacement.Text = "\1\4 \2 \3 \5"
    .Execute Replace:=wdReplaceAll
    DoEvents
    .Text = "(^13[0-9]{1,}>)([!$]@)($[!^13]{1,})"
    .Replacement.Text = "\1^t\2^t^t^t^t\3"
    .Execute Replace:=wdReplaceAll
    DoEvents
    .Text = "([0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}) ([0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}) ^t^t^t"
    .Replacement.Text = "^t^t\1^t\2"
    .Execute Replace:=wdReplaceAll
    DoEvents
    .Text = "(EACH )(^t)"
    .Replacement.Text = "\2\1"
    .Execute Replace:=wdReplaceAll
    .Text = "([0-9.]{2,5}[ ML]{2,4})(^t)"
    .Execute Replace:=wdReplaceAll
    DoEvents
    .Text = "($[0-9.]{4,}) ($[!^13]{1,})"
    .Replacement.Text = "\1^t\2"
    .Execute Replace:=wdReplaceAll
    DoEvents
    .Text = "^t[ ]{1,}"
    .Replacement.Text = "^t"
    .Execute Replace:=wdReplaceAll
    .Text = "[ ]{1,}^t"
    .Execute Replace:=wdReplaceAll
    DoEvents
  End With
  .Copy
End With
Call Export
Application.ScreenUpdating = True
End Sub
 
Sub Export()
Dim xlApp As Object, xlWkBk As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
  .Visible = True
  .ScreenUpdating = False
  Set xlWkBk = .Workbooks.Add
  With xlWkBk.Sheets(1)
    .Range("A1").PasteSpecial Paste:=-4163 'xlPasteValues
    .Columns.AutoFit
    .Columns("A:A").ColumnWidth = 8
    .Range("A2").Select
    .Columns("C:C").HorizontalAlignment = xlRight
  End With
  With .ActiveWindow
      .SplitColumn = 0
      .SplitRow = 1
      .FreezePanes = True
  End With
  .ScreenUpdating = True
End With
Set xlWkBk = Nothing: Set xlApp = Nothing
End Sub

Note: There's a few extra enhancements to deal with wrapped lines in the source that come out as disjointed paragraphs in the text file. Plus there's a header row for the output.

Quote:

My ultimate goal is to run a script that will pull all of the updated prices for each item and copy it to Cost/Inventory Sheet. Is this something that you could help me write as well?

Does that mean you're really only concerned with the items that have the two prices?

Quote:

I am thinking it would go something like this:

1. Search through the outputted text for the item "code". Ex. Jim Beam would be "4079".
2. Once the item is found in the outputted text, see if there is any changes in the "Price" field based on the price in the Cost/Inventory Sheet.
3. If that price is changed, update the price in the Cost/Inventory Sheet and highlight it to show there was a change.
4. Repeat this process for each item in the inventory

As coded, the macro sends its output to a new Excel file. Obviously some changes would be required to output the results to a file that already exists. Indeed, in such a scenario it might be better to run the macro from Excel, let Excel automate a Word session for the parsing, then just do the necessary updating.

shanemarkley · #4 12-26-2013, 11:38 AM

Macropod, thank you for fixing that piece. You are correct in saying that this would be more ideal if it could all be run via Excel if that is not too difficult. I am concerned with not only the fields that have special prices, but all of the fields that are in our inventory.

Not sure if you saw my response to Bob, but maybe it would be best if the macro just pulled out the needed data based on what liquors an establishment sells and outputted that in the correct format instead of everything? This would save a ton of time and processing power since it would only need to be a couple hundred items. In the above message, I attached part of the Cost/Intventory spreadsheet I am working with so you can see an example of the format of spreadsheet I am trying to update.

Many thanks for all your help with this!

macropod · #5 12-27-2013, 03:46 AM

Quote:

Originally Posted by shanemarkley

this would be more ideal if it could all be run via Excel if that is not too difficult. I am concerned with not only the fields that have special prices, but all of the fields that are in our inventory.
...
maybe it would be best if the macro just pulled out the needed data based on what liquors an establishment sells and outputted that in the correct format instead of everything?

Before proceeding, it would appear that the only data that can reliably be used to match the workbook to the source data are the LCB#s. But that's OK, because it also just happens to be the case that even where the line-wrapping has messed up the data, both the LCB# and the price(s) are on the same line. I take it you want the retail prices updated. However, where there are two prices, which do you want?

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