Home Search

DriveWorks Pro 21
Export/Calculate/Import To and From Named Sheets in Excel

Send Feedback

Export/Calculate/Import To And From Named Sheets In Excel

This Specification Macro Task will look for the text in the first column of a specified input Sheet.

If the Project has a Variable with the same name, it will take the Variable result and insert it in the second column for each one found.

It will then calculate the Sheet and also run a Macro called CreateReports if it exists.

Please see the topic How To Create A Macro To Run On A Document for more information on the CreateReports macro.

It will then read the data from a two column multi row output sheet, and pass it into the Specification for driving into Constants or Controls.

Microsoft Office

  • Ensure Microsoft Office is installed on all DriveWorks machines that will process this Task.

Microsoft Office and DriveWorks Live running as a service through IIS

Services (IIS) do not have access to many applications, especially COM programs such as Microsoft Word and Excel.

When using DriveWorks Live and IIS please read the following articles for information on where to place this Task.

Properties

Property NameDescription
TitleChanges the Title (not the name) of the Task.
File NameFile Name of the Excel File Including Extension.
Show ExcelWorkbook will be visible if set to TRUE.
Input Sheet NameSheet name of the inputs sheet.
Output Sheet NameSheet name of the outputs Sheet.
Constant NameConstant into which the Input and Output count will be stored.

Example

When this Task is added the properties are static. To be able to build rules on a static property see How To: Change A Static Property To A Dynamic Property.

Property NameExample RuleExample ResultMeaning
File Name"D:\DriveWorks\ExportCalcImport.xlsx"D:\DriveWorks\ExportCalcImport.xlsxWill use the Excel file ExportCalcImport.xlsx for the export and import.
Show ExcelTRUETRUEWill briefly show the Excel file while the export and import takes place.
Input Sheet Name"Input"InputWill use the sheet named Input as the Input sheet name.
Output Sheet Name"Output"OutputWill use the sheet named Output as the Output sheet name
Constant NameDWConstantResults3 inputs driven and 4 outputs retrieved with zero failuresWill drive the results of the import and export to the constant named Results.

Example Excel File - Input Sheet

The Excel sheet used to import the Variable values to is required to have two columns:

  • The first column contains the name of the Variable (do not include the DWVariable prefix).
  • The second column is where the value of the Variable will be imported.

The sheet needs headers in the first row, which need to be Name, Value.

The data must begin on the second row.

AB
1NameValue
2BayLength
3BayQuantity
4Cost
Input

Example Excel File - Output Sheet

The Excel sheet used to export values to Constants and Controls is required to have two columns:

  • The first column contains the name of the Constant or Control (do not include the DWConstant prefix or the Return suffix).
  • The second column contains the value to be passed into the Constant or Control.

If a Constant is found with the same name (not case sensitive) then the value from the second column will be passed into it.

If no Constant is found with that name, then DriveWorks will look for a Form Control with that name to pass the value to.

If no Form Control is found, then no action will be taken with that rows data.

If a Constant and Form Control exist with the same name the Constant will take priority.

The sheet needs headers in the first row, which need to be Name, Value.

The data must begin on the second row.

AB
1NameValue
2OverallLength=Input!B2*Input!C2
3ValveSize=IF(Input!B2>2000,"7/16-20 UNF","9/16-18 UNF")
4MaterialThickness=IF(Input!B2>2000,"1.6","1.2")
5Color=VLOOKUP(B2,E5:F100,2,TRUE)
Output
If any Excel formulas result in an error (#VALUE, #NA, etc.) the value -2146826246 will be exported into the Constants and Controls.

Status Outputs

This task supports Status Outputs. These can be used to perform different actions depending on what the status outcome of the task is. For more information see Status Outputs in the Task Node Outputs section of Specification Macros Task Node.

The status output navigation is as follows:

Success

  • Data sent and retrieved successfully

Success with Warnings

  • N/A - Should never be fulfilled.

Failed (one of the following occurred).

  • File name is empty
  • Target file does not exist or could not be accessed
  • Target file extension is invalid
  • Input sheet name is empty
  • Output sheet name is empty
  • Unknown error occurred