Pushing data into Word or Excel

This example demonstrates how to use Visual Basic as a controlling application to push data into Word and Excel.

This example is an extension of the techniques adopted in the rich text box example. The report output medium is Word and Excel, and the same template techniques are used to allow the user to select or create a range of report results.

The project consists of two objects: a form, frmReportInExcelAndWord, which contains three command buttons and two option buttons (and which controls the activation of Word and Excel with the correct template and the execution of the report); and a module, modReportInExcelAndWord, which includes the Sub Main() routine that displays the form.

In the frmReportInExcelAndWord Declarations section, we add constants to hold information about the report template to use:

Private Const sDocTemplate = "Company List Template"
Private Const sDocResult = "Result"
Private Const sDocWordExtension = ".doc"
Private Const sDocExcelExtension = ".xls"
Private Const sDocWordTmpltExtension = ".dot"
Private Const sDocExcelTmpltExtension = ".xlt"

Two forms of each application’s output are used: the standard document format (XLS, DOC) and the template equivalent (XLT, DOT).

Two lines of code create a new instance of the CompanyDetails business object and an in-process instance of the CommonMethodsAndProperties (ActiveX DLL) object:

Private cmUtils   As New CommonMethodsAndProperties
Private csCompDet As New CompanyDetails

Two lines of code create a reference to the application object being used and to a variable that indicates which method the user has selected:

Private nOutputMedium As Integer
Private objOutput     As Object

In the Form_Load event, we enter code to inform the business object of the default method of data retrieval and code to ensure that no output medium is selected yet. In the Form_Unload event, basic housekeeping is performed to ensure that no trailing reference to the objects remains.

Private Sub Form_Load()
    ' Use only the pass by variant method.
    csCompDet.DataPassFormat = csPassByVariant
    nOutputMedium = -1 ' No value
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set csCompDet = Nothing
    Set cmUtils = Nothing
    Set objOutput = Nothing
End Sub

The command button cmdReload is used to load a template and ensure that the user never overwrites it. The following code is placed in the cmdReload_Click event to ensure that the correct object is created and the relevant template is loaded and saved as an output result file:

Private Sub cmdReload_Click()

Dim nPos      As Integer
Dim sFileName As String

' Which output medium has the user selected?
    Select Case nOutputMedium
        Case cmToWord

' Indicate to the common utils object that Word is selected.
            cmUtils.OutputMedium = cmToWord

' Prepare the object.
            cmUtils.OpenOutputMedium objOutput, App.Path & "\" _
                & sDocTemplate & sDocWordTmpltExtension
            sFileName = App.Path & "\" & sDocResult & _
                sDocWordExtension

' Make sure we have a unique output result file.
            ' See example code on the CD.
            §
            objOutput.FileSaveAs sFileName
            objOutput.AppShow

        Case cmToExcel
' Indicate to the common utils object that Excel is selected.
            cmUtils.OutputMedium = cmToExcel

' Prepare the object.
            cmUtils.OpenOutputMedium objOutput, App.Path & "\" _
                & sDocTemplate & sDocExcelTmpltExtension
            sFileName = App.Path & "\" & sDocResult & _
                sDocExcelExtension

' Make sure we have a unique output result file.
            ' See example code on the CD.
            §
            objOutput.activeworkbook.SaveAs sFileName
            objOutput.application.Visible = True

' No output method is active; report error.
        Case Else
            MsgBox "Please select an output medium"
    End Select
End Sub

Figure 3-14 and Figure 3-15 show the format of the output result file once the Activate button (cmdReload) is clicked.

Figure 3-14 Word template used by Visual Basic application

Figure 3-15 Excel template used by Visual Basic application

The command button cmdProcess is used to populate the active object document with data extracted from the business object. The criteria for the report reside within the object document and are retrieved in the same fashion as in the earlier rich text box example. Here’s a sample of the code:

§
' Extract the criteria from the object document.
    vFrom = cmUtils.ExtractCriteria(objOutput, _
        ":FilterStart:", ":FilterEnd:")
    csCompDet.BuildCompanyCriteria vFrom, v1, v2, v3, v4, _
        v5, v6, v7, v8, v9, v10

    csCompDet.BuildCompanyList v1, v2, v3, v4, v5, v6, _
    v7, v8, v9, v10

' Get the result back into a variant.
    vCompanyList = csCompDet.GetCompanyList

' Prepare the output medium for data population.
    cmUtils.PrepareOutputMedium objOutput

' Loop through the return variant array, and populate the data.
    On Error Resume Next
    For nPos = 1 To UBound(vCompanyList, 1)
        cmUtils.InsertCopyRow objOutput, ":RowID:"
        cmUtils.PopulateCell objOutput, ":RowID:", ""

        vStockList = csCompDet.Companies.Item(nPos).GetStockList

        cmUtils.PopulateCell objOutput, ":CmpID:", "" & _
            cmUtils.StripData(vCompanyList(nPos), 1, Chr(9))
        ' And so on
        §
        For nPos1 = 1 To UBound(vStockList, 1)
            If nPos1 > 1 Then
                cmUtils.InsertCopyRow objOutput, ":RowID:"
                cmUtils.PopulateCell objOutput, ":RowID:", ""
                cmUtils.PopulateCell objOutput, ":CmpID:", ""
                ' And so on
                §
        Next nPos1
    Next nPos

' A trailing row is left; remove it.
    cmUtils.DeleteRow objOutput, ":RowID:"
    On Error GoTo 0

' Display the result.
    cmUtils.PrepareOutputMedium objOutput
§

Figure 3-16 shows the format of the output result file in Word once the Process button (cmdProcess) is clicked. The Excel worksheet appears as in Figure 3-17.

Figure 3-16 Word report result

Figure 3-17 Excel report result