LabVIEW Excel VBA node Help missing

[Bob_Schor]

Several years ago, I wrote some LabVIEW code to read from and write to Excel SpreadSheets.  I'm not certain what version of LabVIEW, Windows, or Microsoft Office I was using, but if I clicked on a Node, like the Range node in the example shown below, LabVIEW would offer me "Help for Range", and something would pop up (I think from the VBA Help) that explained the parameters and how it all worked.

I'm now hoping to use and extend these routines.  I'm running LabVIEW 2011 and 2012 (32-bit) on a Windows 7 (64-bit) PC, with MicroSoft Office 2010 installed (I chose to install VBA when I installed Office).  I don't know if it matters, but Visual Studio 2010 is also installed (and was installed after Office).

However, when I now right-click on the Range Node and ask for help, I get the following window:

They have the correct folder setup (that is where the Office 2010 files/folders are located), but there is no VBAXL10.CHM file.  There is a file one level down, ...Office14\1033\VBAOWS10.CHM, but that is the only file starting with VBA installed on my system.

There (logically) must be a way to get back the functionality that existed in the older version of LabVIEW/Windows/Office I used, probably 3 years ago, that had a working VBA Help system integrated into LabVIEW (so that when LabVIEW offered Help for Range, you got ... Help for Range, instead of an Error Message).  Any assistance would be greatly appreciated.

P.S. -- I did try downloading the "missing" file from the Web and putting it where LabVIEW said it wasn't, but that didn't do anything.  

Bob Schor

[tst]

I would suggest just opening the help file (directly or through Excel) and finding the relevant properties and methods there. Unless you're going to be dealing with lots and lots of code, this might make it easier.

Also, another useful tip is to record a macro of your desired actions. The macro is actually VBA code which you can then replicate in LV (or just call the macro).

One thing to consider is that in general, interacting with Office through the ActiveX API can easily break between computers, so be aware that if you're building an application, it might not function.

[Bob_Schor]

The frustrating thing is that, several years ago and with older versions of LabVIEW and Office, writing LabVIEW code to do "interesting" things with Excel was simple, didn't require writing a VBA macro, opening Excel and trying to write actual VBA code, etc. was not necessary -- one could plunk down a LabVIEW property or invoke node, wire in an appropriate Office Object (like _WorkSheet), and use the LabVIEW Help (we are, after all, working in LabVIEW) and get LabVIEW to connect to the appropriate Microsoft .chm file, which had all of the Help necessary to really get LabVIEW to do some amazing things with an Excel spreadsheet.

Yes, LabVIEW has new versions, but it still has "hooks" into some Microsoft code (see the Error message) -- what is necessary to make these links functional?  Shouldn't this be NI's responsibility?  I'm willing to download "missing pieces" (like the appropriate VBA Help File), but need some instructions to know what to download and how to "hook" it into LabVIEW and the NI-supplied Office Toolkit.

BS

[nathand]

---

@Bob_Schor wrote:

The frustrating thing is that, several years ago and with older versions of LabVIEW and Office, writing LabVIEW code to do "interesting" things with Excel was simple, didn't require writing a VBA macro, opening Excel and trying to write actual VBA code, etc. was not necessary -- one could plunk down a LabVIEW property or invoke node, wire in an appropriate Office Object (like _WorkSheet), and use the LabVIEW Help (we are, after all, working in LabVIEW) and get LabVIEW to connect to the appropriate Microsoft .chm file, which had all of the Help necessary to really get LabVIEW to do some amazing things with an Excel spreadsheet.

---

You can still do all of that.  Tst was just saying that sometimes the easiest way to determine the correct set of properties and methods is to generate a macro and look at the functions it uses.

I have the same problem with the missing VBA help, and I blame Microsoft.  On my current machine with LabVIEW 12, I don't even get an error message - nothing at all happens when I ask for help on an Excel method.  I suspect, with no proof, that Microsoft changed the location of the help file but still publishes the old one, preventing LabVIEW from finding it.  It's not clear to me if you're actually using the Office Toolkit - if you're using property and invoke nodes, then you're working directly with the ActiveX components that Microsoft publishes, and not the NI-supplied Office Toolkit (which is an easier-to-use wrapper around the ActiveX library).  In any case, the help is still available (I usually use the online version from MSDN), you just can't launch it directly from within LabVIEW.

[tst]

---

@Bob_Schor wrote:

...writing LabVIEW code to do "interesting" things with Excel was simple, didn't require writing a VBA macro, opening Excel and trying to write actual VBA code, etc. was not necessary...

---

Just to clarify - I was suggesting recording a macro, not writing one. When you record a macro, Excel automatically translates the actions you do in the Excel UI to the appropriate VBA code. I think this is easier simply because you don't have to do digging for the right code. This has nothing to do with whether the data from Office is or isn't available in LV.

[Bob_Schor]

Thanks for the comments, guys.  I actually found the "system that works" -- here is what happens when I ask for help on the Range Node on another PC (to be described shortly) --

Now, this is useful!  I realize that NathanD is right -- I'm (probably) not using the LabVIEW Office Toolkit, but using Active X calls directly.  When I first started doing some Excel work (dragged kicking and screaming, because my colleagues wanted to control their experiments using an Excel spreadsheet to organize their stimuli and do simple data analysis), I tried the LabVIEW Toolkit, but found it quite slow, with Active X an order of magnitude faster (and more capable).  I even developed a system whereby I could create a "LabVIEW spreadsheet" as an array (rows) of clusters (columns), where the data types in the cluster were things like strings, integers, floats, timestamps, and booleans, and write a LabVIEW/ActiveX set of routines that would write the array out as an entire WorkSheet, using the names of the Cluster Elements as the Excel Headers and the contents of the clusters as the row elements (handling the type conversion as it went).  Made for much cleaner LabVIEW code (though there was quite a bit of "behind the scenes" work with variants ...)

Anyway, the System That Works is a Windows XP PC with Office XP (which, incidently, seems to have the internal name Office 10, the same name that seems to be linked to the VBA Help File) and LabVIEW 8.6.  I'm going to try to build a "clean" VM with just these component and see if I can duplicate my success.  If so, I'll try keeping Office the same and upgrading LabVIEW -- maybe that's the answer.

Nathan, I like the idea of using the Help directly from MSDN (though it is handier from LabVIEW, and the more-relevant Help seems to be selected).  Can you point me to How To Get Started?

Bob Schor

[nathand]

---

Bob_Schor wrote:

Nathan, I like the idea of using the Help directly from MSDN (though it is handier from LabVIEW, and the more-relevant Help seems to be selected).  Can you point me to How To Get Started?

---

I'm not sure what you mean by "How To Get Started" but the top-level documentation for VBA for Excel is at http://msdn.microsoft.com/en-us/library/aa272254%28v=office.11%29.aspx

EDIT: also, you can download the entire help from http://www.microsoft.com/en-us/download/details.aspx?id=7273

(Office 2003, I don't know if it's changed much in Office 2010 but poke around and you might find a newer version)

[Bob_Schor]

Thanks, Nathan.  The compiled Help file in your last reference is (I presume) the VBAXL10.chm file that seems to be the "Final Word" from Microsoft on VBA (there's nothing newer in Office 2010).

I'm doing some Experimentation.  There are Three Possible Culprits -- Microsoft OS (Windows XP vs Windows 7), Microsoft Office (Office 2002, aka Office 10, to Office 2010, aka Office 14), and NI LabVIEW (Versions 8.6 and higher).  As it happens, I have a pretty empty VM with Windows XP, Office 2003 (Office 11), and no LabVIEW.  Thought I'd try to install LabVIEW 8.6, and start with a system "one step" (Office 10 -> Office 11) above my older system that worked.

Very interesting result.  As I said earlier, Office 2003 comes with the VBA Help Files, and if I open them and search, say, for Range Properties, I get the same information that LabVIEW gave on my "working" system when I click a Range Property Node and ask for Help for Range.  However, when I try to get the same information in the same way, i.e. from within LabVIEW, I get an error message saying "An error has occured in the script on this page".  Curiously, it shows me almost the same "page" that I saw when I looked at the page "manually" by opening the Help file and looking up the Range property -- only some of the links seemed incomplete.

     Just for fun, I copied the .CHM file to the corresponding Office 2010 folder on my Windows 7 machine, and tried it there with LabVIEW 2011.  A Big Fat Nothing -- asking for Help on Range gave me no error, and no information.  So I opened the Help File, navigated to Properties, and asked for Range.  I got the following:

 

Note that, with the exception of the appearance of the Window and the details of the path to the file, this is exactly the same error message I got on my XP/Office 2003/LV 8.6 system when I asked LabVIEW for help.

I haven't done the tests yet, but I'm willing to bet a dime (not a big gambler!) that (a) Windows 7 will always cause a failure, even when opening the Help by hand, (b) Windows XP will work in XP, and (c) the XP-XP combination will work with newer versions of LabVIEW (maybe even LabVIEW 2012).  Will report back when I have the data.

Meanwhile, my next step is to upgrade the LabVIEW in my VM to LabVIEW 2011 (the system I'm currently using for Excel-related development) and use that as my "debugging tool" while I develop.  [Of course, this assumes that 2011 will work, but that's Test (c), above, in part].

Thanks to all for your help.

Bob Schor

[nathand]

Well, with the information from that error, you might be able to find a solution.  See if any of these links help:

http://answers.microsoft.com/en-us/office/forum/office_2003-excel/an-error-has-occurred-in-a-script-...

http://support.microsoft.com/KB/892675

http://support.microsoft.com/kb/902225

[Bob_Schor]

Nathan,

     Thanks for the links.  I spent quite a bit of time tracking these down, and trying one or more of the suggested fixes.  The underlying problem these seem to be addressing is a "feature" Microsoft introduced in one (or more) of their updates that results in an incompatibility between two of their components, hhctrl.ocx (which is the Microsoft HTML Help Control, an ActiveX file that provides the Web interface to the .chm file) and user32.dll (which handles the Windows API).

     After trying several of the fixes to no avail, I checked the versions of these files on my Test system (Win XP, Office 2003, LabVIEW 8.6) -- both hhctrl and user32 have dates of 14 Apr 2008 (so are presumably the "original" versions from the WinXP install) and have version numbers of 5.2.3790.4110 and 5.1.2600,5512, respectively.

     So I looked at the "system that works" (WinXP, Office XP, LabVIEW 8.6) and it has exactly the same versions of these two files!  Makes it look like the Culprit is Office.  I'm going to try removing Office from my Test system, reinstalling Office XP, and see if that fixes the problem.  If it does, I'll install LabVIEW 2011 and try again.  If it still works, I'll build a Win 7, Office XP, LV 2011 system for a final try.

     Since this is a "reference platform" for developing some LabVIEW code that can do clever things with Excel spreadsheets, as long as the code works properly at run time, it is only a small pain-in-the-rear to use a special platform for Development Work just for this part of the project to take advantage of the convenience of the LabVIEW Help system working with ActiveX Objects.  And, if it does prove to be too much of a pain, I can always open the .chm files "by hand" (though on a Windows 7 system, I'll still get the annoying error messages and broken links).

     I'll post my results ...

BS