Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

if syntax

Status
Not open for further replies.
ram567

ram567

Programmer
Dec 28, 2007
123
US
could anybody help me the syntax of the below one
thanks
If Account no = (obj.Worksheets("Sheet1.cells(RW,"B").value)) then
 
Sort by date Sort by votes



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,

[glasses] When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[tongue]
 
Upvote 0 Downvote
Here's a wild stab.
Code: 
' Untested

Declare Sub Wait(Sess As Object)

Sub Main()
   Dim Sys As Object, Sess As Object

   Set Sys = CreateObject("Extra.System")

   If Sys Is Nothing Then
      MsgBox ("Could not create Extra.System...is E!PC installed on this machine?")
      Exit Sub
   End If

   Set Sess = Sys.ActiveSession

   If Sess Is Nothing Then
      MsgBox ("No session available...stopping macro playback.")
      Exit Sub
   End If

   ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

   Dim xl As Object, xl_wb As Object, xl_sheet_1 As Object, xl_sheet_2 As Object, file_name As String
   Dim curr_id As String, journal_id As String, key As String, key_mask As String
   Dim tl As String, trans_list As String, opt_access As String, update As String
   Dim delete As String, opt_insert As String, replace As String, move As String, overlay As String
   Dim i As Integer, next_row As Integer

   [COLOR=red]file_name      = "C:\Excel.xls"[/color]

   Set xl         = CreateObject("Excel.Application")
   Set xl_wb      = xl.Workbooks.Open(file_name)
   Set xl_sheet_1 = xl_wb.Sheets("Sheet1")
   Set xl_sheet_2 = xl_wb.Sheets("Sheet2")

   xl.Visible       = True
   xl.DisplayAlerts = False

   xl_sheet_2.Range("1:1").EntireRow.Font.Size = 12
   xl_sheet_2.Cells(1, 1)  = "JOURNAL ID"
   xl_sheet_2.Cells(1, 2)  = "KEY"
   xl_sheet_2.Cells(1, 3)  = "KEY MASK"
   xl_sheet_2.Cells(1, 4)  = "TL"
   xl_sheet_2.Cells(1, 5)  = "TRANSLIST"
   xl_sheet_2.Cells(1, 6)  = "ACCESS/DISP"
   xl_sheet_2.Cells(1, 7)  = "UPDATE"
   xl_sheet_2.Cells(1, 8)  = "INSERT"
   xl_sheet_2.Cells(1, 9)  = "REPLACE"
   xl_sheet_2.Cells(1, 10) = "DELETE"
   xl_sheet_2.Cells(1, 11) = "MOVE"
   xl_sheet_2.Cells(1, 12) = "OVERLAY"

   next_row = xl_sheet_2.UsedRange.Rows.Count + 1

   '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 109
         curr_id = Trim(xl_sheet_1.Cells(i, "B").Value)

         If curr_id <> "" Then
            [COLOR=red]' Not sure if this is what you want
            If UCase(journal_id) = UCase(curr_id) Then[/color]
               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
               next_row = next_row + 1
               Exit For
            End If
         End If
      Next

   '   Sess.Screen.SendKeys ("<PF8>") 'next screen
   '   Call Wait(Sess)
   '[COLOR=red]Loop While UCase(Sess.Screen.GetString(24, 1, 9)) <> "LAST PAGE"[/color]
   
   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

Sub Wait(Sess As Object)
   Do While Sess.Screen.OIA.Xstatus <> 0
      DoEvents
   Loop
End Sub
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
I'm not sure what you're trying to do.
Code: 
----------        --------        -----------------------
Screen            Sheet1          Sheet2
journal_id        Column B        Columns A-L
----------        --------        -----------------------
AAA               123             "AAA", key, key_mask...
                  ABC
                  XYC

So, if AAA pops up on the screen and it's not on Sheet1, add it to Sheet2?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
WHAT I NEED IT I COMPARE WITH G AND WRITE IN SHEET2
Click to expand...
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?
 
Upvote 0 Downvote
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


 
Upvote 0 Downvote
Code: 
'Untested

Declare Sub Wait(Sess As Object)

Sub Main()
   Dim Sys As Object, Sess As Object

   Set Sys = CreateObject("Extra.System")

   If Sys Is Nothing Then
      MsgBox ("Could not create Extra.System...is E!PC installed on this machine?")
      Exit Sub
   End If

   Set Sess = Sys.ActiveSession

   If Sess Is Nothing Then
      MsgBox ("No session available...stopping macro playback.")
      Exit Sub
   End If

   ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

   Dim dict As Object
   Dim xl As Object, xl_wb As Object, xl_sheet_1 As Object, xl_sheet_2 As Object, file_name As String
   Dim curr_id As String, journal_id As String, key As String, key_mask As String
   Dim tl As String, trans_list As String, opt_access As String, update As String
   Dim delete As String, opt_insert As String, replace As String, move As String, overlay As String
   Dim i As Integer, next_row As Integer, last_row As Integer

   [COLOR=red]file_name      = "C:\Excel.xls"[/color]
   
   Set dict       = CreateObject("Scripting.Dictionary")
   Set xl         = CreateObject("Excel.Application")
   Set xl_wb      = xl.Workbooks.Open(file_name)
   Set xl_sheet_1 = xl_wb.Sheets("Sheet1")
   Set xl_sheet_2 = xl_wb.Sheets("Sheet2")

   xl.Visible       = True
   xl.DisplayAlerts = False

   xl_sheet_2.Range("1:1").EntireRow.Font.Size = 12
   xl_sheet_2.Cells(1, 1)  = "JOURNAL ID"
   xl_sheet_2.Cells(1, 2)  = "KEY"
   xl_sheet_2.Cells(1, 3)  = "KEY MASK"
   xl_sheet_2.Cells(1, 4)  = "TL"
   xl_sheet_2.Cells(1, 5)  = "TRANSLIST"
   xl_sheet_2.Cells(1, 6)  = "ACCESS/DISP"
   xl_sheet_2.Cells(1, 7)  = "UPDATE"
   xl_sheet_2.Cells(1, 8)  = "INSERT"
   xl_sheet_2.Cells(1, 9)  = "REPLACE"
   xl_sheet_2.Cells(1, 10) = "DELETE"
   xl_sheet_2.Cells(1, 11) = "MOVE"
   xl_sheet_2.Cells(1, 12) = "OVERLAY"

   last_row = xl_sheet_1.Range("B1").CurrentRegion.Rows.Count
   next_row = xl_sheet_2.Range("A1").CurrentRegion.Rows.Count + 1

   For i = 2 To last_row
      curr_id = Trim(xl_sheet_1.Cells(i, "B").Value)

      If curr_id <> "" And Not dict.Exists(curr_id) Then
         dict.item(curr_id) = curr_id
      End If
   Next
      
   [COLOR=red]'Do[/color]  
      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))

      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

   [COLOR=red]
   '   Sess.Screen.SendKeys ("<PF8>") 'next screen
   '   Call Wait(Sess)
   'Loop While UCase(Sess.Screen.GetString(24, 1, 9)) <> "LAST PAGE"[/color]
   
   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

Sub Wait(Sess As Object)
   Do While Sess.Screen.OIA.Xstatus <> 0
      DoEvents
   Loop
End Sub
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Did you change the following line?
Code: 
   file_name      = "C:\Excel.xls"
Check the path to the Excel file.
Code: 
   file_name      = "C:\Documents and Settings\Desktop\Excel.xls"            ' You
   file_name      = "C:\Documents and Settings\[COLOR=red]WinblowsME[/color]\Desktop\Excel.xls" ' Me
If you know which field you want from the line that is delimited with commas, you can use the following.
Code: 
   field_value = GetField ( "ABC,DEF,GHI", 1, "," ) ' Pulls field 1
   MsgBox field_value 
   
   field_value = GetField ( "ABC,DEF,GHI", 2, "," ) ' Pulls field 2
   MsgBox field_value
 
Upvote 0 Downvote
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"

 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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))
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

CADD6953
  • Locked
  • Question
Help needed with syntax/logic in Attachmate macro
2
Replies
26
Views
604
SkipVought
S
Gutarm
  • Locked
  • Question
If Then Statement
Replies
1
Views
96
SkipVought
S
2009luca
  • Locked
  • Question
check if session already is opened and visible true..
Replies
1
Views
109
SkipVought
S
csilvern12
  • Locked
  • Question
Use IF statement to loop send key
Replies
12
Views
225
csilvern12
csilvern12
link99sbc
  • Locked
  • Question
IF Statements help
Replies
5
Views
74
link99sbc
link99sbc

Part and Inventory Search

Sponsor

Back
Top