if syntax

[ram567]

could anybody help me the syntax of the below one
thanks
If Account no = (obj.Worksheets("Sheet1.cells(RW,"B").value)) then

 

[SkipVought]

No spaces in variables...

If Account_no =  obj.Worksheets("Sheet1").cells(RW,"B").value then

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ram567]

Thanks Skip
I have another issue, i have already excel sheet have sheet 1
so i am trying to compare with sheet1 column b to curreent session
but it says the file could not open
here is the code
---------------------------
Set Sessions = System.Sessions
If (Sessions is Nothing) Then sFile = "C:\Documents and Settings\Desktop\Excel.xls"
Dim obj as object
Dim objWorkbook as object
Set obj=CreateObject("Excel.Application")
obj.visible=TRUE
obj.Workbooks.Open sFile
set objWorkbook=obj.Worksheets("Sheet2")
STOP
'End If---------------------------

 

[SkipVought]

If this is another issue, please post in a new thread.

Was this thread's issue resolved?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ram567]

Thanks Skip
the below code is copying into sheet2 only head line
it is not comparing in sheet 1 columb B
could you see what is wrong in this code
Do Until EOF (1)
'Sess0.Screen.GetString(3,20, 25))

AcctNo = Trim(Sess0.Screen.GetString(5, 58, 12))
Acckey= Trim(Sess0.Screen.GetString(8, 80, 1))
OptAcct = Trim(Sess0.Screen.GetString(9, 3, 100)) + Trim(Sess0.Screen.GetString(10, 3, 100))
Optlist = Trim(Sess0.Screen.GetString(13, 16, 100))
Trans = Trim(Sess0.Screen.GetString(13, 80, 1))
Optaccess = Trim(Sess0.Screen.GetString(18,10,1))
Update = Trim(Sess0.Screen.GetString(18,20,1))
Optinsert = Trim(Sess0.Screen.GetString(18,30,1))
Replace = Trim(Sess0.Screen.GetString(18,40,1))
Delete = Trim(Sess0.Screen.GetString(18,50,1))
Move = Trim(Sess0.Screen.GetString(18,60,1))
Overlay = Trim(Sess0.Screen.GetString(18,71,1))
If AcctNo = obj.Worksheets("Sheet1").cells(RW,"B").value then

RW = RW + 1
With obj.Worksheets("Sheet2")
.Cells(Rw, "A").Value = AcctNo
.Cells(Rw, "C").Value = Acckey
.Cells(Rw, "D").Value = OptAcct

.Cells(Rw, "E").Value = OptList
.Cells(Rw, "F").Value = Trans
.Cells(Rw, "G").Value = Optaccess
.Cells(Rw, "H").Value = Optinsert
.Cells(Rw, "I").Value = Replace
.Cells(Rw, "J").Value = Delete
.Cells(Rw, "K").Value = Move
.Cells(Rw, "L").Value = Overlay



obj.Save

End with
Else
End if
Sess0.Screen.SendKeys ("<PF8>") 'next screen
Sess0.Screen.WaitHostQuiet (500)
Loop

 

[SkipVought]

ram,

AcctNo is 12 characters. What EXACT value is is in
[tt]
AcctNo
RW
obj.Worksheets("Sheet1").cells(RW,"B").value
[/tt]
when you execute

If AcctNo =  obj.Worksheets("Sheet1").cells(RW,"B").value then

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ram567]

the same value
it compares with sheet 1 column B row 2 to 109
how do i do that. thanks in advance

 

[ram567]

it excutes only column B row 2 in sheet 1 excel but i need column B row 2 to 109

 

[SkipVought]

Then RW need to incriment from 2 to 109.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ram567]

thanks skip
so do i have to
rw = 2 to 109 rw = rw +1
is that right. pl let me know

 

[ram567]

Hi Skip\for example columm B example data
xxx, xxx,xxx,yyyy,zzzz,zzzz,abcd
first it check xxx, if it is equal write to sheet2
and next also xxx if it checks it does not need to write into sheet 2, it is equal it has to go next record, coudl you explain to me how to do this one. thank you very much for yur help.

 

[SkipVought]

for rw = 2 to 109
  If AcctNo =  obj.Worksheets("Sheet1").cells(RW,"B").value then
     with obj.Worksheets("Sheet2")
       i = 0
       nextrow = .[A1].currentregion.rows.count + 1
       for icol = 1 to .[A1].currentregion.columns.count
          .cells(nextrow, icol).value = vData(i)
          i = i + 1
       next
     end with
  end if
next

where your data scrape results for the row are in array vData

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ram567]

thanks skip
vData i put as vdata array()
how do i declare

 

[SkipVought]

Dim vData() or vData(3) where there are FOUR array elements.

BTW, you DO have VB HELP, which will give you the information you need. Feel free to use your F1 key.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ram567]

thank you so much . i found that.
the belwo one has error no property method found
is that sheet 2 column A1

nextrow = .[A1].currentregion.rows.count + 1
Thanks in advance.

 

[SkipVought]

This statment must be preceeded by a WITH statement, referencing a Worksheet.

     with obj.Worksheets("Sheet2")
       i = 0
       nextrow = .[A1].currentregion.rows.count + 1

Use faq707-4594 as a tool to discover what your objects & variables properties and values are.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ram567]

first of all thank you very much for helping me
this macro is in extra! macro in notepad.
so i will run in extra! eba code. not vba in excel sheet.
here is the whole code
if you find out please help me in this regard. the same property method not found in particular line
nextrow = .[A1].currentregion.rows.count + 1


ub Main
' Get the main system object
Dim Sessions As Object
Dim System As Object
Dim sFile as String
Set System = CreateObject("EXTRA.System") ' Gets the system object
If (System is Nothing) Then
Msgbox "Could not create the EXTRA System object. Stopping macro playback."
STOP
End If
Set Sessions = System.Sessions
If (Sessions is Nothing) Then

Msgbox "Could not create the EXTRA System object. Stopping macro playback."
STOP
End If

sFile = "C:\Documents and Settings\Desktop\Excel.xls"
Dim obj as object
Dim objWorkbook as object
Set obj=CreateObject("Excel.Application")
obj.visible=TRUE
obj.Workbooks.Open sFile
set objWorkbook=obj.Worksheets("Sheet2")

'End If
'--------------------------------------------------------------------------
' Set the default wait timeout value
g_HostSettleTime = 3000 ' milliseconds
OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If

' Get the necessary Session Object
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
If (Sess0 is Nothing) Then
Msgbox "Could not create the Session object.Stopping macro playback."
STOP
End If
If Not Sess0.Visible Then Sess0.Visible = TRUE
'--------------------------------------------------------------------------

'THIS copies the data from the VT session row by row into
'Excel sheet beginning in Cell A2-C2 and downward
Dim ExtraScreen As Object
Dim Journalid as String
Dim Key as String
Dim KeyMask as String
Dim TL as String
Dim Translist as String
Dim Optaccess as String
Dim Update as String
Dim Delete as String
Dim Optinsert as String
Dim Replace as String
Dim Move as String
Dim Overlay as String
Dim vData() as String
Dim i as integer

obj.Worksheets("Sheet2").Cells(1,1).Font.Size = 12
obj.Worksheets("Sheet2").Cells(1,1)= "Journlid"
obj.Worksheets("Sheet2").Cells(1,2)= "KEY"
obj.Worksheets("Sheet2").Cells(1,3)= "KEYMASK"
obj.Worksheets("Sheet2").Cells(1,4)= "TL"
obj.Worksheets("Sheet2").Cells(1,5)= "TRANSLIST"
obj.Worksheets("Sheet2").Cells(1,6)= "ACCESS/DISP"

obj.Worksheets("Sheet2").Cells(1,7)= "UPDATE"
obj.Worksheets("Sheet2").Cells(1,8)= "INSERT"
obj.Worksheets("Sheet2").Cells(1,9)= "REPLACE"

obj.Worksheets("Sheet2").Cells(1,10)= "DELETE"
obj.Worksheets("Sheet2").Cells(1,11)= "MOVE"
obj.Worksheets("Sheet2").Cells(1,12)= "OVERLAY"
obj.Worksheets("Sheet2").Columns("A").ColumnWidth = 13
obj.Worksheets("Sheet2").Columns("B").ColumnWidth = 22
obj.Worksheets("Sheet2").Columns("C").ColumnWidth = 23
obj.Worksheets("Sheet2").Columns("D").ColumnWidth = 30
'obj.Worksheets("Sheet2").Columns("D").RowWidth = 20
obj.Worksheets("Sheet2").Columns("E").ColumnWidth = 31
obj.Worksheets("Sheet2").Columns("F").ColumnWidth = 11
obj.Worksheets("Sheet2").Columns("G").ColumnWidth =6
obj.Worksheets("Sheet2").Columns("H").ColumnWidth = 6
obj.Worksheets("Sheet2").Columns("I").ColumnWidth =7
obj.Worksheets("Sheet2").Columns("J").ColumnWidth =6
obj.Worksheets("Sheet2").Columns("K").ColumnWidth =4
obj.Worksheets("Sheet2").Columns("L").ColumnWidth = 8

RW = 3
Do
'Sess0.Screen.GetString(3,20, 25))



Journlid = Trim(Sess0.Screen.GetString(5, 58, 12))
Key = Trim(Sess0.Screen.GetString(8, 80, 1))
KeyMask = Trim(Sess0.Screen.GetString(9, 3, 100)) + Trim(Sess0.Screen.GetString(10, 3, 100))
Tl = Trim(Sess0.Screen.GetString(13, 16, 100))
TransList = Trim(Sess0.Screen.GetString(13, 80, 1))
Optaccess = Trim(Sess0.Screen.GetString(18,10,1))
Update = Trim(Sess0.Screen.GetString(18,20,1))
Optinsert = Trim(Sess0.Screen.GetString(18,30,1))
Replace = Trim(Sess0.Screen.GetString(18,40,1))
Delete = Trim(Sess0.Screen.GetString(18,50,1))
Move = Trim(Sess0.Screen.GetString(18,60,1))
Overlay = Trim(Sess0.Screen.GetString(18,71,1))
FOR RW = 2 to 109
If Passkey = obj.Worksheets("Sheet1").cells(RW,"B").value then
With obj.Worksheets("Sheet2")
i = 0
nextrow = .[A1].currentregion.rows.count -1

for icol = 3 to .[A1].currentregion.columns.count
.cells(nextrow, icol).value = vData(i)
i = i + 1

Next
RW = RW + 1


.Cells(Rw, "A").Value = journlid
.Cells(Rw, "B").Value = Key
.Cells(Rw, "C").Value = KeyMask
.Cells(Rw, "D").Value = TL
.Cells(Rw, "E").Value = TransList
.Cells(Rw, "F").Value = Optaccess
.Cells(Rw, "G").Value = Update
.Cells(Rw, "H").Value = Optinsert
.Cells(Rw, "I").Value = Replace
.Cells(Rw, "J").Value = Delete
.Cells(Rw, "K").Value = Move
.Cells(Rw, "L").Value = Overlay


'obj.Worksheets("Sheet2").Cells.Sort Key1:=obj.Worksheets("Sheet2").Columns("D"), Header:= 1
obj.Save

End with

Else
End if
Next
Sess0.Screen.SendKeys ("<PF8>") 'next screen
Sess0.Screen.WaitHostQuiet (100)
Loop
error_exit:
obj.Quit
if err then
msgbox sFile + "was not replaced"
else
msgbox "Created " + sFile
End if

Exit Sub
End Sub

 

[IknowMe]

Ram, I give you an 'A' for persistance.

Skip, I'm dying to see your answer

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[ram567]

Mr Milson
thank you so much. Really i am learning and in my work i would like to do somehting special. that'why i am trying to do this. the above code Could you see that what is wrong in it. i tried this one it says no property no method found. where is wrong in that line?
i am expecting your answer in this regards

 

[ram567]

Really really WIHT THIS WEBSITE AND WITH YOU GUYS (GENIOUS) IT WILL BE USEFUL FOR SO MANY PEOPLE LIKE ME.
THANK YOU SO MUCH FOR THIS WEBSITE AND GREAT HELP FOR YOU GUYS.