Is it possible to read the contents of an Excel cell in DIAdem and assign its value to a variable in a VBS script.

[Matthew.cf828br]

Hi All,

Initially I thought this little problem would be relatively straight forward but now I’m not so sure. I am familiar with the mechanism by which DIAdem communicates with Excel and how to change the contents of a cell via a VBS script. In my task the contents of the cell in the first row, first column of MyProblem.xls contains the text “DIAdem”. I would like to be able to read this value and assign it to the variable MyString. I originally thought of doing something simple like this:

Dim MyString

Dim Excel, ExcelSheet

Set Excel = CreateObject(“Excel.Application”)

Excel.Workbooks.Open(“C\MyProblem.xls”)

Set ExcelSheet = Excel.Workbooks(“MyProblem.xls”).Sheets(“Sheet1”)

MyString = ExcelSheet.Cells(1,1)

At this point I would have hoped that MyString would have been set equal to “DIAdem” and I could have used MyString to change the name of a channel in the data portal if I desired using the following code:

Data.Root.ChannelGroups(1).Channels(1).Name = MyString

Doesn’t seem to work though. I’m guessing it is because MyString has not picked up the value of the contents of the cell? Can anybody propose a solution to my problem or indeed confirm whether what I am proposing to do is technically feasible.

Thanks in advance for any responses.

Matthew

[Matthew.cf828br]

Hi All,

I think I may have just actually solved this. Writing to this forum may have just triggered something in my mind.

Still interested in any responses though.

Thanks

Matthew

[Brad_Turpin]

Hi Matthew,

Just staring at your ActiveX code, it looks fine to me.  My first thought is that this should work as you outlined it, and I've done this sort of thing many times, so I know it can work.  My second thought though, is that what you probably really want is a DataPlugin and not a VBScript.  Then you could just drag&drop the Excel file into the Data Portal and load all the properties and channels you want from the Excel file.  If you have DIAdem 2010 or later you can use the SpreadSheet reader object in the DataPlugin to avoid the Excel ActiveX functions (and Excel's jealously with other applications trying to read a file it has open already).

Feel free to send me a few sample Excel files and describe what you want to load from the various cells, and I'd be happy to help you get a DataPlugin written to load your data.  You can also email me at brad.turpin@ni.com.

Brad Turpin

DIAdem Product Support Engineer

National Instruments

[Matthew.cf828br]

Hi Brad,

I thought that I could rely on you to make a contribution. As I said in the follow up post to my initial one, just by putting things down in black and white was enough to trigger ideas in my mind and give me a solution. I had slept on things though. My objective wasn't to get the contents of the Excel file into the data portal. I'm basically reading the contents of the excel file just like it is a text file. Depending on what I find I then do other wonderful and weird things with whatever is already in the data portal, but in very simple terms I change the names of channels and move them to different groups if necessary.

Thanks for your input and until next time good luck always.

Regards

Matthew

[MajoCuellar]

Hi!

I actually have the same problem, I just wanted to know how did you solve it. 

I just want to read one cell of my excel document, but I don´t know how.

Thank you 🙂

[Brad_Turpin]

Hi Majo,

What DIAdem version are you using?  There's a new Excel file reading object in DIAdem 2019 that would be the easiest way to pluck out the value of a particular cell in a particular *.xlsx or *.xls file.  If you're using an older version, we'd need to resort to an Excel DataPlugin or perhaps use the Excel ActiveX objects.

Brad Turpin

Senior Technical Support Engineer

National Instruments