Excel Com port communication 16

[malforg]

Can anyone help with communicating to a COM port through Excel (on a machine that does not also have Visual Basic installed)?

I've tried downloading MSCOMM -- Excel recognizes the reference to MSComm32.ocx, but I get an error when trying to use it ("Run-time error '429': ActiveX component can't create object" ).

These are the lines of code that create the error (omitting the remainder of the subroutine). The line "Set CPort..." is what causes the error to pop up:

Sub ComPortTesting()
   Dim CPort As MSComm

   Set CPort = New MSComm 
...

This is really a two part question:

(1) Can this be done using MSComm? (Or is there some basic problem like a licensing/registry issue)

AND

(2) Is there another way to communicate with the Com port using Excel that doesn't require MSComm?

Thanks!
--Jason

 

[OzzieGeorge]

GVF

Thanks for the help. I tried the code you posted but because I send the "AT" string to the modem the OnComm event fires immediately and screws things up. If I try modifying the code the modem just hangs up on me. The other end must be looking for a signal I am not sending. Perhaps I should see if there is a comConnect constant.

 

[GVF]

Ozzie G

If you know a device is going to respond then maybe you should poll the port (use an OnTime event instead of OnComm). You can poll the port once per second.

The alternative is a loop with a built in pause. The danger with a loop is if there is no response from the device the code hangs in an infinite loop.

Either of these slows the process down to allow the device to respond with less likelyhood that the downloaded data will get truncated.

This code will poll the port. When something arrives in the port the loop is broken. If nothing arrives in the port you're stuck. I suppose you could put a counter in so it only ran a set number of times and then exited.

Do While userform1.MSComm1.inbuffercount = 0
TheWait = 1
TheTime = Timer
Do While Timer < TheTime + TheWait
Loop
Loop

 

[GVF]

OzzieG

What code do you have in the MSCOMM1_OnComm routine? It should include something like:

if userform1.mscomm1.commevent = comEvReceive then
'Call Something
end if

This forces OnComm to only act on receive events, not on your send events. You could add elseif statements to the above snippit to handle other types of events besides receive events.

Greg

 

[OzzieGeorge]

The biggest problem I am having at the moment is that the modems are doing the initial handshaking then immediately closing down the link. I used portmon to see what was happening and the results less the time and process columns are below. As I am new to this it doesn't mean a lot to me (By the way the word Programmer next to my name is from A pabx forum where I usually supply the answers).

I am wondering whether the problem is the serial port or the modem protocols? Do I have to use a command to hold a pin high or something?

IOCTL_SERIAL_GET_COMMSTATUS Winachsf0 SUCCESS
IOCTL_SERIAL_SET_WAIT_MASK Winachsf0 SUCCESS Mask: RXCHAR TXEMPTY CTS DSR RLSD BRK ERR RING
IOCTL_SERIAL_WAIT_ON_MASK Winachsf0 SUCCESS
IOCTL_SERIAL_SET_WAIT_MASK Winachsf0 SUCCESS Mask: RXCHAR TXEMPTY CTS DSR RLSD BRK ERR RING
IOCTL_SERIAL_WAIT_ON_MASK Winachsf0 SUCCESS
IOCTL_SERIAL_SET_WAIT_MASK Winachsf0 SUCCESS Mask: RXCHAR TXEMPTY CTS DSR RLSD BRK ERR RING
IOCTL_SERIAL_WAIT_ON_MASK Winachsf0 SUCCESS
IOCTL_SERIAL_GET_COMMSTATUS Winachsf0 SUCCESS
IOCTL_SERIAL_GET_COMMSTATUS Winachsf0 SUCCESS
IRP_MJ_READ Winachsf0 SUCCESS Length 14: ..NO CARRIER..
IOCTL_SERIAL_GET_COMMSTATUS Winachsf0 SUCCESS
IOCTL_SERIAL_SET_WAIT_MASK Winachsf0 SUCCESS Mask:
IOCTL_SERIAL_CLR_DTR Winachsf0 SUCCESS
IOCTL_SERIAL_PURGE Winachsf0 SUCCESS Purge: TXABORT RXABORT TXCLEAR RXCLEAR
IRP_MJ_CLEANUP Winachsf0 SUCCESS
IRP_MJ_CLOSE Winachsf0 SUCCESS

Any help gratefully recieved.

Regards

R4

 

[khanpur101]

Can anyone tell me where I can download cheapcomm.zip?

 

[mattj63]

The sites where I found Cheapcom.zip is no longer active. I would recommend MSCOMM instead. The information in this thread should allow you to get it working even if you don't have Visual Basic. Alternatively, search on Excel+ Com Port or something like that. I found a website that had an example of an Excel sheet with MSCOMM already present as a control. I then deleted everything but the control and started fresh. If the control is already on the spreadsheet, you can use it as long as you have MSCOMM.ocx on your PC. YOu can get that from MS.

 

[mattj63]

You can find an Excel Worksheet with the MSCOMM control on it at

http://www-ese.fnal.gov/D0Cal_TWG/.

Just delete all the code (but not the control) and start fresh. I couldn't add a second control to this so you're stuck with only 1 serial port.

 

[OzzieGeorge]

Guys

The answer to this problem is given earlier in this thread....

 

[OzzieGeorge]

Finally the light has dawned.....
All the code controlling the modem has to be on the form. I was trying to write the code in a module. Now that I am doing it on the form it is working.

Thanks all.

 

[dr249]

Thanks guys,
This thread has been very helpful in getting me started in the right direction. I am trying to communicate with a serial device with VBA, however I need to see how the device communicates first.
I downloaded portmon and tried it but it seems to lack the most basic feature... I want to see what ascii characters are being sent back and forth. In the "other" column it sort of shows this, but the first 8 characters or so are missing (the word "Length:25" for example) is covering them up. Is there any way to see what is being sent? Maybe a different port monitoring program?

 

[mattj63]

If you know the baud rate, parity, stop bits, data length and the type of cable required, a very simple way to monitor what your serial device is sending is using HyperTerminal which comes with Windows. You'll see all the ASCII characters that are sent although sometimes Hyperterminal has trouble with non-printing ASCII characters (like carriage return, linefeed). Often I will use Hyperterminal to record the data that a device sends and then write the parsing routine offline instead of having to sit in the lab in front of the instrument.

 

[tomdtomd]

I've found this thread quite helpful. I'm trying to communicate with a lab instrument by an RS-232 serial comm port from Excel VBA. I've downloaded MSComm using GVF's post of 27 May '05, and registered the file by the method of tbl (Jan 05). I got the control into my toolbox as suggested to farmcafe by tbl on 16 Feb. 05, but "the control could not be created because it is not properly licensed". I got some text to be inserted to the registry from the devhood site recommended by GVF (29 May '05), and tried saving it as a notepad file with a .reg extension as MisterCfromIT suggested on 18 Feb '05. It was identical to the first text he supplied as his example. I got the error message, "specified file is not a registry script. You can only input binary registry files". I'm working in Windows XP, not NT. I do not know if that is why his method does not work. Can someone help me insert the appropriate license information to my registry? I'm in unfamiliar territory in the registry so a little detail may be needed on my part.

 

[GVF]

tomdtomd

You can manually create the key. This should work....

HKEY_CLASSES_ROOT\Licenses\4250E830-6AC2-11cf-8ADB-00AA00C00905

Default:
kjljvjjjoquqmjjjvpqqkqmqykypoqjquoun

Greg

 

[tomdtomd]

Thank you for the key, GVF. I am not sure exactly how to manually insert it to my registry. Could you provide some details on how to do this?
Thank you.

 

[GVF]

tomdtomd,

Select <Start button||Run> and type in "Regedit"
navigate to <HKEY_CLASSES_ROOT\Licenses> and right click on "Licenses"

Select <New||Key> and type in 4250E830-6AC2-11cf-8ADB-00AA00C00905

Hit enter

In the rightside window, right click on "Default" (for the key you just created)and then select "Modify". In the "Value Data" textbox type kjljvjjjoquqmjjjvpqqkqmqykypoqjquoun

Close the dialog box and close regedit.

Greg

 

[Hypnofrog]

To everone, thanks for all the information in this thread, I have been able to successfully create an application using mscomm as instructed so I simultaneously run 2 Excel files each pointing to a different port and exchange data on command through a null modem.

What I really want to do is initiate the transfer by a trigger read in as a bit by one of the two applications through the parallel port LPT1. I am looking for the simplest way to read the parallel port in bidirectional mode by polling. I am running under XP. If anyone can point me in the right direction to do this I would appreciate it.

Thanks again for this great thread!

Rob

 

[GVF]

Hypnofrog,

I haven't tried communicating with a parallel port, but I believe that MSCOMM only works with serial ports 1 thru 16. I suggest starting a new thread in the VBA forum (and maybe in the VB forum as well) and see if anyone can drop you a code snippet that makes the correct Windows API calls to talk to a LPT port. Good Luck.

Greg

 

[Hypnofrog]

Thanks for response,

I realized I may be able to pull this off by installing a generic text only printer using lpt1: and then using VBA to make it the active printer. I may then be able to check printer status and thus get a read of the input bits. Not sure if it will work or the overhead, but I'll try it over the next few days and let you know for posterity.

I really appreciate your earlier work with the MSCOMM, it helped me quite a bit. In my implementation, I used the multimedia timer to me let me know 25ms after the receive buffer stopped receiving characters. Then I just read in the buffer and so far it looks pretty solid.

 

[tomdtomd]

Hypnofrog,
You may want to try the following link to the MS Knowledge Base:

http://support.microsoft.com/default.aspx?scid=kb;en-us;823179

(HOW TO: Access Serial Ports and Parallel Ports by Using Microsoft Visual Basic .NET)
I'm not sure if this qualifies as the "simplest way" but I hope it helps. Perhaps someone more expert than I may be able to offer additional advise on this article, and how to apply it to your problem.

tomdtomd

 

[ETID]

Old Excel (undocumented) was able to read and write to lpt1:
using

something like...

open "lpt1:" for output as #1
print #1, chr$(12)
close #1