bwagner-stl
asked on
COM Port Error Using Excel VBA - Can the Same COM Port Be Used for Output & Input
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
==========================
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
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?
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?
ASKER
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.
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.
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
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
ASKER
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DanRollins,
It seems that the questionner solved the problem himself. Perhaps it's worth PAQ'ing. The points should be refunded.
Patrick
It seems that the questionner solved the problem himself. Perhaps it's worth PAQ'ing. The points should be refunded.
Patrick
Are you sure the balance is sending you a string as opposed to a numerical value?
Patrick