Solved

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

Posted on 2007-03-19
9
597 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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;…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

708 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

17 Experts available now in Live!

Get 1:1 Help Now