access rs232 port with vba

[BoBri]

is there any way to send data to com port or listen to it and write data to
file or sheet from vba in excell
lp b

 

[Bob Phillips]

A quick trawl of Google came up with these posts http://tinyurl.com/4uzsy

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Sharad]

For this you can use "Microsoft Communications Control 6.0" which is
"MSCOMM32.OCX".
To use it you must insert a UserForm in your code.
You need not show the form to user, you just load it but don't show, but
insert a UserForm.
Change the name of this Userform to (say) ' frmComm '.
When you insert the userform, normally the ToolsBox pops up. (If it
doesn't then click on 'Veiw' Menu and select 'ToolsBox'.).

Right click on ToolsBox and select 'Additional Controls'.
In Additional Controls window scroll down and find "Microsoft
Communications Control 6.0" and select it.
In the file path that is displayed below verify that it points to
MSCOMM32.OCX and click on OK.

You will now find that a new control with picture of telephone has
appeared on the ToolsBox.
Now click on this control and drag it on the form. On the userform this
picture can be anywhere, it doesn't matter. (This is picture is never
shown even if you Show the userform.).

On the userform (the name I assume is frmComm), select the drag MScomm
picture and view its properties. Change its name to (say) ' RS232 '.

Now that's all you have to do with the Userform.
You can now refer to your RS232 connection in any module, or in class
procedure (like Thisworkbook etc.) as 'frmComm.RS232' .

In your procedure (in a module or any other class modules) when you want
to communicate you start your code as under:

Sub CommTest()
Load frmComm 'Load the useform first.

'Now set the communications port to one of COM1, COM2 etc.
'For this you need to use only the number 1, 2 etc.
'Assume it is COM1 then you set the port as under.

frmComm.RS232.CommPort = 1

'Before opening the port prepare for errors
On Error Resume Next
frmComm.RS232.PortOpen = True
If Err = 8002 Then
MsgBox "The COMPORT is not present or defective."
Exit Sub
End if
If Err = 8004 Then
Msgbox "The selected port is already open by other program."
Exit Sub
End if

'Now you are ready to send data / read data.
'For this the commands are .Output and .Input
'But before proceeding some equipment when connected
'my send a welcome string. So you first start checking
'if it is sent and if sent you must clear the buffer.
'Do as under
Dim commInput
If frmComm.RS232.InBufferCount > 0 Then
frmComm.RS232.InputLen = 0
commInput = frmComm.RS232.Input
End If
'The variable commInput now holds the received data.

'Now you can send command to get data. Most commonly
'the data is sent in ASCII. Check your communication
'protocol, if you must send in ASCII. I assume it is
'ASCII. Suppose the code to read a parameter is 5 and
'terminating code is 10.
'So you send the data as under.
frmComm.RS232.Output = Chr(5) & Chr(10)

'Now you must wait for the device to respond. This may take
'few miliseconds to a couple of seconds. However you can't
'wait indefinately. Max. wait time of 5 sec. should be more
'than enough. We do this in a Doloop as under.

Dim stTime As Long

stTime = Timer

Do While frmComm.RS232.InputBufferCount = 0
DoEvents
If Timer > stTime + 5 Then Exit Do
Loop

If frmComm.RS232.InputBufferCount = 0 Then
MsgBox "Time out communicating with device." _
& Chr(13) & "Ensure the device power is on and cable is connected."
Exit Sub
End If

'Decide whether you want to read one character at a time
'or all received data at a time.
frmComm.RS232.InputLen = 1 'for 1 char. at a time
'OR frmCommRS232.InputLen = 0 ' for all at once.

commInput = frmComm.RS232.Input

'Will read either one all all the characters depending
'upon what you seleted. If you selected one at a time
'you must process the input, and read next character
'until all characters are read. You must do this before
'you send the next .Output command. Other wise the 'leftover character
will be still in the buffer and 'when you do .Input nextime that
character will be sent first.

'When done you can close the port with command:
frmComm.RS232.PortOpen = False

'And then unload the form when finally done
Unload frmComm

Hope this helps

Sharad

 

[Thomas Lutz]

You can use the MSComm ActiveX control in Excel however it will
require that you first have a copy of Visual Studio 6 installed in
your system because the licensed version of MSComm ActiveX control
only comes with Visual Studio 6.
It will also require that you do quite a bit of VBA programming in
Excel to make things work.
Perhaps a better solution would be to use a third party tool that is
designed for the job.
An excellent tool that you can use to do serial communications
directly from within Excel is called WinWedge and you can learn more
about it on the following web site:
http://www.taltech.com/products/winwedge.html