×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

if syntax

if syntax

if syntax

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

RE: if syntax




No spaces in variables...

CODE

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

Skip,

glasses When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Largetongue

RE: if syntax

(OP)
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---------------------------

RE: if syntax





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

Was this thread's issue resolved?

Skip,

glasses When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Largetongue

RE: if syntax

(OP)
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

RE: if syntax






ram,

AcctNo is 12 characters.  What EXACT value is is in

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

when you execute

CODE

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

Skip,

glasses When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Largetongue

RE: if syntax

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

RE: if syntax

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

RE: if syntax





Then RW need to incriment from 2 to 109.

Skip,

glasses When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Largetongue

RE: if syntax

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

RE: if syntax

(OP)
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.

RE: if syntax




CODE

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,

glasses When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Largetongue

RE: if syntax

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

RE: if syntax




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,

glasses When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Largetongue

RE: if syntax

(OP)
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.

RE: if syntax



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

CODE

     with obj.Worksheets("Sheet2")
       i = 0
       nextrow = .[A1].currentregion.rows.count + 1
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,

glasses When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Largetongue

RE: if syntax

(OP)
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

RE: if syntax

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

Skip, I'm dying to see your answer wink

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

RE: if syntax

(OP)
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

RE: if syntax

(OP)
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.

RE: if syntax




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 Largetongue

RE: if syntax

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

   file_name      = "C:\Excel.xls"

   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
            ' Not sure if this is what you want
            If UCase(journal_id) = UCase(curr_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
               next_row = next_row + 1
               Exit For
            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

Sub Wait(Sess As Object)
   Do While Sess.Screen.OIA.Xstatus <> 0
      DoEvents
   Loop
End Sub

RE: if syntax

(OP)
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

RE: if syntax

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?

RE: if syntax

(OP)
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

RE: if syntax

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?

RE: if syntax

(OP)
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

               

RE: if syntax

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

   file_name      = "C:\Excel.xls"
   
   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
      
   '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))

      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

   
   '   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

Sub Wait(Sess As Object)
   Do While Sess.Screen.OIA.Xstatus <> 0
      DoEvents
   Loop
End Sub

RE: if syntax

(OP)
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

RE: if syntax

(OP)
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

RE: if syntax

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

RE: if syntax

(OP)
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"
   

RE: if syntax

(OP)
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

   

RE: if syntax

(OP)
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.

RE: if syntax

(OP)
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

RE: if syntax

(OP)
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))

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close