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
thanks
If Account no = (obj.Worksheets("Sheet1.cells(RW,"B").value)) then
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS Contact USThanks. We have received your request and will respond promptly. Come Join Us!Are you a
Computer / IT professional? Join Tek-Tips Forums!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting Guidelines |
|
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.
RE: if syntax
No spaces in variables...
CODE
Skip,
When a diminutive clarvoyant had disappeared from detention, headlines read...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Small Medium at Large
RE: if syntax
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,
When a diminutive clarvoyant had disappeared from detention, headlines read...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Small Medium at Large
RE: if syntax
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
Skip,
When a diminutive clarvoyant had disappeared from detention, headlines read...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Small Medium at Large
RE: if syntax
it compares with sheet 1 column B row 2 to 109
how do i do that. thanks in advance
RE: if syntax
RE: if syntax
Then RW need to incriment from 2 to 109.
Skip,
When a diminutive clarvoyant had disappeared from detention, headlines read...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Small Medium at Large
RE: if syntax
so do i have to
rw = 2 to 109 rw = rw +1
is that right. pl let me know
RE: if syntax
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
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
Skip,
When a diminutive clarvoyant had disappeared from detention, headlines read...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Small Medium at Large
RE: if syntax
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,
When a diminutive clarvoyant had disappeared from detention, headlines read...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Small Medium at Large
RE: if syntax
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
i = 0
nextrow = .[A1].currentregion.rows.count + 1
Skip,
When a diminutive clarvoyant had disappeared from detention, headlines read...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Small Medium at Large
RE: if syntax
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
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
RE: if syntax
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
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,
When a diminutive clarvoyant had disappeared from detention, headlines read...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Small Medium at Large
RE: if syntax
CODE
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
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
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
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
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
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
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
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
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
CODE
CODE
file_name = "C:\Documents and Settings\WinblowsME\Desktop\Excel.xls" ' Me
CODE
MsgBox field_value
field_value = GetField ( "ABC,DEF,GHI", 2, "," ) ' Pulls field 2
MsgBox field_value
RE: if syntax
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
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
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
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
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))