PDF to Excel

macropod · #1 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