Hack 55 Easily Attach Your Document's Tables

Pack your document's essential information into its PDF edition.

Readers copy data from PDF documents to use in their own documents or spreadsheets. Tables usually contain the most valuable data, yet they are the most difficult to extract from a PDF [Hack #7]. Give readers what they need, as shown in Figure 5-4, by automatically extracting tables from your source document, converting them into an Excel spreadsheet, and then attaching them to your PDF.

Figure 5-4. Giving your readers live data to work with

5.6.1 Copy Tables into a New Document

In Microsoft Word, use the following macro to copy a document's tables into a new document. In Word, create the macro like so.

Open the Macros dialog box (Tools Macro Macros . . . ). Type CopyTablesIntoNewDocument into the "Macro name:" field, set "Macros in:" to Normal.dot, and click Create.

A window will open where you can enter the macro's code. It already will have two lines of code: Sub CopyTablesIntoNewDocument() and End Sub. You don't need to duplicate these lines.

You can download the following code from http://www.pdfhacks.com/copytables/:

Sub CopyTablesIntoNewDocument( )

' version 1.0

' http://www.pdfhacks.com/copytables/



Dim SrcDoc, NewDoc As Document

Dim SrcDocTableRange As Range



Set SrcDoc = ActiveDocument

If SrcDoc.Tables.Count <> 0 Then

    

    Set NewDoc = Documents.Add(DocumentType:=wdNewBlankDocument)

    Set NewDocRange = NewDoc.Range

    Dim PrevPara As Range

    Dim NextPara As Range

    Dim NextEnd As Long

    NextEnd = 0

        

    For Each SrcDocTable In SrcDoc.Tables

        Set SrcDocTableRange = SrcDocTable.Range

       

        'output the preceding paragraph?

        Set PrevPara = SrcDocTableRange.Previous(wdParagraph, 1)

        If PrevPara Is Nothing Or PrevPara.Start < NextEnd Then

        Else

            Set PPWords = PrevPara.Words

            If PPWords.Count > 1 Then 'yes

                NewDocRange.Start = NewDocRange.End

                NewDocRange.InsertParagraphBefore

                

                NewDocRange.Start = NewDocRange.End

                NewDocRange.InsertParagraphBefore

                NewDocRange.FormattedText = PrevPara.FormattedText

            End If

        End If

            

        'output the table

        NewDocRange.Start = NewDocRange.End

        NewDocRange.FormattedText = SrcDocTableRange.FormattedText

        

        'output the following paragraph?

        Set NextPara = SrcDocTableRange.Next(wdParagraph, 1)

        If NextPara Is Nothing Then

        Else

            Set PPWords = NextPara.Words

            NextEnd = NextPara.End

            If PPWords.Count > 1 Then 'yes

                NewDocRange.Start = NewDocRange.End

                NewDocRange.InsertParagraphBefore

                NewDocRange.FormattedText = NextPara.FormattedText

            End If

        End If

     

     Next SrcDocTable

End If

End Sub

Run this macro from Word by selecting Tools Macro Macro . . . , selecting Copy Tables Into New Document, and clicking Run. A new document will open that contains all the tables from your current document. It will also include the paragraphs immediately before and after each table. This feature was added to help readers find the table they want. Modify the macro code to suit your requirements.

5.6.2 Create an HTML or Excel Document from Your Tables Document

Use [Hack #35] to convert this new document into HTML. Make this HTML file act like an Excel spreadsheet by changing its filename extension from html to xls. Excel is perfectly comfortable opening data this way.

Not only can Excel open HTML files disguised as XLS files, but it can also convert Internet web sites into spreadsheets. From Excel, select File Open, enter a web address in the "File name:" field, and click Open.