Article ID: 170745
Article Last Modified on 1/20/2007
APPLIES TO
- Microsoft Excel 97 Standard Edition
- Microsoft Word 97 Standard Edition
This article was previously published under Q170745
SUMMARY
This article includes a sample Microsoft Visual Basic for Applications macro. The macro uses Automation to build a PivotTable in Microsoft Excel 97 and returns the data back to Microsoft Word.
MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
The following example, when run from Microsoft Word 97, creates a Microsoft Excel 97 PivotTable, inserts the data into a new Microsoft Word document, and then arranges the data in a table format. If the GetObject function returns error 429, the example uses the CreateObject function to start a new session of Microsoft Excel. If the CreateObject function is used, the example uses the Quit method to close the new instance of Microsoft Excel. To use this example, do the following:
Create a worksheet in Microsoft Excel with data similar to the following:
A1: Region B1: Office C1: Sales A2: North B2: Alpha C2: 100 A3: East B3: Beta C3: 120 A4: West B4: Alpha C4: 130 A5: North B5: Beta C5: 100 A6: East B6: Beta C6: 140 A7: West B7: Alpha C7: 110
Then, save the workbook in the My Documents folder with the name Sales.xls.
In Microsoft Word 97, point to Macro on the Tools menu and click Visual Basic Editor. On the Insert menu, click Module and type the following macro:
Sub Create_PivotTable() Dim xlObj As Excel.Application Err.Number = 0 On Error GoTo notLoaded Set xlObj = GetObject(, "Excel.Application.8") notLoaded: If Err.Number = 429 Then Set xlObj = CreateObject("Excel.Application.8") theError = Err.Number End If xlObj.Visible = True xlObj.Workbooks.Open FileName:="C:\My Documents\Sales.xls" With xlObj .Range("A1").Select .ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _ SourceData:= "Sheet1!R1C1:R5C3", TableDestination:="", _ TableName:="PivotTable1" .ActiveSheet.PivotTables("PivotTable1").AddFields _ RowFields:="Office", ColumnFields:="Region" .ActiveSheet.PivotTables("PivotTable1"). _ PivotFields("Sales").Orientation = xlDataField End With xlObj.ActiveSheet.UsedRange.Select Documents.Add With xlObj For Each newCell In .Selection With Selection .InsertAfter Text:=newCell.Value mCount = mCount + 1 If mCount Mod xlObj.Selection.Columns.Count = 0 Then .InsertAfter Text:=vbCr Else .InsertAfter Text:=vbTab End If End With Next newCell ActiveDocument.Range.ConvertToTable _ Separator:=wdSeparateByTabs ActiveDocument.Tables(1).AutoFormat _ Format:=wdTableFormatClassic1 End With If theError = 429 Then xlObj.DisplayAlerts = False xlObj.Quit Endif Set xlObj = Nothing End Sub
- On the Tools menu, click References. Click "Microsoft Excel 8.0 Object Library." This step allows you to use the Microsoft Excel 97 objects, properties, and methods in Visual Basic macros.
- On the File menu, click "Close and Return to Microsoft Word." To run the macro, point to Macro on the Tools menu and click Macros. Click Create_PivotTable and click Run.
REFERENCES
For more information about Automation, click the Index tab in Visual Basic Help, type the following text
automation
and then double-click the selected text to go to the "Working across applications" topic.
Additional query words: ole pivot table
Keywords: kbdtacode kbinfo kbinterop kbprogramming KB170745