I need to create a button in Excel to read data in from serial port. I can't have any extra files attached to the excel sheet. I need to transfer this excel file to another computer to read this data. Here is how the file is suppose to function: Press button to select the serial port. Then, press another button to read data from serial port into the excel cell. Could someone please tell me how to do this?
Using ActiveXperts Serial Port Component with VBA (Visual Basic for Applications) ActiveXperts Serial Port Component is a software development kit (SDK) that enables the user to communicate to a device over a serial interface. I spent hours and hours researching serial communications from VBA. Firstly, I just googled VBA serial comm and found pricey 'shareware' solutions. SerialPort COM3,9600,None,8,one #opens serial port - adjust.
Use VB macro or ActiveX macro? Sorry, this is the first time i'm using excel for this. Again, I can't have another file attached to the excel sheet. I found a discussion on exactly this topic in the german microcontroler.net forum here: Since I am running on Linux I can not verify if the code is correct.
What is the best way to access a serial port from VBA? I have a need for some of our sales reps to be able to send a simple string over the serial port from an action button in PowerPoint.
I don't commonly use VBA, especially for anything like this. Normally I would turn it into an application of some sort, but I actually don't think the idea is that bad. It will be a handy tool for them to demo this device with while on a projector and talking to other sales guys and non technical people.
Also, this sales guy will have no problem making small modifications to the VBA or PowerPoint presentation, but would not do as well with recompiling a.NET application. I know we could do it through a batch file run from the presentation on the action, but that doesn't make me very happy.
I figure we could probably access a COM object and run from there, but again I am not real up on the latest and greatest libraries to use in VBA, and it would also be nice to get a quick little primer in how to easily open, send and close the connection. Since this will need to be run on multiple people's computers, it would be nice if it would be easily transportable to other machines. I should be able to say it has to run on Office 2007 and Windows XP. Compatibility with anything else would be a nice bonus though. How should I go about handling this? Any good tips or tricks? Library recommendations?
Here is a brief module of VBA code that can send and receive messages on a PC serial port. This is not very elegant, but it is simple and should work on modern versions of Excel and Windows. You are left on your own to expand the functionality and store or parse the messages. This just shows the low-level stuff to deal with the serial port.
The first 5 lines declare the millisecond 'Sleep' library function (based on Excel version). The SerialPort subroutine outlines the steps to open the port, transmit some data, receive some data, try again to receive some data (to show that it really does not run afoul of the 'end of file' error), and close the port. #If VBA7 Then ' Excel 2010 or later Public Declare PtrSafe Sub Sleep Lib 'kernel32' (ByVal Milliseconds As LongPtr) #Else ' Excel 2007 or earlier Public Declare Sub Sleep Lib 'kernel32' (ByVal Milliseconds As Long) #End If Public Sub SerialPort ' open a COM port, transmit a message, gather results, close the port. ' open the COM port as file #1 Debug.Print 'Open COM port 4' Open 'COM4:115200,N,8,1' For Binary Access Read Write As #1 transmit$ = Chr(2) + 'Hello, World.' + Chr(13) receiveDummy$ = ' ' transmit a message Put #1, transmit$ Debug.Print 'Message sent.'
' wait a bit for a response Sleep 100 ' check for received message Debug.Print 'Look for incoming message.' On Error Resume Next Do While True receive$ = receiveDummy$ 'dummy value Input #1, receive$ If receive$ = receiveDummy$ Then Exit Do 'the string didn't change, so move on Debug.Print receive$ Loop On Error GoTo 0 ' do it again to show that the empty input queue doesn't stop the flow Debug.Print 'Look again for incoming message (should not stop on error).' On Error Resume Next Do While True receive$ = receiveDummy$ 'dummy value Input #1, receive$ If receive$ = receiveDummy$ Then Exit Do 'the string didn't change, so move on Debug.Print receive$ Loop On Error GoTo 0 ' close the serial port Debug.Print 'Close COM port.' Close #1 Debug.Print 'Done.'