DriveWorks Pro 15: How To: Create A Macro To Run On A Document (KB13103005) [send feedback...]

Introduction

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.

The CreateReports macro must exist in a module named DriveWorks, in the VBA Project 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.

Example Macro
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 
        
            ' Reset the row to work on 
            I = I - 1 
     
            ' Reset the final row to account for us deleting one 
            FinishRow = FinishRow - 1
            
        End If 
    
    Next I

    Set bomSheet = Nothing

End Sub

DriveWorks Tech Tips Portal

Create Reports

Creates an Excel document, which uses a CreateReports macro to create a copy of the document in PDF format, to a location and file name specified by you.

View all Tech Tips

DriveWorks Tech Tips Portal is available to DriveWorks Pro customers with an active subscription and support contract.

Tech Tips provide cut down projects that highlight specific functionality for faster and more effective learning.

To access the portal:

  1. Go to https://hub.driveworks.co.uk/tech-tips/
  2. Login with your DriveWorks Pro account credentials, if asked
  3. Click the Visit Tech Tips Portal button for access

The portal provides a search facility, start typing the name of the Tech Tip (as indicated above) to display the Tip you require.


Knowledge Base Article Ref:KB13103005

Table of Contents