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]

Or this...

Open "PRN" For Output As 1
Print #1, Chr$(12);
Close 1

 

[alanjmartin]

Is it possible to send a variable in the
MSComm1.Output = "c nua" & vbCr where nus is a variable or worksheet cell value....?

 

[GVF]

Alan,

Yes...provided the device you are sending to accepts ASCII strings (otherwise you will have to translate the information (hex or binary or whatever) before you send it.

I usually put the string together before I send it, but I don't think that it's truly necessary.

My personal preference is

SendStr = nus & vbCR
Userform1.MSComm1.Output = SendStr

but the following should also work

Userform1.MSComm1.Output = nus & vbCR

The only reason I do it as shown in the first example is that I generally pass the string along to a sub which sends the string and gathers the response. This can be device specific and so collection of the response might also be device specific. In the snippet below...nus, SendStr and Response are assumed to be public variables.

Sub BlahBlah()
nus = Activecell.Value
SendStr = nus & vbCR
call SendAString(SendStr)
MsgBox Response
End Sub

Sub SendAString(SendmyStr)
with Userform1.MSComm1
.InBufferCount = 0 'clear the buffer
.Output = SendmyStr
TheWait = .2 + Timer
Do until Timer >= TheWait 'a pause that allows the device time to respond
Loop
Response = ""
.InputLen = 1
Do
Response = Response & .Input
Loop Until .InBufferCount = 0
End With
End Sub

Greg

 

[alanjmartin]

Greg

Thanks for the response. Not only did it answer the question I had but also the next 2 which I haven't yet asked.... Well done and thanks

Alan

 

[alanjmartin]

I am trying to expand on the script GVF sent on 10th Mar.

I am using

with Userform1.MSComm1
.InBufferCount = 0 'clear the buffer
.Output = SendmyStr
TheWait = .2 + Timer
Do until Timer >= TheWait 'a pause that allows the device time to respond
Loop
Response = ""
.InputLen = 1
Do
Response = Response & .Input
Loop Until .InBufferCount = 0
End With

to send "SendmyStr" and wait for a response comimg back.

This works ok and I can check it against a known variable. However sometimes I get no response and it just hangs.
Is it possible to have a loop which checks for a response for a time limit and jumps out if it expires ?

 

[PHV]

Response = ""
.InputLen = 1
TheWait = 2 + Timer '2 seconds timeOut
Do
Response = Response & .Input
Loop Until .InBufferCount = 0 Or Timer >= TheWait

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886