DriveWorks Pro 22

How To: Create A Macro To Run On A Document (KB13103005)

How To: Create A Macro To Run On A Document

When creating Word or Excel documents DriveWorks will automatically execute a macro called CreateReports, if it exists, in a module named DriveWorks, in the VBProject for the document.

Example

An Excel document has been created and captured into DriveWorks, that itemizes all components into a parts list. DriveWorks will drive the quantities of all required components into the adjacent cells in column J. When a component is not required DriveWorks will drive a quantity of zero.

The finished document is required to have all rows, where the quantity is zero, deleted so the components that are not required are not shown.

1. Create an Excel document that represents the parts list. List all components as they are required to be displayed.
2. For each component quantity that could change as a result of a specification being made, name the appropriate Quantity cell (the cells in column K highlighted below). 
3. In the Excel template open the Visual Basic editor. In the Project window right click on the project and click Insert > Module
4. In the properties for the module change the name to DriveWorks
5. Copy the code from the example macro below to create a macro called CreateReports in the module DriveWorks.
6. Save the template and add it into the DriveWorks project. Build rules for the named quantity cells, ensuring that some values will result in  zero when the document is created.
7. Now when DriveWorks creates the document and drives all the required values into it, the CreateReports macro will be found and run deleting all the rows that have zero quantities in column K.

Depending on the template you create you may want to change some of the code in the example below, in perticular:

StartRow - Identifies the row the search for a zero value is to begin

FinishRow - Identifies the row the search for a zero value is to end

QuantityColumn - Identifies the column the search for a zero value is to take place in.

Public Sub CreateReports()

    ' Initialise the variables '
    Dim bomSheet As Worksheet
    Dim StartRow As Integer 
    Dim FinishRow As Integer 
    Dim I As Integer 
    Dim QuantityColumn As String

    ' Set the start and finish rows '
    StartRow = 13
    FinishRow = 48

    ' Set the quantity column '
    QuantityColumn = "K"

    ' Attach to the first worksheet '
    Set bomSheet = ActiveWorkbook.Sheets(1)

    ' Loop up the rows, deleting any row whose quantity = 0 '
    For I = FinishRow To StartRow Step -1
        If bomSheet.Range(QuantityColumn & I).Value2 = 0 Then 
    
    '         Delete the row '
             bomSheet.Rows(I & ":" & I).Delete Shift:=xlUp 
            
        End If 
    
    Next I

    Set bomSheet = Nothing

End Sub