FX Classes  -->  Office Automation 

Excel Class Module  - FXc8_Excel

The Excel class module is a simple to use class module that is designed to get you started in the right way in your quest to automate Microsoft Excel.  This class module comes with all the source code so that you can take your software where you need to go. 

Excel Reporting Example

To run the Access 2000/2002/2003 databases, simple save the demo files to a temporary directory and double click on the files to run them

Running Excel Chart

Here is an example of the code that will transfer data from an Access consolidation query into Excel so that it can be used to populate an Excel Chart. The bolded text shows the class module in action.

Const FileDir = "c:\fxclasses\"
Const DataSource = "excelChart"
Const FileType = ".xlt"

'Open Excel with a pre-existing Excel charting template

Set Excel_FX = New FXc8_Excel
openOK = Excel_FX.OpenExcel(FileDir & DataSource & FileType)

If openOK = True Then

  Excel_FX.Calculation = False

' Following is a consolidation query that is used to generate rows of data
' that are passed to Excel.  The chart then uses this information for plotting.

  strQuery = "SELECT Country, Sum(Sales) AS SalesTot, Sum(Budgets) AS
                   BudgetTot FROM zWorld_Demo GROUP BY zWorld_Demo.Country;"

  If Excel_FX.sqlData(strQuery, 1, 1, "data", True) Then

'    Modify the chart titles 

    If Excel_FX.chartTitle(strQuery) Then

       Excel_FX.ChartTitleYAxis "Totals Sales and Budgets"
       Excel_FX.ChartTitleXAxis "Countries"

    End If
  End If

  Excel_FX.Calculation = True
  Excel_FX.Finished

End If

What Do You Have To Do In Excel ?

• Take the template Excel chart document supplied and place it in a directory where all users of your software can reference it. 
• Modify the graph type and of the Excel chart object to suit your requirements.

• Experiment with the sample application that you want to launch your Excel chart report from.  Working projects are provided for Access and Visual Basic.
• Design your consolidation query (or other data collection software)
• Modify the sample software to suit your requirements.
• Add the text that you want to add to the spreadsheet.
• Move the sample application into your existing application.
  

What Actually Happens ?

In the Access Example, you would have a consolidation query that looks like example 3

Example 3 - A sample consolidation query that is used to produce chart

When you transfer those rows of data into the data component of the Excel Charting template, the chart will then reflect those rows of information

Example 4 - An Excel Chart document produced by the FX class module for Excel 

Excel is now open showing this information with the data from the graph on a different sheet.  You can modify the look of the chart to your own preferences or even using your own code if you wish.

Excel Reporting

Here is an example of the code that will transfer data from an ADO persistent data set using Visual Basic 6 into a Excel template file. The bolded text shows the class module in action.

Set Excel_FX = New FXc8_Excel

openOK = Excel_FX.OpenExcel(FileDir & "excelRep.xlt")

If openOK = True Then

'  Add the recordset in the cache directory to the
'  combo box by looping through the recordset and
'  adding a value string to the combo box.

' Excel sometimes doesn't refresh the display properly
' so it is better to display the report in full screen mode

  Excel_FX.FullScreen = True
  Excel_FX.Calculation = False

' Now display the date on the Excel report in the cell B4

  Excel_FX.CellValue Format(Date, "dddd dd-mmm-yyyy"), "B4", "Production"

  repRowInt = 0
  Set Rst = New ADODB.Recordset
  Rst.Open FileDir & DataSource & FileType

' Loop through the data and display by row in Excel

  Do Until Rst.EOF

    Excel_FX.CellValueRC Rst(0), BegRepRow + repRowInt, 1
    Excel_FX.CellValueRC Rst(1), BegRepRow + repRowInt, 2
    Excel_FX.CellValueRC Rst(2), BegRepRow + repRowInt, 3
    Excel_FX.CellValueRC Rst(3), BegRepRow + repRowInt, 4
    Excel_FX.CellValueRC Rst(4), BegRepRow + repRowInt, 5

    repRowInt = repRowInt + 1

    Rst.MoveNext
  Loop

' Close the persisted recordset

  Rst.Close

  If repRowInt > 0 Then

'   It is important to make your spreadsheet big enough to
'   display all the rows of data that you wish to display.
'   All the rows that are not need are deleted the
'   using this class method. Your software needs to keep track of the
'   number of rows used in your recordsets

    Excel_FX.DeleteRows repRowInt + 1 + BegRepRow, MaxRows + BegRepRow - 1
  End If

  Excel_FX.Calculation = True
  Excel_FX.Finished

  Exit Sub
End If

What Do You Have To Do In Excel ?

• If you are doing normal reporting, take any Excel document and place it in a directory where all users of your software can reference it 
• Clean out any text that you are going to replace with information from your database or application.  Save the format settings on the cells that you want to use.
• Add bookmarks to the word document where you are going to insert the text from your software.
• Save the Excel file as a template file *.xlt

What actually happens in this example?

The software opens an Excel template file and makes a new spreadsheet using all the layout and formulae in the template.  The software then transfers static information to specific cells in the spreadsheet using the CellValue method.  Next the software loops through the results of a recordset and adds the information to the spreadsheet using numerical row and columns identifiers using the CellValueRC method.  Finally as most reports are likely to have totals at the bottom of rows of data, the cells that are not used are deleted from the spreadsheet.  Example 5 shows where the spare 20 lines have been deleted using the DeleteRows method.

Example 5 - An Excel Report produced by the FX class module for Excel 

That's all that you need to turn those useful end user spreadsheets into reports that can be run under a controlled software environment using Visual Basic or Microsoft Access Visual Basic.

The Word class module is a simple to use class module that is designed to get you started in the right way in your quest to automate Microsoft Word.  This class module comes with all the source code so that you can take your software where you need to go.

Here is an example of the code that would run open an existing Word report using Visual Basic 6, add some text and print it out. The bolded text shows the class module in action.

Dim fileName As String, wordExport As FXc8_Word, successInt As Long

Set wordExport = New FXc8_Word
With wordExport

  fileName = "c:\fxClasses\wordRep.doc"
  successInt = .openWord(fileName)

  If successInt = True Then

    .gotoBookmark "Address1"
    .addText "555 Willow Drive"

    .gotoBookmark "Address2"
    .addText "Sydney"

    .gotoBookmark "State"
    .addText "NSW"

    .gotoBookmark "Postcode"
    .addText "2000"

    .gotoBookmark "Name"
    .addText "John Smith"

    .printDoc
    .closeDoc
    .Finished

Else

'   Opening the file has failed
    .Finished

End If

End With

What Do You Have To Do In Word ?

• Take any word document and place it in a directory where all users of your software can reference it 
• Clean out any text that you are going to replace with your software
• Add bookmarks to the word document where you are going to insert the text from your software.
• Take a backup of the file to use as a template.

• Experiment with the sample application that you want to launch your Word report from.  Working projects are provided for Access, Excel and Visual Basic.
• Bring your word report into the test environment and add the bookmarks that you need.
• Add the text that you want to add to the document
• Now add the formfields or data extraction software so that you can display your data on your word report.
• Move the sample application into your existing application.
  

What Actually Happens ?

In the Excel Example, you would have a worksheet that looks like example 1

Example 1 - A Worksheet that runs a Word Report using the data typed into the cells

When you click on the Print Report Command button, it runs the visual basic code and the class module to produce a word report that looks like example 2.

Example 2 - A Word document produced by the FX class module for Word Reports.

In the word report, the bookmarks are place markers that decide where the information is to be placed.  The bookmarks do not appear on the document when it is printed.

What Tools Can You Use

Microsoft Access 2000 and up
You need an additional reference to

Word 8 Object library or Word 9 Object Library (if you use Office 2000)

VB, Access and Excel working examples are provided

General Information For FX Classes

Licensing Information

The author of this program accepts no responsibility for damages resulting from the use of this product and makes no warranty or representation, either express or implied, including but not limited to, any implied warranty of merchantability or fitness for a particular purpose. This software is provided "AS IS", and you, its user, assume all risks when using it.

The software comes with complete source code and you may use it however you wish.  We even welcome additions that you might make to the software and will provide you with free support if those changes are useful.

Getting Started With FX Classes

Once you have ordered the software, you will unzip the downloads and look for the following zip file.  Now all you have to do is unpack it and use the samples

  Your Sample Database Is Called   "fxClasses.zip"

Sample database contains a Student Details form and a command button to view all changes made to a student's record.

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.

 

Click here to view all the sample directories and source code

The two links above will work properly on your local drive once you purchase FX Classes.   

Ordering The Software

FX Classes is provided as part the The ToolBox.    It is just one of the many resources that you can try when you use The Toolbox libraries on your own computer.   These libraries have been used in all the Office Automation projects that Garry Robinson has been involved in since late 1998.

Order The Toolbox (includes The Toolbox) Here