No spaces in variables...
 

Skip,

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

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

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}

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

ram,

AcctNo is 12 characters.  What EXACT value is is in

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

when you execute
 

Skip,

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

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

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

Then RW need to incriment from 2 to 109.

Skip,

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

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

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.

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}

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

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}

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.

This statment must be preceeded by a WITH statement, referencing a Worksheet.
Use FAQ707-4594: How to use the Watch Window as a Power Programming Tool 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}

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

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

Skip, I'm dying to see your answer

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

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

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.

I never write macros in Extra.  All my Extra screen scapers are written in Excel VBA.

That being said, what do you get in the Watch Window for obj.Worksheets("Sheet2")

Skip,

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

Here's a wild stab.

winblow
thanks a lot, i removed the two codes
If curr_id <> "" Then
            ' Not sure if this is what you want
            If UCase(journal_id) = UCase(curr_id) Then
  
and what happens  
   curr_id = Trim(xl_sheet_1.Cells(i, "B").Value)
 the above code has duplicate
the same thing unique
curr_id = Trim(xl_sheet_1.Cells(i, "G").Value)
           
I CHANGED THE ABOVE ONE BUT IT COEMS WITH DUPLICATE JOURNAL ID , COUDL YO SEE THAT WAHT I HAVE TO CHANGE HERE

I'm not sure what you're trying to do.

So, if AAA pops up on the screen and it's not on Sheet1, add it to Sheet2?

winblow
i have excel sheet1
A   B   C   D  E  G
B HAS JOURNAL ID
AND G HAS ALSO JOURNAL ID
B HAS INCLUDING DUPLICATE JOURNLA ID
G HAS ONLY UNIQUE JORNAL ID
WHAT I NEED IT I COMPARE WITH G AND WRITE IN SHEET2
BUT IT WILL TAKE ONLY COLUMN B
IS THAT THE SAME JOURNAL ID NAME IN B AND G
THAT'WHY
I HAD EARLIER CODE HAS UNIQUE
SO IT SEPARTE THEM FROM B TO G

Quote:

WHAT I NEED IT I COMPARE WITH G AND WRITE IN SHEET2

Compare what with Sheet 1, column G?  The journal_id from the screen?

So, if the journal_id from the screen matches the value in column G of Sheet 1, do you want to insert the screen info into Sheet 2?

curr_id = Trim(xl_sheet_1.Cells(i, "G").Value)
 If UCase(journal_id) = UCase(curr_id) Then
so the above code compare with sheet1 Column G
right????
what i changed here
if   Trim(Sess.Screen.GetString(5, 58, 12)) = curr_id
is that right? please let me know
thanks a lot once again helpming me Winblow

               

thanks Winblow
i will try tomorrow and let you know the result
and  shall i include this function  in the above code
becasue Key masks containts , so that this below function if i add it t will separe it and put it in next column or last column. obviously  have to include xl_sheet_2.Cells(next_row, "M").Value = overlay
could you advise me
thank you once agin, will come back to you tomrorow.

Declare Sub Split(ByRef arr() as Variant, target as String, search as String)
Declare Sub Print_Array(arr() as Variant)

Sub Main()
   Dim arr() As Variant

   Call Split(arr, "xxxyz, xxxyz1234,xxxx7352,yyyME,YYYMEZ10", ",")
   Call Print_Array(arr)
   MsgBox "Done"

   Call Split(arr, "a@b@c@d@e", "@")
   Call Print_Array(arr)
   MsgBox "Done"
End Sub

Private Sub Split(ByRef arr() As Variant, target As String, search As String)
   Dim i As Integer, j As Integer

   i = InStr(1, target, search)
   j = 1

   Do While i > 0
      ReDim Preserve arr(j)
      arr(j) = Trim(Mid(target, 1, i - 1))
      target = Mid(target, i + 1)
      i      = InStr(1, target, search)
      j      = j + 1
   Loop

   ReDim Preserve arr(j)
   arr(j) = target
End Sub

Private Sub Print_Array(arr() As Variant)
   Dim i As Integer

   For i = 1 To UBound(arr)
      MsgBox arr(i)
   Next
End Sub

Winblow
i tried the above which you provided me.macro runs and fraction of moment its done and it nos writing into excel, and there is no error showing

Did you change the following line?
Check the path to the Excel file.
If you know which field you want from the line that is delimited with commas, you can use the following.

Winblow
the path is ok it is not problem but the macro is wriitng onely the present screen and macro done, i put do and
   Sess.Screen.SendKeys ("<PF8>") 'next screen
   Call Wait(Sess)
Loop While UCase(Sess.Screen.GetString(24, 1, 9)) <> "LAST PAGE"
   

Winblow
Thanks for your help
i put the below line under do what happens the current screen write into excel and then the macro done. it is not going to next screen. and that screen also not matching with sheet1.Column B
could you see that what is wrong in it?

Do  
      journal_id = Trim(Sess.Screen.GetString(5, 58, 12))
      key        = Trim(Sess.Screen.GetString(8, 80, 1))
      key_mask   = Trim(Sess.Screen.GetString(9, 3, 100)) + Trim(Sess.Screen.GetString(10, 3, 100))
      tl         = Trim(Sess.Screen.GetString(13, 16, 100))
      trans_list = Trim(Sess.Screen.GetString(13, 80, 1))
      opt_access = Trim(Sess.Screen.GetString(18, 10, 1))
      update     = Trim(Sess.Screen.GetString(18, 20, 1))
      opt_insert = Trim(Sess.Screen.GetString(18, 30, 1))
      replace    = Trim(Sess.Screen.GetString(18, 40, 1))
      delete     = Trim(Sess.Screen.GetString(18, 50, 1))
      move       = Trim(Sess.Screen.GetString(18, 60, 1))
      overlay    = Trim(Sess.Screen.GetString(18, 71, 1))
For i = 2 To last_row
      curr_id = Trim(xl_sheet_1.Cells(i, "B").Value)
    If Not dict.Exists(curr_id) Then
        dict.item(curr_id) = curr_id

      If Not dict.Exists(journal_id) Then
         xl_sheet_2.Cells(next_row, "A").Value = journal_id
         xl_sheet_2.Cells(next_row, "B").Value = key
         xl_sheet_2.Cells(next_row, "C").Value = key_mask
         xl_sheet_2.Cells(next_row, "D").Value = tl
         xl_sheet_2.Cells(next_row, "E").Value = trans_list
         xl_sheet_2.Cells(next_row, "F").Value = opt_access
         xl_sheet_2.Cells(next_row, "G").Value = update
         xl_sheet_2.Cells(next_row, "H").Value = opt_insert
         xl_sheet_2.Cells(next_row, "I").Value = replace
         xl_sheet_2.Cells(next_row, "J").Value = delete
         xl_sheet_2.Cells(next_row, "K").Value = move
         xl_sheet_2.Cells(next_row, "L").Value = overlay

         dict.item(journal_id) = journal_id
         xl_sheet_1.Cells(last_row + 1, "B").Value = journal_id
         
         last_row = xl_sheet_1.Range("B1").CurrentRegion.Rows.Count         
         next_row = next_row + 1
      End If
      End if
   Next
   
     Sess.Screen.SendKeys ("<PF8>") 'next screen
     Call Wait(Sess)
  Loop While UCase(Sess.Screen.GetString(24, 1, 9)) <> "LAST PAGE"
   
   xl_sheet_2.Range("1:12").EntireColumn.AutoFit
   
   xl_wb.Save
   xl_wb.Close
   xl.Quit

   Set xl_sheet_2 = Nothing
   Set xl_sheet_1 = Nothing
   Set xl_wb      = Nothing
   Set xl         = Nothing
   Set Sess       = Nothing
   Set Sys        = Nothing
End Sub

   

Winblow
it works great!!!!!! thank you very much. now i amtrying to delimiter comma, as you had give the above code
what i did
the same thing your code it has given everyhting not unique records
and  i remove NOt
in the below code
 If Not dict.Exists(journal_id) Then
it works great. Thank you very much once again fo rall your help.

Winblow
is it right? it says cannot convert into ineger

 If GetField ( Ucase(Sess.Screen.GetString(9, 3, 77)) + UCase(Sess.Screen.GetString(10, 3, 77)), 1, "," )  then
         xl_sheet_2.Cells(next_row, "C").Value
        Else
          
          GetField GetField ( Ucase(Sess.Screen.GetString(9, 3, 77)) + UCase(Sess.Screen.GetString(10, 3, 77)), 2, "," ) ' Pulls field 2
          xl_sheet_2.Columns(next_row,"M)..Value

Winblow
thanks for your help
if the key_mask has more than in row  exceed autofit, is it posssible it itwll come adjust next row automatically
 key_mask   = Trim(Sess.Screen.GetString(9, 3, 100)) + Trim(Sess.Screen.GetString(10, 3, 100))