Solved

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

Posted on 2007-03-19
9
612 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

726 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