March 20, 2011, 6:48 a.m.
posted by mrthe
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, by automatically extracting tables from your source document, converting them into an Excel spreadsheet, and then attaching them to your PDF.
Giving your readers live data to work with
1 Copy Tables into a New Document
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.
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.