Can I use serial comm port in Excel VB script?

[bt.newsgroup.com.hk]

Can I use serial communication port (COMM PORT) in Excel VB script?

If can, how can I use it???

 

[FxM]

bt.newsgroup.com.hk a écrit :

Can I use serial communication port (COMM PORT) in Excel VB script?

If can, how can I use it???

Hi Nelson,

If you read french language, have a look (basics + example) :
http://cherbe.free.fr/rs232.html

HTH

@+
FxM

 

[NC]

FxM,

Thanks for your support. But I cannot read french.
Could you give a xls example of setting COMM Port to me?
Thanks!

NC

 

[FxM]

NC a écrit :

FxM,

Thanks for your support. But I cannot read french.
Could you give a xls example of setting COMM Port to me?
Thanks!

NC

Nelson,

My own translation of the web page.

=== start ===
Reading data via serail port (RS232)

You'll not find any theroy on this communication mode. Here's some
listing of tasks to be done with an example of code in VBA5.

Goal : put in a sheet directly collected values from an external instrument.
Used for this example :
* Excel97 ou above (Windows version only)
* VBA5 ou above
* activeX MSComm32.ocx (*)
* a dead weight instrument with RS232 output and documentation
* a small VB or VBA code

(*) This activeX is not provided with Office2000 or previous but only
with Visual Basic. First look at your system directory to find any file
named "mscom*.ocx". If present, go further; if not, you can register
actveX via the XL_RS232 (only free copy). The example soft also contains
example made in file "pesees.xls".
Use:
- download file XL_RS232.ZIP
- uncompress this file
- run SETUP.EXE
- open file "pesees.xls"
- run macro to test good working
- adapt VBA code with serial as needed

The following example uses COM1. Ilis possible to talk via parallel port
(undocumented here) using activeX named IOport.ocx (shareware) for win98
ou the control NTport.ocx under NT.

Doing the job

It's absolutely necessary to have instrument's documentation to serail
comms settings. Code can be adapted from any MS-Office 97 ou VB
application. Starting from Excel, press Alt-F11.

* Insert a module
* Insert a userform
* In window "toolbox", find out icon MSCOMM (like a telephone). If
missing, right-click toolbox and tick "Microsoft Communication Control".
* Drag and drop the MSCOMM control on userform
* Drag and drop a COMMANDBUTTON control on userform
* Put a label on userform
* Double click on COMMANDBUTTON and enter following code :

Private Sub CommandButton1_Click()
'empts the buffer
MSComm1.InBufferCount = 0
'choose serial port
MSComm1.CommPort = 1 'Com1
'Instruments comms settings, values to be founded in doc
'These values can be filled in here (not clean!)
'or in initialisation procedure (better)
'or in property 'setting' of control
'Ideal is offering to user a configuration menu
'This example is : 1200 bauds, 1 parity bit, 7 data bits, 1 stop bit
'stop
MSComm1.Settings = "1200,o,7,2"
'force to read on character only in view to check stabilisation
MSComm1.InputLen = 1
'open port
MSComm1.PortOpen = True
'loops until reading is not plus sign
Do While MSComm1.Input <> "+"
Loop
'reads the 5 first characters and stores on sheet
MSComm1.InputLen = 5
Label1.Caption = MSComm1.Input
ActiveCell.Value = CSng(Label1.Caption)
ActiveCell.Offset(1, 0).Select
'close port
MSComm1.PortOpen = False
End Sub


In this example, instrument sens data continuously. Program reads data
looking for + sign, because this sign separates data packets.
Often it is possible to proceed by request/answer.
--- end ---

I personally tried to talk to a Modbus slave. Here's an example of code
I'm working with [under development for one year - always something else
to do ) ]

Public Sub test()
With MSComm1
Sheets("Données").Range("A10").ClearContents
On Error Resume Next
.PortOpen = False
On Error GoTo 0
' D1 has 1
.CommPort = Sheets("Données").Range("D1").Value
' D2 shows 38400,n,8,1
.Settings = Sheets("Données").Range("D2").Value
.OutBufferSize = 256
.InBufferSize = 4096
.InputLen = 4096
'reading Modbus ASCII
.InputMode = comInputModeText
' .InputMode = comInputModeBinary
.PortOpen = True
deb = Now(): timeout = False
' D3 shows :010303E800020F
'=> read 2 adresses on slave 01 starting at address 1000
.Output = Sheets("Données").Range("D3").Value & vbCrLf
'gives a 1 second timeout
fin = deb + TimeValue("0:0:1")
Do Until (Now > fin)
DoEvents
Loop
inp = .Input
'ASCII = 2 last are checksum (to be checked by other)
Sheets("Données").Range("A10") = Left(inp, Len(inp) - 2)
.PortOpen = False
End With
End Sub

HTH

@+
FxM