LabVIEW and Excel - ActiveX or ADO

[Graeme]

Hi all,

I've put together a subVI that writes a row of data to an Excel worksheet using ActiveX. It works fine but it's a bit slow. It takes a few seconds for the VI to complete, although the row only has a few tens of columns. This is not a show-stopper but it doesn't give the user a slick experience in the top level VI that the subVI's used in. I was wondering, if I attempted the same using ADO would I see a significant improvement in speed? If you're thinking why don't I just try it, it's because I'm rather new to ADO (and ActiveX for that matter) and it would take me a while to get an Excel ADO solution going for myself, although I successfully use ADO to communicate with Access. I don't mind doing my own donkey work but it'd be nice to get some speed comparison information between the two approaches from those more in the know than myself before I attempt to flog a potentially dead horse. As ever, many thanks.

Regards.

[asbo]

ADO will be faster, but at the expensive of formatting options. If all you need to do is dump data into a spreadsheet, ADO is probably the right way to go. I don't think you can really modify files using ADO, so if you're starting from a template that may be a deal-breaker. The automation interface is for just that - automating tasks (even if it's just importing data) that you need the Excel feature set to accomplish (charts, highlight, find, sort, and so on).

It would be interesting to see how much faster ADO was, though.

Also, do you have Excel visible while operating on the spreadsheet?. I've noticed that tends to slow it down significantly.

[Graeme]

Hi asbo,

Thanks for your reply. Excel's not running when I'm doing all this, not to my knowlegde anyway. I attach my ActiveX subVI for reference but I think it's doing the best that it can. I've heard others say that ADO is faster, which is why I asked. I'm not worried about formatting as all I want to do is read data and write data to an Excel file.

Regards.

AXExcelWrite2DArrayWsheet.vi

[hooovahh]

Try starting Excel first (just have it open) and then run your application and see if that helps with speed. If it does I guess you could start Excel (if it isn't running already) at the beginning of the application. If you are worried a user may mess with Excel and close it you could hide Excel from the taskbar. Never done this programatically before but I think it is a user32.dll function.

I wonder if the TDMS addon for Excel uses ADO. I noticed it has bolding which according to asbo it can't do with ADO. I just mention it because it imports to Excel relatively quickly. I had one file that had 250 workbooks, 3 columns per workbook and 5000 rows per column and it only took a few seconds to import.

[asbo]

I'm not worried about formatting as all I want to do is read data and write data to an Excel file.

Then I have to ask - why are you using Excel at all? If you have no need for formatting, why not write to a standard CSV/TSV/whatever? Just want to make sure you're not skinning that cat unnecessarily

Try starting Excel first (just have it open) and then run your application and see if that helps with speed. If it does I guess you could start Excel (if it isn't running already) at the beginning of the application. If you are worried a user may mess with Excel and close it you could hide Excel from the taskbar. Never done this programatically before but I think it is a user32.dll function.

Opening and closing Excel are almost certainly the longest parts of your process. If you want to hide this from the user, open Excel while your app load initially and close it when you exit, hanging on to the reference in the interim. And there's no need to mess with user32.dll, Excel has a Visible property.

I wonder if the TDMS addon for Excel uses ADO. I noticed it has bolding which according to asbo it can't do with ADO. I just mention it because it imports to Excel relatively quickly. I had one file that had 250 workbooks, 3 columns per workbook and 5000 rows per column and it only took a few seconds to import.

Since that's an add-in, I expect it has (more) direct access to Excel functionality, possibly through VBA or something like that. Since there's no ActiveX layer to go through, it's much quicker.

[Graeme]

Hi again asbo,

Good questions! Well, as ever in these things, I'm stuck with Excel due to hand-me-down legacy reasons. Also, I like a challenge!!! I guess I'll just have to try and develop some ADO Excel stuff on my own and see what advantage it gives me, if any. Interestingly, I see there's loads of stuff about ADO and Access, MySQL etc but little about ADO and Excel, at least with respect to LabVIEW. I've found a bit but any pointers would be most welcome.

Regards.

[asbo]

I think I went through the same trial at one point, not for speed, but because we were having trouble with displaying the report in our UI and didn't want to buy a license for each rack. I doubt the ADO code is still floating around but I'll have a look and see if I have any complete examples left over.

Easy starting point for you, though: drop a new Automation Open node, right-click it, and choose Select ActiveX Class > Browse. Use this dialog to find the Microsoft ActiveX Data Object class. There should be 5 or 6 objects you can create (Connection, Recordset, Command, and the like). Now that you know where to get those, googling "ado excel" will give you plenty of information on getting started.

[Graeme]

Hi asbo,

Thanks for replying again. Well I took your advice and Googled as you suggested, and it turned up trumps! I found a cracking little site that explained all about ADO and Excel in terms that even I could understand. It even had simple examples albethem text based, though it was simple to follow. I hadn't realised that Excel was a database, or at least via ADO could be treated as one. I only had to slightly tweak the SQL stuff in my ADO MS Access VIs that I'd written to get them to work with Excel. I think I might change those to handle both Access and Excel based on the target's file extension perhaps.

Incidentally, I did a little experiment. I created a simple three-column two-row Worksheet in Excel by hand. I ran my ActiveX Excel Worksheet Reader VI on it and it took 2-3 seconds to return the 2D array. I tweaked my ADO MS Access table reader appropriately (with my newly gained knowledge!!!) then ran that on the Excel Worksheet and it returned pretty much instantaneously. So there you have it. ADO it is for me from now on. Obviously there is some overhead in in ActiveX. A guy I know who does databases for a living tells me the ActiveX layer sits above ADO??? A bit beyond me but I'm happy I'm moving forward now.

Thanks so much to LAVA and to you asbo. I just learn so much cool stuff here it's untrue.

Regards.

[asbo]

Glad to hear you got something working for you If you have a generic example to post here for the next person to come across this, that'd be great.

[Graeme]

I haven't access to my code at the moment from where I'm posting but I'll upload it as soon as I can. It would be the least that I could do.

Regards.

[Graeme]

Sorry for the delay. Here's my ADO database table reader. It's a bit of a mish-mash and uncommented I'm afraid, but make of it what you will.

Regards.

ADOReadFromTable.zip

[Jacemdom]

Here is very simple VI to read data from Excel using ADO.

Excel ADO Query LV8.vi