Excel and MSCOMM control 2

[mattj63]

I have a spreadsheet I originally created in Excel 2003 which used the MSCOMM control to read data sent to the serial port.

When I upgraded to Excel 2007 this spreadsheet still worked fine. Lately our IT installed some updates on my computer and now this spreadsheet cannot use the MSCOMM control. I get the message, "Can't exit design mode because control 'MSCOMM1' cannot be created".

An ideas of why an update to the OS would break a spreadsheet? My OS is Windows XP Pro. Is there a way to fix this problem?

 

[mattj63]

I haven't solved the MSCOMM.OCX problem but did find the following website which provides code to do serial communications using WindowsAPI functions. This approach eliminates the need to install and register MSCOMM.OCX

I was able to get this code to work in Excel 2007 with my operating system.

http://dev.emcelettronica.com/serial-port-communication-excel-vba

 

[GVF]

I had the same problem under Vista. I thought it was a Vista thing, but it was Internet Explorer blocking the ActiveX control. If IT pushed out an update to Internet Explorer you might have the same problem.

Here is how I fixed it...
Use regedit and then search for:
HKEY_LOCAL_MACHINE\Software\Microsoft\InternetExplorer\
ActiveXCompatibility\{648A5600-2C6E-101B-82B6-000000000014}

If the value of the compatibility flag is 400, there is your problem. Modify the compatibility flag to a value of 0.

If the value is already 0 then your problem is elsewhere.

Greg

 

[mattj63]

Thanks. This worked. The MSCOMM control is visible and I can edit in the code.

How did you come up with this? Random guess?

 

[GVF]

It was kind of random and kind of inspired... I was having the same problem and was googling for {"MSCOMM32.ocx" Vista}. All the sites complained about the problem and the solution was universally that MSCOMM didn't work in Vista but should work, so keep trying to re-register it. That was obviously not the fix. I changed the search to {ActiveX Vista} and came across a site that described how IE blocks ActiveX controls that are not installed by a trusted source and how to fix the particular ActiveX that they were concerned with. That was the key. I used their fix for MSCOMM and now you know.
It took hours to come up with the fix, so thanks for the star.

 

[GVF]

Oh Yeah, one more thing. After changing the registry key...if you export the key from the registry a *.reg file will be created. Name it "Enable MSCOMM.reg" or something. After installing your project on another machine - copy the .reg file over and double click it. A couple of warning dialogs will appear explaining the scary things that can happen when making changes to the registry, but the line will be altered and MSCOMM will be available on the other machine as well. I don't know about programming to it on the other machine (licensing issues have always been vague regarding using MSCOMM with VBA since it only shipped with FoxPRo and VB) but it can be called and used by your project provided of course that you have installed mscomm32.ocx on the second machine and registered it there as well. The .reg file will provide you with a way to quickly fix the problem if your IT department pushes out another update and IE bans the ActiveX control again.
Once upon a time I wrote an extensive FAQ regarding MSCOMM. It has been removed from Tek-Tips. I think the issue was that I addressed the licensing issue and provided a workaround that may have been deemed illegal. Darn useful little ActiveX though don't you think?

Greg

 

[mattj63]

The MSCOMM is certainly useful. I've interfaced several minor pieces of lab equipment (balances, titrators) through Excel. Sometimes the vendors provide software but usually it has way more bells and whistles than one needs for routine measurements.

I even interfaced a 20-year old spectrometer which originally ran in DOS. When our last Win95 computer went belly up, the DOS program would no longer run reliably in the command prompt window for newer OSs. MSCOMM, VBA and Excel allowed me to keep the instrument running.

 

[GVF]

Mattj63,
Do you use the shareware program "Port Monitor"? It is excellent. Lets you see what a device is sending across the port. Consequently you can figure out how to program for it. Old or new, loadcells, timers, scales, whatever, as long as it has a serial port on it you can dump data into Excel. Just play with the baud rate and other settings until you get the machines to talk together. You don't need to find manuals for the older equipment.

 

[mattj63]

D'OH The compatibility flag was set back to 400 sometime last week, disabling the ActiveX again. I may stick to the WindowsAPI version of the solution since it isn't affected by the registry change but it is more cumbersome than MSCOMM.

Thanks anyway about the tip about the compatiblity flag.

I thought about possibly editing the security settings in IE, but those are disabled on my computer. I'm not even sure how I would make my computer a trusted zone.

P.S. The PortMonitor site says it contains Malware on my McAfee sitemonitor program. I wonder why?

 

[GVF]

I can't explain a problem with the Port Monitor site. Try to contact them. It is too useful to just pass on.

Your problem with IE is an issue. Here is a fix but you still have to run it when you notice that MSCOMM is not responding.

Run regedit and follow the previous instructions to set the flag to 0.

Click on the key name {648A5600-2C6E-101B-82B6-000000000014} in the left window frame.

From the Registry menu select "Export Registry File"

Save the file "Enable MSCOMM.reg". It must have a .REG file name extension.

Start Windows Notepad.

Paste in the following batch code at the top of the page using the correct path and file name for the .REG file you created.

@echo off
start regedit.exe "c:\My Folder\My Path\enable mscomm32.reg"
end

Save the file as Enable MSCOMM.cmd (or .bat).

Double clicking on the CMD file will run regedit, which in turn will run the .reg file, which in turn will set the flag to 0 again.

 

[GVF]

You can download Port Mon from here...

http://technet.microsoft.com/en-us/sysinternals/bb896644.aspx

The site states its for Win2000 but I run it on XP as well. On Vista you probably need to run it as an administrator.