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.
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.
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 ' 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
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.
|Knowledge Base Article Ref:||KB13103005|