DataTable Cells with references are corrupted

  • Questions
  • DataTable Cells with references are corrupted
Question ID: 105672
1
0

After importing an .xls sheet into the datatable, the cells with references to other cells become corrupted and show #REF instead of the data we need. Is there any way to fix this?

Marked as spam
Posted by (Questions: 341, Answers: 22)
Asked on October 28, 2014 9:00 pm
11 views
Answers (1)
1
Private answer

This is a known issue with the DataTable component, which is made by a third-party called FormulaOne. In KM1345490, HP published the below code as a work-around to clean up the problem, which is that [] are appended to cells with references:

Problem:

Before importing sheet individually:

=Action1!A2

After

=[]Action1!A2

Code to fix from HP KM article above:

Note: The following script is provided for example purposes only and ''AS-IS''. It is not supported by Hewlett Packard.

Function CleanMissingReferencesPerDataTableSheet(DTObj, Sheet, Parameter, Row)
Set DTSheetObj = DTObj.GetSheet(Sheet)
currentRow = DTSheetObj.GetCurrentRow
DTSheetObj.SetCurrentRow Row
Set DTSheetParam = DTSheetObj.GetParameter(Parameter)
If DTSheetParam.Value = ''#REF!'' then
DTSheetParam.Value = Replace(DTSheetParam.RawValue, ''='' & chr(91) & chr(93),''='')
End If
Set DTSheetParam = Nothing
DTSheetObj.SetCurrentRow currentRow
Set DTSheetObj = Nothing
End Function

' To do so against a particular sheet
Sheet = '' Action1''
For y = 1 to DataTable.GetSheet(x).GetParameterCount
For z = 1 to DataTable.GetSheet(x).GetRowCount
CleanMissingReferencesPerDataTableSheet DataTable, Sheet, y, z
Next
Next

' To fully sweep entire Data Table for any #REF! found
For x = 1 to DataTable.GetSheetCount
For y = 1 to DataTable.GetSheet(x).GetParameterCount
For z = 1 to DataTable.GetSheet(x).GetRowCount
CleanMissingReferencesPerDataTableSheet DataTable, x, y, z
Next
Next
Next

Marked as spam
Posted by (Questions: 16, Answers: 751)
Answered on October 28, 2014 9:01 pm
yep, that cleaned it up and made it work right. Pretty frustrating we have to do that.
( at October 28, 2014 9:02 pm)