The sub below is very straightforward - but will help get you started Private Sub GetData() Dim MyData As String Wokrsheets(SerialPort).MSComm1.InputLen 0 MyData Worksheets(SerialPort).MSComm1.Input ActiveCell.Value MyData MyData Worksheets(SerialPort).MSComm1.PortOpen False End Sub The InputLen setting tells Excel how much data to read from the serial device.Although I am no Excel or VBA expert.I have learnt one heck of a lot in recent times.
I have had some major issues using MSCOMM and finding informationsupport on the web. So I thought I would share how I got it working on an excel worksheet to communicate with a serial device. Go to command prompt and type the following - regsvr32 C:WindowsSystemMSCOMM32.OCX You should get a message to indicate that the control has been registered. Excel Vba Serial Port Mscomm Control Update The RegistryThen you may need to update the registry with this key - HKEYCLASSESROOTLicenses4250E830-6AC2-11cf-8ADB-00AA00C00905 kjljvjjjoquqmjjjvpqqkqmqykypoqjquoun As far as the registry stuff goes, I am no expert. But if you have any concerns.or if you are not familiar with working within the registry.I would suggest doing some more research first Now.you should be ready to program with MSCOMM Open up a new Excel workbook. Make sure you can see the Control Toolbox (ViewToolbarsControl Toolbox). ![]() Click the icon and select Microsoft Communication Control from the list. The cursor will change, allowing you to draw a box on the worksheet. It does not matter where you place it - as when you open the workbook this will not be visible to the user. Once you have created the control, right-click and select View Code. You should see something like - Private Sub MSComm1OnComm() End Sub This is the OnComm event and tells excel what to do when data is received from the serial port. Insert the following into a new sub under the OnComm sub - Sub OpenPort() Open the COM Port with the relevant settings Worksheets(SerialPort).MSComm1.CommPort 1 Worksheets(SerialPort).MSComm1.Settings 9600,n,8,1 Worksheets(SerialPort).MSComm1.RThreshold 1 Worksheets(SerialPort).MSComm1.InBufferSize 4096 Worksheets(SerialPort).MSComm1.PortOpen True End Sub The sub above will configure the port when you try and open it. Settings are the device setting (baud rate, parity, etc) and are usually in the device documentation (you can also use the device with Hyperterminal to get these settings). PortOpen True tells Excel to open the port with the above settings. Excel Vba Serial Port Mscomm Control Code Whenever DataBy setting it to 1 we are telling Excel to fire the OnComm code whenever data is received from the serial port. The way I call the port open sub, is to have a worksheet onchange event. I only want the port to open when the user selects a cell in a particular column range, so I am using the following - Private Sub WorksheetSelectionChange(ByVal Target As Range) If Not Intersect(Target, Columns(A:A)) Is Nothing Then If Target.Value Then Call OpenPort End If End If Application.EnableEvents True End Sub This piece of code must sit against the Worksheet Selection Change section. Use the dropdown boxes at the top of your VBA editor to select this. So now, if a cell in column A is selected, the port will be opened with my settings. As RThreshold is set to 1 - the OnComm code will be called whenever I try to read from the cheque reader and am in the A column. Go back to your OnComm make it look like - Private Sub MSComm1OnComm() If Worksheets(SerialPort).MSComm1.CommEvent comEvReceive Then Call GetData End If End Sub The CommEvent - comEvReceive tells Excel that if data is coming in from the serial device.do something. In this case, Call sub GetData. Now, to create sub GetData that will grab the information from the device and place it in your worksheet.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |