Solved

COM Port Error Using Excel VBA - Can the Same COM Port Be Used for Output & Input

Posted on 2007-03-19
9
607 Views
Last Modified: 2010-04-30
I'm having a problem with importing data from an analytical balance into an Excel spreadsheet connected via a RS232 serial cable.  I first send a command to the balance to read the weight and then try to capture the input from the com port into Excel.  When I do this i get "Run-Time Error 52".  Here's the code I am using:
========================================================
Open "COM1:12,,0o,7,1" for Output as #1   ' open COM1 port for output
Write #1, Chr(33) + Chr(80) + vbCrLf   ' send command to balance to read weight
Close #1   ' close COM1

Open "COM1:1200,o,7,1" for Input as #1   ' open COM1 port for input
Input #1, strString   ' get input from balance (weight) and assign it to a string variable
ActiveCell = Mid(strStirng,5,6)  ' parse input
ActiveCell.Select
Close #1   ' close COM1
=========================================================
The run-time error comes up on the Input #1 line of code.  Any ideas what might be going on?  Thanks.

Bruce
0
Comment
Question by:bwagner-stl
  • 5
  • 2
9 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 18747953
Bruce,

Are you sure the balance is sending you a string as opposed to a numerical value?

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 18747970
The alternative is that you have two different Open commands:

Open "COM1:12,,0o,7,1" for Output as #1   ' open COM1 port for output
.
.
Open "COM1:1200,o,7,1" for Input as #1   ' open COM1 port for input

Is there a reason for the difference?
0
 

Author Comment

by:bwagner-stl
ID: 18748125
patrickab,

Sorry about the poor typing.  The Open commands are the same accept for the mode...one is for output (so I can send a command to the balance) and the other is for input (so I can read the input from the balance).  Is there an alternate method?  I don't know of an access mode that supports both input and output when a file is not being used.  Also, I'm not sure if a string is being generated by the balance output, but when I execute the input code:

Open "COM1:1200,o,7,1" for Input as #1   ' open COM1 port for input
Input #1, strString   ' get input from balance (weight) and assign it to a string variable
ActiveCell = Mid(strStirng,5,6)  ' parse input
ActiveCell.Select
Close #1   ' close COM1

without first trying to send a command to the balance through the Output code section the number comes into the spreadsheet just fine.  Thanks.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 45

Expert Comment

by:patrickab
ID: 18748325
Will it accept 'Read' as in:

Open "COM1:1200,o,7,1" for Input as #1   ' open COM1 port for input
Read #1, strString   ' get input from balance (weight) and assign it to a string variable
ActiveCell = Mid(strStirng,5,6)  ' parse input
ActiveCell.Select
Close #1   ' close COM1
0
 

Author Comment

by:bwagner-stl
ID: 18755258
patrickab,

Thanks for the idea, but no using the READ statement did not work.  I ended up finding a solution by using the MSCOMM32 Active X component that Microsoft has.  It seems like the key was there has to be some type of small delay between outputing to the balance and reading from the balance.  When I put a delay in of approximately 250 milliseconds the code (using the mscomm32.ocx) works great.

Thanaks so much for your help and prompt response.  I'm kinda new on EE and was wondering is there a way you can receive a partial point count for being so quick to assist me?  If so, how do I go about do that?  Thanks!
0
 
LVL 45

Accepted Solution

by:
patrickab earned 500 total points
ID: 18755348
bwagner-st,

Glad you solved the problem. Thank you for your kind thoughts but I'm afraid there's no partial points system here - only grades but that doesn't reduce your 'spend' of points. I suggest that as you solved the problem yourself it would be right to ask for the question to be closed and for a refund of your points. It would be worth PAQing (archiving) your result for others to see.

You can ask a free question at the following link:

http://www.experts-exchange.com/Community_Support/

Requesting "The question is closed and points refunded because you've solved the problem yourself - but PAQ for future use."

Quote the URL for your question. In this instance it is:

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_22457880.html?cid=238#a18755258

Hope that helps

Patrick

0
 
LVL 45

Expert Comment

by:patrickab
ID: 19130704
DanRollins,

It seems that the questionner solved the problem himself. Perhaps it's worth PAQ'ing. The points should be refunded.

Patrick
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question