Solved

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

Posted on 2007-03-19
9
602 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup" or a blinking cursor with black screen. A loop for Auto repair will start but fix nothing.  You will be panic as there are no back…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

911 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now