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

[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

 

[Ftca]

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

 

[jaf]

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.

 

[Ftca]

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

 

[Ftca]

Dim FF

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

 

[Ftca]

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

 

[Tom Ogilvy]

? 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.

 

[Ftca]

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

 

[Tom Ogilvy]

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

 

[Ftca]

Thanks Tom
works a treat

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

 

[Ftca]

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