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

 

[ETID]

excel by itself does not support the comm port, but it does (undocumented) support communications via the lpt port

it's something like (but don't quote me) open lpt1: for random as #1

 

[tbl]

I have a very simple solution to using the Com ports via Excel. I have a DLL and a form that I use with a few preset commands. These are more or less in the public domain, and work very reliably. I have more than 10 applications running for many years. If anyone is interested I will publish the code that I use.

Richard

 

[malforg]

Yeah, I'd be interested in seeing the DLL that you use!

The only things that I've found that work are to (1) install a VB application that uses MSCOMM, or (2) install and uninstall VB itself. After either of these steps, the MSCOMM ActiveX control works. So, if you have a more direct way to allow COM port communication, I'd love to see it!

--Jason

 

[ETID]

Me too

 

[tbl]

Playing with MSCOMM is definitely not for the fainthearted. I use a simple solution called Cheapcom which is as far as I am aware Freeware. It has an installer that installs the DLL and a simple set of commands for performing the various COM operations. I have written a standard Excel book that contains a sheet with all the settings, Baud, Parity, etc., and I just tailor this to suit the application that I am writing. It is possible to use any Serial Com Port with both ASCII and Hexadecimal.

I quote from the Cheapcom help file.
***********************************************************
CheapComm was written to permit Visual Basic, Excel, and VBA (Visual Basic for Applications) users to use the Photon Technology International (PTI) serial port devices without requiring purchase of the professional version of Visual Basic (that comes with MSCOMM32, a decent serial port OCX), or an expensive serial port DLL that provides many more features than the SIDs require. This control has also been used with to control other serial port devices. This control can be used with any language that works with OLE controls (OCX controls).

CheapComm is a means of using the serial port for instrument control. It is NOT meant for very sophisticated serial port operations. To this end, CheapComm only exposes the most basic operations of the serial port. It will send or receive ASCII or binary information, configures the port for basic communications, and clears the port buffers, and that’s it!

The command set can be summarized in only a few lines:
OpenCommPort(Serial_Port as String, Serial_Frame as String) as Boolean
CloseCommPort()
GetNumBytes() as Integer
GetStringData(Buffer as String, Numchars as Integer) as Integer

GetBinaryData(Buffer as Byte array, Numbytes as Integer) as Integer
SendStringData(Buffer as String) as Integer
SendBinaryData(Buffer as Byte array) as Integer
SendSubArray(Buffer as Byte array, NumBytes as Integer) as Integer
ClearCommPort()

That’s all the functionality this control provides, but these few procedures can do a lot. For example, a timer can be used to poll the serial port using GetNumBytes(). When the number of bytes reaches a certain value, the data can be removed from the serial port.

*****************************************************

For more info give me an E-mail address and I will send a sample application.

Richard

Richard

 

[malforg]

Thanks for your help Richard!

I found Cheapcomm.zip at this website:

http://ourworld.compuserve.com/homepages/jacksilver/software.htm

 

[tbl]

A pleasure Malforge,

I once started down the MSCOMM route and found it quite beyond me ! I think if you are a professional programmer then it certainly offers everything that you ever could require. My trouble was that I never got going ! I only wanted to send an ASCII string of four characters to Com 1 and see what came back, but was immediately lost in the technicalities.
If it is any use to you, I have written applications that use both ASCII and HEX , and have made my own routines for converting between DECIMAL, HEX, and ASCII as well as a converter for floating decimal.

regards,

Richard

 

[Mechie]

I realize this is an old thread, but I am trying to do the same thing. I've searched for info on CheapComm, and I downloaded the installation package, but there is no sample code with it, so I'm not doing something right.

Can anyone either post their sample code including declarations, or email me some code?

Thanks

 

[tbl]

In the meantime I have progressed somewhat with my programming and can now use a variety of dlls and ocxs including MSCOMM.
Cheapcom is brilliant if you just want to do what they offer in the text I posted above. You have to remember that these objects only run from a form. In this example I have created a form called Form1 and using TOOLS/Additional TOOLS have added the Cheapcom Object to the form. You don't need to go further with the form as you will never see it displayed. This code was used to communicate with a microprocessor called FPO which required the HEX string to give a response.

Sub Read_FPO_Registers()


'This routine is used to send a single fixed command, which returns
'the binary state of all registers.
'This is the routine that is used for reading the state of the I/Os

Dim PortSettings As String, BinaryResponse As String, ResponseString As String
Dim Interval As Integer, i As Integer, DataCol As Integer
Dim ArrayOut(0 To 19) As Byte 'Set the length of transmission array for this case
Dim ArBytes(0 To 229) As Byte
Dim strFPO As String
Dim j As Integer
Dim fStartTime As Single
Dim fCurrentTime As Single
Dim bIsPortOk As Boolean
Dim nNumBytesWaiting As Integer
Dim nNumBytesReceived As Integer
Dim SampleTime As Date
Dim CommsOK As Boolean
Dim TimeToSave As Variant
'kill timer to stop multiple timers running

On Error Resume Next 'sometimes the timer is NOT running
Application.OnTime SampleTime, "Read_FPO_Registers", , False
On Error GoTo 0



Sheets("TimeAnalysis").Range("SaveTimerTotal") = Sheets("TimeAnalysis").Range("SaveTimerTotal") + 1

Application.ScreenUpdating = True
CommsOK = True
' Open selected serial port with baudrate 19200, Parity Odd, 8 databits and 1 stop bit
'("COM1", "19200,o,8,1") These settings are in Public Declarations
PortSettings = CStr(BaudRate & "," & Parity & "," & DataBits & "," & StopBits)
'Open COM port with these settings
bIsPortOk = Form1.CheapComm1.OpenCommPort(ActivePort, PortSettings)
'if port can't be opened successfully, end program
If bIsPortOk = False Then
MsgBox "Can't open serial port. Ending Program"
End
End If

Form1.CheapComm1.ClearCommPort 'clear buffers

'Define array for the FPO string
ArrayOut(0) = DecimalCode("%")
ArrayOut(1) = DecimalCode("0")
ArrayOut(2) = DecimalCode("1")
ArrayOut(3) = DecimalCode("#")
ArrayOut(4) = DecimalCode("R")
ArrayOut(5) = DecimalCode("C")
ArrayOut(6) = DecimalCode("C")
ArrayOut(7) = DecimalCode("X")
ArrayOut(8) = DecimalCode("0")
ArrayOut(9) = DecimalCode("0")
ArrayOut(10) = DecimalCode("0")
ArrayOut(11) = DecimalCode("0")
ArrayOut(12) = DecimalCode("0")
ArrayOut(13) = DecimalCode("0")
ArrayOut(14) = DecimalCode("0")
ArrayOut(15) = DecimalCode("0")
ArrayOut(16) = DecimalCode("*")
ArrayOut(17) = DecimalCode("*")
ArrayOut(18) = DecimalCode("CR")

nNumBytesSent = Form1.CheapComm1.SendSubArray(ArrayOut, BytesToSend) 'send FPO array
'Form1.CheapComm1.SendBinaryData (ArrayOut) 'send FPO string

'get the current time (seconds since midnight)
fStartTime = Timer

Do
'Give the program time to read the input buffer
nNumBytesWaiting = Form1.CheapComm1.GetNumBytes
fCurrentTime = Timer 'get current time
'if no reply within 2 sec, exit
If fCurrentTime - fStartTime > 2 Then
'MsgBox "No Reply from Matsushita FPO PLC !", vbCritical, "Reply Error"
Form1.CheapComm1.CloseCommPort 'close Comport
CommsOK = False
GoTo BYPASS 'leave the loop if no reply, and write entry in log
End If
Loop Until nNumBytesWaiting = 13 'Change this value to suit number of words
'Select the number of bytes to be removed from buffer for processing
nNumBytesReceived = Form1.CheapComm1.GetBinaryData(ArBytes, 13)

Form1.CheapComm1.CloseCommPort 'close Comport
End Sub

I have written Hex to Decimal converters so that I can program in what the instrument expects to receive and let my converter look up the decimal code automatically. This helps with debugging.

Hope this helps,

Richard

 

[Mechie]

Thank you very much! That is a lot of great information.

In my time today, I managed to figure out enough to put the CheapComm on a form, and call it as you show.

My problem now seems to be something with the controller connected to the port, or CheapCommm isn't written correctly. I have a small controller that takes ASCII commands to read some analog voltages. I can get the port open, I can verify that the command was sent, I can see data on the input buffer, but I can't get it out. The Help file is written wrong-- showing GetString() instead of GetStringData(). (That took about 4 hours to figure out (ah.. Object Browser.. I get it.) So I'm not sure I'm using GetStringData or GetBinaryData the way they supposed to be.

The key thing is that I don't get any errors-- I just don't get any data either.

Your code does give me a few more ideas to try.

Have you found any other way to do this without CheapComm? Somebody else mentioned that you can just print to the port, but I couldn't get that to work either.

Anyway, thanks again for your reply. I'll post again with my results-- good, bad, or indifferent.

 

[tbl]

If you use the F8 trick to step through your code, and Portmon from Sysinternals (free) to monitor what your port is doing, you can see the values of all variables while the code is executing.
You can monitor the value of
nNumBytesReceived = Form1.CheapComm1.GetBinaryData(ArBytes, 13)


Richard

 

[javanic]

I've been looking through these posts on Tek-tips trying to find info on Com Port programming. I've since downloaded CheapComm and installed it. The help file refers C++ users to the CPTIComm32 class that supposedly comes in some source file. However, I can't find this file, nor any source code for that matter.

Does anyone out there have the source code that is referred to in the CHEAPCOMM Control Help file?

Thanks,
javanic

 

[javanic]

I noticed something strange about CheapComm. Has anyone else seen this:

bool b = cheapcomm1.OpenCommPort("COM1", "9600,n,8,1")

b is always true even if CheapComm displays a pop-up saying that the port could not be opened. This happens regardless of the port opened or the serial frame settings.

- javanic -

 

[mattj63]

I have the same problem as javanic. The OpenCommPort command is always true, even if the routine has an error opening the port.

Nevertheless I was able to use CheapComm to read a 400 character report from a laboratory titrator and insert desired information into a spreadsheet. The code posted by tbl was useful although I'm reading strings not bytes. My problem was that I don't know exactly when (or how much) data will arrive so my macro polls the serial port and gathers data until I detect the character string marking the report end.

 

[tbl]

I have now been using both CheapComm and MSComm for VBA serial applications for many years and have a substantial number of industrial programs running. I have come to realise that MSComm is more reliable than CheapCom and gives more consistent results. To install MSComm it is necessary to register the DLL and indicate to Excel VBA that it is being used in your code. If anyone needs to know how to do this I will post exact instructions. I have got an enormous amount of working code that covers both binary and ASCII serial comms and will be very happy to publish chunks for specific applications.

Richard

 

[javanic]

Richard,

Please do post detailed instructions on how to register MSComm. I've been searching for that info on and off for some time.

Thanks,


- javanic -

 

[tbl]

1.) Copy the files you would like to register to [C:\WINDOWS\system32](in this case MSComm32.ocx)
2.) Go to the command prompt Start --> Run --> cmd
3a.) To install/register the file type in: regsvr32 file.dll or regsvr32 file.ocx
3b.) To uninstall the files type: regsvr32 -u file.dll or regsvr32 -u file.ocx
4.) Some type of message should be displayed that says you successfully registered or unregistered the file

Hope this is useful

Richard

 

[mattj63]

I tried this. It allows the mscomm control to be added to the control toolbox but when I try to put it into a form for use I get the error "Control could not be created because it is not properly licensed".

Looks like if you don't have VB installed, then you can't use this control and have to stick with CheapComm.

Don't know if this is just on the computer you're creating the program or on any computer. I've used MSCOMM from VB to write an application and then installed the application on other computers so I guess as long as you're not coding, you don't have to have VB installed. Seems compiled software can use the OCX even if it isn't "properly licensed" since it shows up on most computers even if VB was never installed.

 

[tbl]

This is certainly not my experience so I guess that there are varius versions of MSComm. I have used MSComm instead of CheapComm on a whole range of computers and never had any problem. I do not have VB installed on any of my computers.

Richard