DIAdem crashes when using method „find“ for a sheet

[TwinCam24]

Hello,

I loaded an Excel file with DataFileLoad and a self made data plugin. In this data plugin’s VBS script I can successfully search for a CellPosition using:

set ReturnValue = Workbook.Sheets(1).Find("xyz")

This works fine.

But when I want the search to start from a certain row and column like this:

set ReturnValue = Workbook.Sheets(1).Find("xyz", 1, 1)

DIAdem crashes giving error codes like this:

unknown command “”
address 000436DA
modul ntdll.dll

I even used this example code form DIAdem help inside a data plugin:

Dim oSheet : Set oSheet = Workbook.Sheets(1)

Dim oGroup : Set oGroup = Root.ChannelGroups.Add("Find_Result")

Dim Count : Count = 0

Dim RetVal : Set RetVal = oSheet.Find("a")

While (RetVal.Row>0) and (RetVal.Column>0)

Count = Count +1

Call oGroup.Properties.Add("Pos" & Cstr(Count), Cstr(RetVal.Row) & "," & cstr(RetVal.Column))

Set RetVal = oSheet.Find("a", RetVal.Row, RetVal.Column+1)

Wend

and It crashed on three computers (2x Win XP SP3, 1x Win 7) with the same problem.

Has anyone an idea? (DIAdem 2012 SP1)

[Pesmith8]

TwinCam

I have done quite a bit of working with Excel files in DIAdem. 

I woud try to keep the using of the Excel find command to a minimum.  I just never new if that command would return something, and if it did not then the risk of hanging things is high. 

For me, use of the Range command to return a 2D Array of cell values works well, After the Range command is ran in Excel, it will return 2D Array( this can be a large array) you can interate the array fairly quickly.  Much faster than looking at all the cells in excel.

As far as the syntax to go from Range to a Array, find out the commands needed to get Range to Array working in excel (vba), then the syntax in DIAdem (Vbs) is fairly similar.

Please post specific questions if any part of this needs more clarification/examples.

Paul

[TwinCam24]

Hello Paul,

thanks for your quick answer. The idea of putting the content of an excel sheet into a 2D array sounds good. But I couldn’t find any command in DIAdem VBS which is similar to the range command Excel VBA.

There is a CellBlock object but it works with channels. It can’t be read like an array.

The Values in the Excel files I want to read are not in a column or in a row. The single cells with values are scattered over the sheets. That’s why the CellBlock object doesn’t seem to be suitable for my application.

Not working code completion and incomplete / not working example scripts make it even harder to understand the intentions of the different objects, methods and properties like CellBlock etc.

Of course one could build a function that goes through the sheet from cell to cell to put it in an array but this can’t be the intention.

Even if you have the array, there is no built in command to search / filter a 2D array.

Besides that, the find command does work. It only crashes when I tell it to start search from a certain row and column. That’s strange.

Maybe some VBS sample code of working Excel Data Plugins might help me to better understand Data Plugins.

Another question: Is there a way to load Excel files with a VBS script without use of a Data Plugin?

I already found out, that the workbook object can be created with CreateObject("Excel.Application")
but Excel isn’t installed on the computer so that’s no option for me 😞

[usac]

Hi,

I can confirm that the crash in the spreadsheet reader still exists. A fix should be available latest with our 2015 release.

You can track the issue with CAR number 484563.

As a workaround for now you can determine min and max position of the current sheet before your first use of the method Find():

Dim maxPos : Set maxPos = oSheet.MaxPosition
Dim minPos : Set minPos = oSheet.MinPosition
While (RetVal.Row>0) and (RetVal.Column>0)
  ...
  Set RetVal = oSheet.Find("a", RetVal.Row, RetVal.Column+1)
  ...
Wend