Excel: Receive serial without MS Comm controls

J

Jeremy Leach

Senior Member
This is for people experienced with VBA. It's some VBA code that I have found on the Net and have found very useful in Excel. It allows serial input into Excel without having the need to use MS Comm controls. So it's much easier to distribute an Excel solution to people across different versions of windows and without worrying about library references. I can't say I even understand some of it, but it works !

All credits to the author: David M. Hitchner http://www.thescarms.com/vbasic/commio.aspx

Put the comms code into a module. I've had to split the code into 2 files, just add 2 below 1.
 

Attachments

Last edited:
J

Jeremy Leach

Senior Member
... and here's an example of calling the routines. It's how I load 6400 bytes of data into Excel serially, straight from picaxe into Excel, for my own project. I won't bother explaining it, because people who know VBA will get the gist (and probably have improvements anyway)...
 

Attachments

J

Jeremy Leach

Senior Member
Looks good ... I'm sure it's good for graphing etc.

The VBA code is useful for writing 'applications' in Excel that get data dumps from the picaxe (not necessarily graphing data). You've got to know a bit about VBA to use it though. It wouldn't be too hard for someone to write a useful generic 'framework' Workbook to capture picaxe data though, that people could use as a good start to developing their own 'apps' in Excel .... but that's not going to be me !
 
Last edited:
J

Jeremy Leach

Senior Member
Hi. Well I'm not sure this is going to help you, because if you don't have experience of using VBA in Excel then this is probably just going to generate a series of questions :(.

Excel has a very comprehensive help system to teach you about VBA - that's the way I learnt, through many hours of hard work ;).

But anyway .... simply right click on any worksheet tab and you'll see a menu item saying 'view code'. Click that and it will take you into the Visual Basic editor. Then select Insert>Module from the menu. Then paste the code I supplied into the module. Then .... I'm sorry, you will have to work it out for yourself. I've given some advice above about the key things to call. The actual code itself is very complex for a novice, however it offers a very nice direct communication path into Excel, without the fuss of having to check on libraries etc.

Good luck - you might need it :)
 
B

BeanieBots

Moderator
Very handy, thanks for sharing Jez.

Nothing worse than giving someone an app only to find it doesn't work because they don't have the required libraries or run-time modules.
 
L

Linzmeister

New Member
Nice.

I've generally steered away from APIs as most of the examples in the Visual Studio help seem to be directed at C++ type languages and they use slightly different commands or syntax. I have used the API viewer in VB6, but without some guidance you can import a bunch of irrelevant stuff that doesn't seem to do very much.

I have managed to get one very handy set of APIs to work which I use quite a lot. It allows you to call an external executable and wait until it is finished before moving on to the next line of my code.

Code: 
Option Explicit

Private strPath As String
Private StrCommand as String

Private Type STARTUPINFO
    cb As Long
    lpReserved As String
    lpDesktop As String
    lpTitle As String
    dwX As Long
    dwY As Long
    dwXSize As Long
    dwYSize As Long
    dwXCountChars As Long
    dwYCountChars As Long
    dwFillAttribute As Long
    dwFlags As Long
    wShowWindow As Integer
    cbReserved2 As Integer
    lpReserved2 As Long
    hStdInput As Long
    hStdOutput As Long
    hStdError As Long
End Type

Private Type PROCESS_INFORMATION
    hProcess As Long
    hThread As Long
    dwProcessID As Long
    dwThreadID As Long
End Type

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
    hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
    lpApplicationName As String, ByVal lpCommandLine As String, ByVal _
    lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
    ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
    ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As String, _
    lpStartupInfo As STARTUPINFO, lpProcessInformation As _
    PROCESS_INFORMATION) As Long

Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long

Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&



Private Function ExecuteAndWait(cmdline As String) As Long
  
    Dim proc As PROCESS_INFORMATION
    Dim start As STARTUPINFO
    Dim ret As Long
    
    ' Initialize the STARTUPINFO structure:
    start.cb = Len(start)

    ' Start the shelled application:
    ret = CreateProcessA(vbNullString, strPath & cmdline, 0&, 0&, 1&, _
        NORMAL_PRIORITY_CLASS, 0&, strPath, start, proc)

    ' Wait for the shelled application to finish:
       ret& = WaitForSingleObject(proc.hProcess, INFINITE)
       Call GetExitCodeProcess(proc.hProcess, ret&)
       Call CloseHandle(proc.hThread)
       Call CloseHandle(proc.hProcess)
       ExecuteAndWait = ret
   End Function


Public Sub DoSomething()
On Error Resume Next
    Dim test As Variant

   'Insert lots of code here

    cmdline = "Notepad.exe"
    strpath = "C:Windows\"

     test = ExecuteAndWait(strPath & StrCommand)

    'Insert more code here

End Sub
This will halt your application until notepad is closed in this exapple. You can do anything with the other application you like, open as many files and edit for hours is you so desire. As soon as you quit the application your code will continue.

I use it inside MS Access to call a serial comms package to poll sales reports from a cash register. When the comms finish, I import the collected data into the database. and then I do the same again to get the next report.

regards,
Linzmeister
 
L

Linzmeister

New Member
Nice.

I've generally steered away from APIs as most of the examples in the Visual Studio help seem to be directed at C++ type languages and they use slightly different commands or syntax. I have used the API viewer in VB6, but without some guidance you can import a bunch of irrelevant stuff that doesn't seem to do very much.

I have managed to get one very handy set of APIs to work which I use quite a lot. It allows you to call an external executable and wait until it is finished before moving on to the next line of my code.

Code: 
Option Explicit

Private strPath As String
Private StrCommand as String

Private Type STARTUPINFO
    cb As Long
    lpReserved As String
    lpDesktop As String
    lpTitle As String
    dwX As Long
    dwY As Long
    dwXSize As Long
    dwYSize As Long
    dwXCountChars As Long
    dwYCountChars As Long
    dwFillAttribute As Long
    dwFlags As Long
    wShowWindow As Integer
    cbReserved2 As Integer
    lpReserved2 As Long
    hStdInput As Long
    hStdOutput As Long
    hStdError As Long
End Type

Private Type PROCESS_INFORMATION
    hProcess As Long
    hThread As Long
    dwProcessID As Long
    dwThreadID As Long
End Type

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
    hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
    lpApplicationName As String, ByVal lpCommandLine As String, ByVal _
    lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
    ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
    ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As String, _
    lpStartupInfo As STARTUPINFO, lpProcessInformation As _
    PROCESS_INFORMATION) As Long

Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long

Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&



Private Function ExecuteAndWait(cmdline As String) As Long
  
    Dim proc As PROCESS_INFORMATION
    Dim start As STARTUPINFO
    Dim ret As Long
    
    ' Initialize the STARTUPINFO structure:
    start.cb = Len(start)

    ' Start the shelled application:
    ret = CreateProcessA(vbNullString, strPath & cmdline, 0&, 0&, 1&, _
        NORMAL_PRIORITY_CLASS, 0&, strPath, start, proc)

    ' Wait for the shelled application to finish:
       ret& = WaitForSingleObject(proc.hProcess, INFINITE)
       Call GetExitCodeProcess(proc.hProcess, ret&)
       Call CloseHandle(proc.hThread)
       Call CloseHandle(proc.hProcess)
       ExecuteAndWait = ret
   End Function


Public Sub DoSomething()
On Error Resume Next
    Dim test As Variant

   'Insert lots of code here

    cmdline = "Notepad.exe"
    strpath = "C:Windows\"

     test = ExecuteAndWait(strPath & StrCommand)

    'Insert more code here

End Sub
This will halt your application until notepad is closed in this exapple. You can do anything with the other application you like, open as many files and edit for hours is you so desire. As soon as you quit the application your code will continue.

I use it inside MS Access to call a serial comms package to poll sales reports from a cash register. When the comms finish, I import the collected data into the database. and then I do the same again to get the next report.

regards,
Linzmeister
 
You must log in or register to reply here.
Top