Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-03-19
9
Medium Priority
?
635 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

618 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