• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

WRQ Reflection Vb help

X

xcruc1at3r

Member
I here any possibility that can we Make it Excel dynamic range cause the excel varies its range? if the code would be really big so it would take load of time and memory as well


Sub macro_22333()


Dim excelApp As Object

Dim z As Integer

Set excelApp = GetObject(, "Excel.Application")

Set excelApp = GetObject("C:Documents and SettingsmintezarDesktopBook1.xlsx")

z = excelApp.ActiveSheet.Range("B2").Copy

With Session

.WaitForEvent rcEnterPos, "30", "0", 1, 1

.TransmitTerminalKey rcIBMPf9Key

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1

.WaitForEvent rcEnterPos, "30", "0", 5, 13

.WaitForDisplayString "==>", "30", 5, 9

.TransmitANSI "1"

.TransmitTerminalKey rcIBMEnterKey

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1

.WaitForEvent rcEnterPos, "30", "0", 18, 33

.WaitForDisplayString "ID:", "30", 18, 29

.TransmitANSI "akhimki"

.TransmitTerminalKey rcIBMTabKey


' Password has not been recorded for security

Dim hostpassword As String

hostpassword = ""

hostpassword = .GetPassword("PASSWORD:", "", "", "")

.TransmitANSI hostpassword

.TransmitANSI "50.7.13"

.TransmitTerminalKey rcIBMEnterKey

End With

With Session

.WaitForEvent rcEnterPos, "30", "0", 4, 16

.WaitForDisplayString "CITY/SITE:", "30", 4, 4

.TransmitTerminalKey rcIBMTabKey

.TransmitTerminalKey rcIBMTabKey

.Paste

TransmitTerminalKey rcIBMTabKey

.WaitForEvent rcEnterPos, "30", "0", 23, 15

.WaitForDisplayString "SCREEN:", "30", 23, 7

.TransmitTerminalKey rcIBMEnterKey

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1

.WaitForEvent rcEnterPos, "30", "0", 1, 80

.WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67

.TransmitTerminalKey rcIBMNewLineKey

.TransmitTerminalKey rcIBMNewLineKey

.TransmitTerminalKey rcIBMNewLineKey

End With

z = excelApp.ActiveSheet.Range("C2").Copy

With Session

.WaitForEvent rcEnterPos, "30", "0", 4, 11

.WaitForDisplayString "SEALS..:", "30", 4, 2

.Paste

.TransmitTerminalKey rcIBMEnterKey

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1

z = excelApp.ActiveSheet.Range("b3").Copy

With Session

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1

.WaitForEvent rcEnterPos, "30", "0", 3, 11

.WaitForDisplayString "NBR:", "30", 3, 6

.Paste

.TransmitTerminalKey rcIBMEnterKey

.WaitForEvent rcEnterPos, "30", "0", 1, 80

.WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67

.MoveCursor 4, 11

.WaitForDisplayString "SEALS..:", "30", 4, 2

z = excelApp.ActiveSheet.Range("C3").Copy

.Paste

.TransmitTerminalKey rcIBMEnterKey

z = excelApp.ActiveSheet.Range("B4").Copy

.Paste

.TransmitTerminalKey rcIBMEnterKey

.WaitForEvent rcEnterPos, "30", "0", 1, 80

.WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67

.MoveCursor 4, 11

.WaitForDisplayString "SEALS..:", "30", 4, 2


End With

End With

End Sub
 
Xcruc1at3r


Can you please tell us what this does or what your question is ?
 
well hui thanks for showing your interest ! 1st of all..... well Excel is linked with a software Reflection of IBM....

Set excelApp = GetObject(, "Excel.Application")

Set excelApp = GetObject("C:Documents and SettingsmintezarDesktopBook1.xlsx")

z = excelApp.ActiveSheet.Range("B2").Copy <<<< copies the data from Cell B2 and paste it in the software >>> like this >> .WaitForEvent rcKbdEnabled, "30", "0", 1, 1

.WaitForEvent rcEnterPos, "30", "0", 3, 11

.WaitForDisplayString "NBR:", "30", 3, 6 ( this is were it pastes)

.Paste

.TransmitTerminalKey rcIBMEnterKey << Enter the new screen comes up then goes to C2

z = excelApp.ActiveSheet.Range("C2").Copy

.WaitForEvent rcEnterPos, "30", "0", 1, 80 << NEW screen

.WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67 << cursor default position

.MoveCursor 4, 11 >> move cursor to 4 column, 11 row

.WaitForDisplayString "SEALS..:", "30", 4, 2

.Paste


Well this process start from B2 and is till B4 well and my data varies from 500 to 1200 entries so is there any possibility that this process be made for Dynamic range ?
 
When you say it starts in B2 and is till B4

can you explain what this means?


Does it mean B2, then B3, then B4 then B5 up to B500 to B1200
 
yes so on..starts from picks data from b2 1st then c2 , then b3 then c3 ....and so on...but the coding is a test coding and as you can see in the last of the coding its till b4 .. i forgot to add after .WaitForDisplayString "SEALS..:", "30", 4, 2

z = excelApp.ActiveSheet.Range("C4").Copy

.WaitForDisplayString "SEALS..:", "30", 4, 2

.Paste


well as you can see above its till c4 so i need to work still it find the last C column with data!
 
Xcruc1at3r


I would setup a For Each loop

then use the variable C which is a range object to

[pre]
Code: 
Dim c As Range
For Each c In Range("B2", Range("B1048576").End(xlUp))
'your code here
Next c[/pre]

If you use c instead of B2 etc you can use c.offset(0,1) for C2 etc


As you are using VB instead of VBA I am uncertain as to the syntax of the above


It may be something like


for each c in excelApp.ActiveSheet.Range("B2", Range("B1048576").End(xlUp))
 
Dim B As Range

Dim C As Range

for each B in excelApp.ActiveSheet.Range("B2", Range("B1048576").End(xlUp))??

for each C in excelApp.ActiveSheet.Range("C2", Range("B1048576").End(xlUp)) ??

well its not clear to me am not that good with all this!! if you could add the Loop in it and create a code and i'll test it and let you know the result !

am confused cause 1st phase it to connect reflection with Excel

2nd phase is to login

3rd goes to the Screen

4th Copies B2 cell data and Paste in the screen and enters

and then Copies C2 data and paste in the screen and enters

and this process should go on till the last cell found with Data
 
Xcruc1at3r


You don't need the two loops for B & C as the code for each of them is slightly different

You can use the Offset modifier to access C as C steps through each Cell in Column B


Without knowing how your data is laid out or organised and what your code is doing it is hard to modify your code


You should ask whoever setup this code initially to modify it.
 
well i surfed internet and got some the top part to link excel with reflection from 1 site and then the Password area is recorded and next is recorded mixed if my efforts in trying and testing...!! laid out in what sense ! maybe i can explain !
 
Xcruc1at3r


If you have put this together yourself, well done.


One issue you want to think about is data flow and in your case stepping through the data from Row 2 to Row X (500-1200)

Is all your data in 1 row or in ever second row?


As in your code you reference Row 2 then row 3 and row 4 in the same loop


The code will be something like below:


Note the extra comments I have inserted and question the logic of the assignments


You may also want to rearrange the loop so that it doesn't repeatedly open and close the link but Opens the link, Writes/Reads the data and then closes the link

Dim excelApp As Object
Dim z As Integer
Dim c As Range
Dim hostpassword As String

Set excelApp = GetObject(, "Excel.Application")
Set excelApp = GetObject("C:Documents and SettingsmintezarDesktopBook1.xlsx")

For Each c In excelApp.ActiveSheet.Range("B2", excelApp.ActiveSheet.Range("B1048576").End(xlUp))
c.Copy
'z = excelApp.ActiveSheet.Range("B2").Copy

With Session
.WaitForEvent rcEnterPos, "30", "0", 1, 1
.TransmitTerminalKey rcIBMPf9Key
.WaitForEvent rcKbdEnabled, "30", "0", 1, 1
.WaitForEvent rcEnterPos, "30", "0", 5, 13
.WaitForDisplayString "==>", "30", 5, 9
.TransmitANSI "1"
.TransmitTerminalKey rcIBMEnterKey
.WaitForEvent rcKbdEnabled, "30", "0", 1, 1
.WaitForEvent rcEnterPos, "30", "0", 18, 33
.WaitForDisplayString "ID:", "30", 18, 29
.TransmitANSI "akhimki"
.TransmitTerminalKey rcIBMTabKey

' Password has not been recorded for security
hostpassword = ""
hostpassword = .GetPassword("PASSWORD:", "", "", "")
.TransmitANSI hostpassword
.TransmitANSI "50.7.13"
.TransmitTerminalKey rcIBMEnterKey
End With
With Session
.WaitForEvent rcEnterPos, "30", "0", 4, 16
.WaitForDisplayString "CITY/SITE:", "30", 4, 4
.TransmitTerminalKey rcIBMTabKey
.TransmitTerminalKey rcIBMTabKey
.Paste
TransmitTerminalKey rcIBMTabKey
.WaitForEvent rcEnterPos, "30", "0", 23, 15
.WaitForDisplayString "SCREEN:", "30", 23, 7
.TransmitTerminalKey rcIBMEnterKey
.WaitForEvent rcKbdEnabled, "30", "0", 1, 1
.WaitForEvent rcEnterPos, "30", "0", 1, 80
.WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67
.TransmitTerminalKey rcIBMNewLineKey
.TransmitTerminalKey rcIBMNewLineKey
.TransmitTerminalKey rcIBMNewLineKey
End With

z = c.Offset(0, 1).Copy
'excelApp.ActiveSheet.Range("C2").Copy

With Session
.WaitForEvent rcEnterPos, "30", "0", 4, 11
.WaitForDisplayString "SEALS..:", "30", 4, 2
.Paste
.TransmitTerminalKey rcIBMEnterKey
.WaitForEvent rcKbdEnabled, "30", "0", 1, 1
'I'm not sure why you have jumoped from Row 2 to Row 3 ?
z = excelApp.ActiveSheet.Range("b3").Copy
With Session
.WaitForEvent rcKbdEnabled, "30", "0", 1, 1
.WaitForEvent rcEnterPos, "30", "0", 3, 11
.WaitForDisplayString "NBR:", "30", 3, 6
.Paste
.TransmitTerminalKey rcIBMEnterKey
.WaitForEvent rcEnterPos, "30", "0", 1, 80
.WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67
.MoveCursor 4, 11
.WaitForDisplayString "SEALS..:", "30", 4, 2
'I'm not sure why you have jumoped from Row 2 to Row 4 ?
z = excelApp.ActiveSheet.Range("C3").Copy
.Paste
.TransmitTerminalKey rcIBMEnterKey
'I'm not sure why you have jumoped from Row 2 to Row 4 ?
z = excelApp.ActiveSheet.Range("B4").Copy
.Paste
.TransmitTerminalKey rcIBMEnterKey
.WaitForEvent rcEnterPos, "30", "0", 1, 80
.WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67
.MoveCursor 4, 11
.WaitForDisplayString "SEALS..:", "30", 4, 2
End With
End With
Next c
 
as i tried to run it it said

Dim c As Range is not Defined do i need to add all the method that created as it is for C as well after Next c ?
 
this is the link of the reflection Screens step by step !

https://docs.google.com/spreadsheet/ccc?key=0Ah3qQ3ZqPOsPdHhlckhsdmctbXIybmhVVkJQZ2VfSWc

i was thinking !...as data is in B & C rite

like this

B C

APZU4626368 APA9919941

TRLU3853663 APA9910157


what if we rearrange the Data like this by only using B column

APZU4626368

APA9919941

TRLU3853663

APA9910157

would that be easy !?? for creating a loop ?
 
Xcruc1at3r


Because your using VB I cannot help you with the format of the Dim Statement

You could just try:

Dim C as Variant


I wouldn't re-arrange your data


Also your file had no data just 2 text boxes describing data that isn't there ?


You may want to use Google to Search for Calling Excel from Visual Basic etc
 
hmm!! when i open it shows the Snapshots and when u are opening it so its not !! i guess its not properly uploaded thanks to Google.doc! let me email it to you if you dont mind ! i have your email address with me :) cause you have helped me a lot...
 
Xcruc1at3r


I understand the issue you are trying to do, but I don't use Visual Basic (VB) and so cannot assist with the format of the Dim statement or how to access Excel from VB


I also don't know what your data format is and how it relates to the application, ie: Are you looping though a list of data and then uploading data for each Row or every second or third Row etc

or

Can you Login once then upload multiple items one after the other, or do you need to login each time.


The above 2 points will determine how to setup your loops


Have a look at the last comment here which shows how to Dimension Ranges from VB

http://www.xtremevbtalk.com/showthread.php?t=68291

or

http://www.dotnetperls.com/excel-vbnet


I feel I cannot assist any further


Hui...
 
we log in once and after that we start working on it for as along as we want and we can move through screens as well... if the software stays idle for 20mins so it logs out...
 
Hui Need your help.... this is executing but stops at

LR = excelApp(WbSource).WorkSheets("yoursheet").Range("B65536").End(xlUp).Row

is not able to find the source !... i have reached this far!! Hui ....

error : object doesn't support this property or method

property let procedure not defined and property get procedure did not return an object

Sub macro_Seal22333()

Dim WbSource As Object

Set excelApp = GetObject(, "Excel.Application")

Dim z As Integer

Dim LR As Integer


Set excelApp = GetObject("C:Documents and SettingsmintezarDesktopBook1.xls")


With Session


.WaitForEvent rcEnterPos, "30", "0", 1, 1

.TransmitTerminalKey rcIBMPf9Key

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1

.WaitForEvent rcEnterPos, "30", "0", 5, 13

.WaitForDisplayString "==>", "30", 5, 9

.TransmitANSI "1"

.TransmitTerminalKey rcIBMEnterKey

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1

.WaitForEvent rcEnterPos, "30", "0", 18, 33

.WaitForDisplayString "ID:", "30", 18, 29

.TransmitANSI "akhimki"

.TransmitTerminalKey rcIBMTabKey

' Provide Password for Security

Dim hostpassword As String

hostpassword = ""

hostpassword = .GetPassword("PASSWORD:", "", "", "")

.TransmitANSI hostpassword

.TransmitANSI "50.7.13"

.TransmitTerminalKey rcIBMEnterKey


LR = excelApp(WbSource).WorkSheets("yoursheet").Range("B65536").End(xlUp).Row 'FInd the last row


For x = 2 To LR 'create a loop then the data from the cells is copied and pasted below.


z = excelApp(WbSource).WorkSheets("yoursheet").Range("B" & x).Copy


.WaitForEvent rcEnterPos, "30", "0", 4, 16

.WaitForDisplayString "CITY/SITE:", "30", 4, 4

.TransmitTerminalKey rcIBMTabKey

.TransmitTerminalKey rcIBMTabKey

.Paste

TransmitTerminalKey rcIBMTabKey

.WaitForEvent rcEnterPos, "30", "0", 23, 15

.WaitForDisplayString "SCREEN:", "30", 23, 7

.TransmitTerminalKey rcIBMEnterKey

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1

.WaitForEvent rcEnterPos, "30", "0", 1, 80

.WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67

.MoveCursor 4, 11


z = excelApp(WbSource).WorkSheets("yoursheet").Range("C" & x).Copy


.WaitForEvent rcEnterPos, "30", "0", 4, 11

.WaitForDisplayString "SEALS..:", "30", 4, 2

.Paste

.TransmitTerminalKey rcIBMEnterKey

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1


Next x


End With


End Sub
 
In your code the line

LR = excelApp(WbSource).WorkSheets("yoursheet").Range("B65536").End(xlUp).Row


The line expects that you have a variable called WbSource and a Worsksheet already setup that is called "Yoursheet"


WbSource is dimensioned as an object but not assigned to anything, which looks odd

Also check that you have a worksheet called "Yoursheet"


As I have mentioned before I am not a VB person and am unfamiliar with calling Excel from VB, and I am sorry I don't have the time to research this any further


Have you looked at some of the Visual Basic forums, I am sure this would be answered quickly in the right forum ?
 
Yes i have a worksheet called "yoursheet"

i am trying that out Hui thanks for your advice

you have rectified the error "WbSource is dimensioned as an object but not assigned to anything, which looks odd"

so what can i do about it ?
 
Have you looked at some of the Visual Basic forums, I am sure this would be answered quickly in the right forum ?
 
Check this one its will help you to solve http://vb.net-informations.com/excel-2007/vb.net_excel_2007_tutorials.htm vb.net excel tutorial for beginners

warner

xcruc1at3r said:
I here any possibility that can we Make it Excel dynamic range cause the excel varies its range? if the code would be really big so it would take load of time and memory as well


Sub macro_22333()


Dim excelApp As Object

Dim z As Integer

Set excelApp = GetObject(, "Excel.Application")

Set excelApp = GetObject("C:Documents and SettingsmintezarDesktopBook1.xlsx")

z = excelApp.ActiveSheet.Range("B2").Copy

With Session

.WaitForEvent rcEnterPos, "30", "0", 1, 1

.TransmitTerminalKey rcIBMPf9Key

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1

.WaitForEvent rcEnterPos, "30", "0", 5, 13

.WaitForDisplayString "==>", "30", 5, 9

.TransmitANSI "1"

.TransmitTerminalKey rcIBMEnterKey

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1

.WaitForEvent rcEnterPos, "30", "0", 18, 33

.WaitForDisplayString "ID:", "30", 18, 29

.TransmitANSI "akhimki"

.TransmitTerminalKey rcIBMTabKey


' Password has not been recorded for security

Dim hostpassword As String

hostpassword = ""

hostpassword = .GetPassword("PASSWORD:", "", "", "")

.TransmitANSI hostpassword

.TransmitANSI "50.7.13"

.TransmitTerminalKey rcIBMEnterKey

End With

With Session

.WaitForEvent rcEnterPos, "30", "0", 4, 16

.WaitForDisplayString "CITY/SITE:", "30", 4, 4

.TransmitTerminalKey rcIBMTabKey

.TransmitTerminalKey rcIBMTabKey

.Paste

TransmitTerminalKey rcIBMTabKey

.WaitForEvent rcEnterPos, "30", "0", 23, 15

.WaitForDisplayString "SCREEN:", "30", 23, 7

.TransmitTerminalKey rcIBMEnterKey

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1

.WaitForEvent rcEnterPos, "30", "0", 1, 80

.WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67

.TransmitTerminalKey rcIBMNewLineKey

.TransmitTerminalKey rcIBMNewLineKey

.TransmitTerminalKey rcIBMNewLineKey

End With

z = excelApp.ActiveSheet.Range("C2").Copy

With Session

.WaitForEvent rcEnterPos, "30", "0", 4, 11

.WaitForDisplayString "SEALS..:", "30", 4, 2

.Paste

.TransmitTerminalKey rcIBMEnterKey

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1

z = excelApp.ActiveSheet.Range("b3").Copy

With Session

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1

.WaitForEvent rcEnterPos, "30", "0", 3, 11

.WaitForDisplayString "NBR:", "30", 3, 6

.Paste

.TransmitTerminalKey rcIBMEnterKey

.WaitForEvent rcEnterPos, "30", "0", 1, 80

.WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67

.MoveCursor 4, 11

.WaitForDisplayString "SEALS..:", "30", 4, 2

z = excelApp.ActiveSheet.Range("C3").Copy

.Paste

.TransmitTerminalKey rcIBMEnterKey

z = excelApp.ActiveSheet.Range("B4").Copy

.Paste

.TransmitTerminalKey rcIBMEnterKey

.WaitForEvent rcEnterPos, "30", "0", 1, 80

.WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67

.MoveCursor 4, 11

.WaitForDisplayString "SEALS..:", "30", 4, 2


End With

End With

End Sub
Click to expand...
 
You must log in or register to reply here.
Back
Top