can excel use automatic carraige returns? and send/recieve data via com port?

  • Thread starter Thread starter Ftca
  • Start date Start date
F

Ftca

Hi
I need to design a sheet that is of a set size
i.e 20 rows by 20 columns
I'm inputting the data with a barcode scanner (via serial port)
I need the cursor to automatically go to a new line and start of line
once the top line is full of data.
I have spent hours reading help and searching the net but to no success
can someone please help me here?

I also have an even harder problem
and that is -
can excel send a "?" chr via a com port and
then input the response ( a weight in KG) back into a cell?
its a set of scales for animals

Thanks in advance
 
OK I worked out the 1st part by searching newsgroups - google
what a nice tool btw!

and learned about locking and protecting worksheets

but the 2nd part
that is - I need to send 1 charector from excel 2000 - a ? to com1
which a set of scales is attatched to , once the scales recieves the ? chr
it sends the current weight back through the com port
which I need to enter in a cell names weight.

I would really appreciate some hints

I tried to recall old dos commands like rediection and pipes but
managed to send the ? but could not recieve anything
the scales must use 4800,8,n,1

TIA
 
Hi,
If your using a keyboardwedge for the barcode scanner you won't get the data
from the scale.
You need to be using an ActiveX control like MSCOMM32.OCX
See this http://www.yes-tele.com/mscomm.html

You need a macro to send the character.
Do an ALT F11 to open the VBA editor. Then do insert>module.
Copy and paste the code below into the module.

Sub GetWeight()
Dim FF
Shell Environ("comspec") & " /c" & "MODE COM1 BAUD=4800 PARITY=N DATA=8
STOP=1"
FF = FreeFile
Open "COM1" For Output As #FF
Print #FF, Chr(63)
Close #FF
End Sub

Then go to tools>macro>macros. Click on the macro "getweight", click options
and assign a hotkey.

If you go with the MSCOMM32, post back if you need help with the code as the
code above should be replaced as the control will have control of the port.
 
Jaf
Thanks so so much for your reply
I'll try and use your suggestion
I have 12 hours left before my deadline

thanks

Peter
 
Dim FF
Click to expand...

John,
is FF a file or meant to be substituted for a file or can it be a cell?

as I get a runtime error 75 when I step into - Open "COM1" For Output As #FF

TIA
 
Thanks
I don't suppose you know a way to get a string of text from a small text
file
5 charecters to be exact - a weight 000KG
I know about - Data , import external data
but the user is so pc clumsy
I need to automate it

what I need is to extract the weight which is now refreshed with every row
and place it in a cell

btw
I worked the whole lot out myself
I wrote a file using delphi to send a byte to the scales and recieve the
weight into a text file
I'm just stuck on this - getting the weight from the text file into the
spreadsheet

any help would be most appreciated

regards
Peter
 
? freefile
1

he has FF = Freefile

so FF holds the next available filenumber which is required by the open
command.

Sub GetWeight()
Dim FF
Shell Environ("comspec") & " /c" & _
"MODE COM1 BAUD=4800 PARITY=N DATA=8 STOP=1"
FF = FreeFile
Open "COM1" For Output As #FF
Print #FF, Chr(63)
Close #FF
End Sub

Ran fine for me - of course I don't have a scale attached, so I can't test
the results.
 
thanks Tom

That would be nice if it works for me
I'd rather have the whole thing working from within excel
I'll give it a try now
and if it works I'd prefer it to the solution I have now
which is an .exe file I have to "shell" to

thanks Tom
 
Sub ReadFile()
Dim fileno as Long
Dim sLine as String
Dim rng as Range
fileno = Freefile
Open "C:\Data\Weight.txt" For Input As #fileno
Line Input #fileno, sLine
Set rng = Cells(rows.count,1).End(xlup)(2)
rng.Value = sline
Close #fileno
End Sub
 
Thanks Tom
works a treat


Tom Ogilvy said:
Sub ReadFile()
Dim fileno as Long
Dim sLine as String
Dim rng as Range
fileno = Freefile
Open "C:\Data\Weight.txt" For Input As #fileno
Line Input #fileno, sLine
Set rng = Cells(rows.count,1).End(xlup)(2)
rng.Value = sline
Close #fileno
End Sub
Click to expand...
 
Thanks for all your help Tom and John (Jaf)
I finally finished this nightmare spreadsheet
I can finally get some sleep it 3:49am

Most Kindest Regards
Peter
 
Back
Top