[RESOLVED] align cell

[india123]

i am taking value from e4 to Attachmate

MyScreen.Area(10,52,10,8).value = aSheet.range("e4").value
the value has 8 alpha numeric
if it is length <8 charater it copies
for example 8QUEBEC - it shows here 8quebec0
it adds 0 becasue it accepts 8 character
i have
if Len(aSheet.range("4e")) <8 then

MyScreen.Area(10,52,10,8).value = aSheet.range("4e").text
' MyScreen.Sendkeys("<Enter>")
Elseif Len(aSheet.Cells(i,"i")) =8 then
MyScreen.Area(10,52,10,8).value = Trim(aSheet.range("4e")
End if
still it is not working it adds 0
could anybody help me in this

[westconn1]

what do you want to do if the length is less than 8?
you could add a space at the beginning or end of the string, if a space is not valid in this instance you could put some other character such as - or _ , but i don't know what attachmate will accept
try something like this

mychr = "-"   'select your character or space
MyScreen.Area(10,52,10,8).value = string(8 - len(aSheet.range("4e")),mychr) & aSheet.range("4e")

[india123]

the default it shows 8 - 00000000
if it is <8 suppose it is 4 rest of the field it has default 0
so if it is 4 remove smalce if it 5 remove spaces if it 6 remove spaces
it is too much coding
is itheri any other way
if it adjusts right justify it will be ok i think so
and one more question is it possible how od i find column
for example i have heading in 13th row in excel
column from c to M
but if i find dramutn heading
how do i find column
thanks for yurall help

[westconn1]

MyScreen.Area(10,52,10,8).value = string(8 - len(trim(aSheet.range("4e"))),"0") & trim(aSheet.range("4e"))
should write a string of 8 digits with leading zeroes, or you can use format function
MyScreen.Area(10,52,10,8).value = format( trim(aSheet.range("4e")),"00000000")
should produce same result

you can use excel find method to find which column your header is in

rescol = range("c13:m13").find("whattofind").column

[anhn]

My advice is using Attachmate's screen method PutString() is more efficience and easier than using Area object.

Actually, the syntax of Area is:
MyScreen.Area(Row, Col, EndRow, EndCol).Value = ...

Those 4 arguments defines a rectangular area on screen. That option is good for writing a block of more than one rows.
I haven't tested how Area(10,52,10,8) works, as EndCol must >= Col, here 8 < 52,
probably 8 was ignored. Noted that 8 does not represent the length of text is 8 characters.

The syntax of PutString is:
MyScreen.PutString sText, Row, Col
eg.:
MyScreen.PutString aSheet.[E4], 10, 52

Tip1 : Instead of writting : aSheet.Range("E4")
you can use a shortcut form : aSheet.[E4]
For clarity, you should use capital "E" instead of "e" to present a column.

Tip2 : On Excel, you can format exactly what you want to put on Attachmate screen,
such as format cell E4 of value 9000 with Number Format "000000", that cell will display as 009000.
Now, with
MyScreen.PutString aSheet.[E4], 10, 52
aSheet.[E4] will use default property aSheet.[E4].Value to put "9000" to position (10, 52).
However, with
MyScreen.PutString aSheet.[E4].Text, 10, 52
"009000" will be put to position (10, 52).

Tip3 : Use Excel VBA to automate Attachmate is much easier than to use Attachmate macros to automate Excel.

[india123]

wESTCONN
i tried this code
the default in this value = 00000000
MyScreen.Area(10,52,10,8).value = string(8 - len(aSheet.Cells(i, "i")),"" & aSheet.Cells(i, "i"))
when i run the above code it shows 80000000
but it should copy the cell of i "i" if i have less than 8

[westconn1]

you have put no character in the string function, should be like
string(8 - len(aSheet.Cells(i, "i"),"0"),"" & aSheet.Cells(i, "i"))

[india123]

0 is default when it is < 8 it removes 0 and adjust 7 lettersl

[anhn]

Both of you are incorrect if not cause a syntax error. @pete, you misplace a ")".

That should be:

= String(8 - Len(aSheet.Cells(i, "i")),"0") & aSheet.Cells(i, "i")

But what happens if Len(aSheet.Cells(i, "i")) > 8 ? Error!
Where are i and "i" come from? Particular, i has no relavent in post#1.

The first time I see a person uses the syntax .Cells(i, "i") thou it works. This is what pete already mentioned in another post.

Back to post#1, that block of code can be simpler as:

* if you want to fill non-used characters with "0":

MyScreen.Area(10,52,10,8).Value = Right$(String$(8,"0") & Trim(aSheet.Range("E4").Value), 8)

* if you want to fill non-used characters with blank:

MyScreen.Area(10,52,10,8).Value = Right$(Space$(8) & aSheet.Range("E4").Value, 8)

I doubt that the red 8 on LHS is misunderstood, it's a wrong value. The LHS should be:

MyScreen.Area(10, 52, 10, 59).Value

From column 52 to column 59 is 8 characters on row 10.

If you consider what I said in Post#5, your code will be much easier to write.

[india123]

anhn
thanks for your explanation.
MyScreen.Area(10,52,10,8).Value = Right$(Space$(8) & aSheet.Range("E4").Value, 8)

if i try the above code does it work even if asheet range e4 value has 4
in my screen rest of the 4 characters will it be blank.
please let me know
thanks

[india123]

Anhn
ofcourse myscreen.Area ( row, column ,total lengh)
so it works (10,52,10,8) also

[anhn]

anhn
thanks for your explanation.
MyScreen.Area(10,52,10,8).Value = Right$(Space$(8) & aSheet.Range("E4").Value, 8)

if i try the above code does it work even if asheet range e4 value has 4
in my screen rest of the 4 characters will it be blank.
please let me know
thanks

Try it yourself. No need to ask.

Anhn
ofcourse myscreen.Area ( row, column ,total lengh)
so it works (10,52,10,8) also

That's wrong! Read my Post#5 again.
What can you explain with the second 10 in (10,52,10,8)

The MyScreen.Area() has 4 required arguments and other optional argument(s). If use only 4 arguments, the syntax is:
MyScreen.Area(Row, Col, EndRow, EndCol)

For example, as explained in my previous post:
MyScreen.Area(10, 52, 10, 59)
returns or defines a rectangular area on screen from (row 10, col 52) to (row 10, col 59),
that is an area of (1 row, 8 columns) on row 10, starting from column 52.

Instead of using
MyScreen.Area(10,52,10,8).Value = Right$(Space$(8) & aSheet.Range("E4").Value, 8)

I would use:
sText = Right$(Space$(8) & aSheet.Range("E4").Value, 8)
MyScreen.PutString sText, 10, 52

***
Again, as I said in a PM to you, replace your keyboard, probably your Shift-keys are not working!!!! You cannot type a Capital letter.

[india123]

Sorry my capital letters. you are right. thank you so much. it works great.
could yu see my another requirement which i have
if i have my screen has already "09902
how do i copy from excel to screen. could you explain to me
thanks a lot anhn.

[anhn]

Sorry my capital letters. you are right. thank you so much. it works great.
could yu see my another requirement which i have
if i have my screen has already "09902
how do i copy from excel to screen. could you explain to me
thanks a lot anhn.

What I said is right and what is not right? There are more than one things.
This time, only the "S" key on your keyboard can produce Capital letter! Is that right? You kill my eyes.

You are quite "stubborn". Sorry if that word hurts you.
For about 10 posts, I already told you the arguments you used in
MyScreen.Area(10,52,10,8) are wrong.
But you don't want to change the way you are doing,
there is no way to fix-up the problem you have in the other post.

So, I give up! Cannot help you anymore.

Just let you know, I have more than 5 years experience to work with Attachmate macros.
I am not very good with it but reasonably I can work with quite large projects to deal with Attachmate.

If you want to go further. Read my Post#5 and action as what I adviced.

[india123]

Anhn i am not stubburn. i already did half the way more than 500 lines. how do i change it . i will change. i learnt things from you. please help me .please dont get annyed me, i change all MYScreen. Area

[india123]

i = 14 to 35
sText = Right$(Space$(8) & aSheet.Range("iI").Value, 8)
it comes object error
if i put Range ("E4") - no problem
if i put Range ("iI") - it is problem

[westconn1]

if i put Range ("iI") - it is problem

i am not surprised, you have been specifying ranges in many ways, but his does not work
try range("I" & i)

[india123]

i want right space only the below code is right alighment and left spaces
if Len(aSheet.Cells(i, "C")) < 8 then
sText = Right$(Space$(8)) & aSheet.Cells(i, "i").Value, 8) &