How to autofit excel workbooks with Forest & Trees OLE automation

Document ID : KB000055752
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction

The Forest & Trees OLE Automation Add-In allows the user to define objects and use their methods and variables. This article will show how Excel can be manipulated to Autofit the columns so that all data is shown in a work book.

Creating the Formula

Here is an example of a formula that will open an Excel Spreadsheet found on the C: drive. It will autofit the columns so that all the data is shown.

dim objExcel as OleObject                                    // Define the OleObjects
dim objWorkbook as OleObject
dim objColumns as OleObject
objExcel         := New OleObject( 'Excel.Application' )     // Create the Excel Object 
objWorkbook := objExcel.Workbooks // Create the Workbook Object
objWorkbook.Open( 'c:\myData.xls' )                          // Open the Excel Spreadsheet
// Note that the default does not
// display the spreadsheet until
// 'objExcel.visible := TRUE'
// is executed.
objColumns := objExcel.Columns // Create the Columns Object
objColumns.AutoFit( )                                        // Autofit the columns  
objExcel.visible := TRUE                                     // Now display the autofitted  
Spreadsheet

Implementation

The code can be attached to any control that has a formula. It could be put into a function (for example) called AutoFitExcel, with a couple of parameters passed to show where the spreadsheet is and to determine if you want to display the spreadsheet or just save it.

Summary

Autofitting columns are just one small part of what is available with ole automation with Excel. Forest & Trees can be used to access all the methods and parameters in the Excel object.