Transferring data between Excel and a USB-RS232 com port

BillBWann

Member
Has anyone got VBA code or a reference to VBA code that can run in Excel 2007 (or later) using Windows Vista (or later)?

I have tried the VBA code given here but I couldn’t get it to open a com port. It said my port number 6 which corresponds to my usb-RS232 cable was out of range.

It seems that all the successful cases I’ve found involve earlier versions of Excel and/or Windows.
 

grim_reaper

Senior Member
Hi Bill,

That web site has a lot of code! It looks very old to me (VBA was around in the late 90's, and has been eradicated in every application I've seens since then, apart from within Office!). I could easily help you debug things if it was written in .NET... lol

Can you post the exact code you're using from your Excel sheet? Or even post the .xlsm? (If it will let you).
If not, can you post the line the error occurs on? I presume the fault comes back from the API call 'CommOpen'... which I've used many many years ago and if I remember rightly I had 16 COM ports running through it, so the fact it's #6 should make no difference.

What's the exact error?

Grim
 

inglewoodpete

Senior Member
Has anyone got VBA code or a reference to VBA code that can run in Excel 2007 (or later) using Windows Vista (or later)?

I have tried the VBA code given here but I couldn’t get it to open a com port. It said my port number 6 which corresponds to my usb-RS232 cable was out of range.

It seems that all the successful cases I’ve found involve earlier versions of Excel and/or Windows.
I use a Class module by Brendan Lambe, with modComm by David M. Hitchner in Excel 2013. If you search for some of those key words on the www, you should find the working class module.
 

BillBWann

Member
Can you post the exact code you're using from your Excel sheet? Or even post the .xlsm? (If it will let you).
If not, can you post the line the error occurs on? I presume the fault comes back from the API call 'CommOpen'...

What's the exact error?
Thanks Grim for your thoughts. My program is essentially just what I copied from that website. The only changes I made was to change the intPortID to 6 and I got a subscript out of range error when it called CommOpen as you suggested. After sending off my post, I realized that that error was simply because I hadn't increased the size of the udtPorts array. After doing that, I don't get any more errors.

It seems to open the port because if I try to open that port using the picaxe terminal, it says it isn't available. It doesn't seem to close it though because I still don't get access to the terminal after I run the close routine.

I wrote a picaxe program that simply sertxd's "ABCDEFG" with a CR,LF repeatedly and then looked at the variable strData that should return something from the CommRead function but it was always empty.

Thanks also Inglewoodpete for your references. I googled those and they seem to contain a wealth of good information that I'll read in detail at some time.

At this stage, I think I'll abandon the idea of using excel VBA to do what I planned and use a standard program like MakerPlot. I'm getting a bit sick of writing my own application code only to find that it becomes obsolete when a new operating system comes out. I only have a very vague idea of what goes on in Windows and how Visual Basic programs interact with it.
 

szpalko

New Member
Hi,

You need to change MAXPORTS=4 to something else, like 16. There is limit for intPortID for 1 to MAXPORTS.
 

szpalko

New Member
Also make sure your voltage levels are appropriately converted from your picaxe GND-to-5V to match the comport voltages (if you are not using a device to do that for you, the AXE27 is nothing more than a USB-Comport). What I also did to check things out is to feed the Tx pin directly back to the Rx pin via a 1k resistor. Then I opened the port, wrote some data, read the buffer in and closed the port. If everything works well the data you sent will also immediately read-in as well. Works great for me.
 

BillBWann

Member
You need to change MAXPORTS=4 to something else, like 16........

Also make sure your voltage levels are appropriately converted from your picaxe GND-to-5V to match the comport voltages..........

Then I opened the port, wrote some data, read the buffer in and closed the port. If everything works well the data you sent will also immediately read-in as well. Works great for me.
Thanks szpalko for replying.

Do your replies mean that you are using the VBA routines I referred to in #1 and it works for you? I have already increased MAXPORTS as I mentioned in #4. Also I'm sure that I don't have a hardware problem as I can receive data using other terminal software so I'm convinced that I only have a software problem.

What operating system and version of Excel are you using? If you have a VBA program that runs under Vista (or later) and Excel 2007 (or later), could you post it here as I'm sure that there would be many people besides me who would benefit from it.
 

BESQUEUT

Senior Member
At this stage, I think I'll abandon the idea of using excel VBA to do what I planned and use a standard program like MakerPlot. I'm getting a bit sick of writing my own application code only to find that it becomes obsolete when a new operating system comes out. I only have a very vague idea of what goes on in Windows and how Visual Basic programs interact with it.
You can also get Visual STudio Express
so you will be up to date and able to send data directly to Excel...
 

szpalko

New Member
Hi BillBWann,

I have been using this on XP, and Windows 7. The original version was in VBA/Excel 2003. I have a laptop running Excel 2007 and had some issues, but was able to create some lines to accommodate both 2003 and 2007. Now it works on almost any laptop/desktop. I could post the file, but I'll have to thin it down as I have a bunch of routines for processing data, making it difficult to sort through. The main idea though is the info referred to earlier in this thread (thanks to the author/s, who ever they area). I am surprised that the port does not seem to work for you. The basic com port routines worked fine the first time for me, and have worked great on many computers since. Perhaps a voltage level is the problem? Make sure you did not accidentally kill the port circuitry.... By connecting the port Tx and Rx pins (with a 1k resistor for safety) the data you send on an open port will immediately read back into the in-buffer. This is fool proof from a hardware point of view as the port sends out the levels/format that it needs to receive (it is compatible with its self). Then you can read the data via the read function. This must be done prior to closing the port, as closing will flush the data (you can disable that part of the code if needed).
 

szpalko

New Member
Hi BillBWann,

Attached is a quick little spreadsheet with a userform. (Please rename it from .txt to .xls ,it did not upload as an .xls) Review the code in VB. Note that MAXPORTS, buffer size should be changed through out. If you want to use a different port number, make a reference to it via a cell, textbox etc through out the code.
 

Attachments

BillBWann

Member
Yes that works a treat under Vista and saved as a xlsm under Office 2007.
It also works under Windows 10!!!!

Win 10 effectively installed itself recently without my permission. Well it downloaded itself without my permission anyway and used up all my monthly download capacity. I didn't want that to happen again so I accepted it.

It really annoyed me at the time but so far I've been quite surprised that most things seem to work OK and there hasn't been a problem.

There hasn't been any improvement either from my perspective but at least I shouldn't have to change again for a few years.
 
Top