title: Extract tables from pdf (to excel), pref. w/ vba
body: <p>I am trying to extract tables from pdf files with vba and export them to excel. If everything works out the way it should, it should go all automatic. The problem is that the table are not standardized.</p>
  
  <p>This is what I have so far.  </p>
  
  <ol>
  <li>VBA (Excel) runs <a href="http://www.foolabs.com/xpdf/" rel="noreferrer">XPDF</a>, and converts all .pdf files found in current folder to a text file.  </li>
  <li>VBA (Excel) reads through each text file line by line.</li>
  </ol>
  
  <p>And the code:</p>
  
  <pre><code>With New Scripting.FileSystemObject
  With .OpenTextFile(strFileName, 1, False, 0)
  
      If Not .AtEndOfStream Then .SkipLine
      Do Until .AtEndOfStream
          //do something
      Loop
  End With
  End With
  </code></pre>
  
  <p>This all works great. But now I am getting to the issue of extracting the tables from the text files.
  What I am trying to do is VBA to find a string e.g. "Year's Income", and then output the data, after it, into columns. (Until the table ends.)</p>
  
  <p>The first part is not very difficult (find a certain string), but how would I go about the second part. The text file will look like <a href="http://pastebin.com/jf4VG92x" rel="noreferrer">this Pastebin</a>.  The problem is that the text is not standardized. Thus for example some tables have 3-year columns (2010 2011 2012) and some only two (or 1), some tables have more spaces between the columnn, and some do not include certain rows (such as Capital Asset, net).</p>
  
  <p>I was thinking about doing something like this but not sure how to go about it in VBA.</p>
  
  <ol>
  <li>Find user defined string. eg. "Table 1: Years' Return."   </li>
  <li>
  a. Next line find years; if there are two we will need three columns in output (titles +, 2x year), if there are three we will need four (titles +, 3x year).. etc<br>
  b. Create title column + column for each year.   </li>
  <li>When reaching end of line, go to next line  </li>
  <li>
  a. Read text -> output to column 1.<br>
  b. Recognize spaces (Are spaces > 3?) as start of column 2. Read numbers -> output to column 2.<br>
  c. (if column = 3) Recognize spaces as start of column 3. Read numbers -> output to column 3.<br>
  d. (if column = 4) Recognize spaces as start of column 4. Read numbers -> output to column 4.  </li>
  <li>Each line, loop 4.  </li>
  <li>Next line does not include any numbers - End table. (probably the easiet just a user defined number, after 15 characters no number? end table)</li>
  </ol>
  
  <p>I based my first version on <a href="http://www.business-spreadsheets.com/forum.asp?t=884&amp;o=n" rel="noreferrer">Pdf to excel</a>, but reading online people do not recommend <code>OpenFile</code> but rather <code>FileSystemObject</code> (even though it seems to be a lot slower).</p>
  
  <p>Any pointers to get me started, mainly on step 2?   </p>