ADDING 10 SHEETS

[braven]

after sheet 1 how to add 10 sheets
the below code i tried it gives error no such property or method. but i declard as object
Set xl_sheet_1 = xl_wb.Sheets("Sheet1")
xl_sheet_1.Add After:=xl_sheet_1(xl_sheet_1.Count), Count:=10

 

[braven]

type error here. but when in macro it is right only.

 

[SkipVought]

Please listen!

You have a problem with consistency! SOMEWHERE, you have an inconsistency. Find it!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[braven]

thank you skip, i will do that sure and let you know. thank you very much for your great help

 

[SkipVought]

Furthermore, why would ANY of the code that you post here, be any different than the code that you are running?

Do you not copy 'n' paste what is in your code editor, EXACTLY as it is?

Surely you would not TYPE stuff, would you?

Please do not post code, that is not COPIED from your code editor, or that you have modified by TYPING in this forum.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[braven]

Hi Skip
i changed all the consistency and now the final code here.
but here what happens ieven change the city name and ocrresponding rows and colum in the same row 1 and row2
if it changes it goes ot column 3 row 3 and row 4
i tried but it does not work out.
sCity = Trim(Sess.Screen.GetString(5,20,5))

If sPrevCity <> sCity Then
J = J +1
x1_ws.Cells(j,RW) = Trim(Sess.Screen.GetString(5, 12, 13))

For i = 8 To 22
Name = Trim(Sess.Screen.GetString(i, 12, 10))
Accountid = Trim(Sess.Screen.GetString(i, 34, 19))
j = j+1

x1_ws.Cells(j, RW).Value = Name
x1_ws.Cells(j, RW+1 ).Value = Accountid

sPrevCity = sCity

Next

End if
if Ucase(Sess.Screen.GetString(24, 8, 11)) = "END OF LIST" then
Sess.Screen.SENDKEYS("<Enter>")
exit sub
End if


Sess.Screen.SendKeys ("<PF8>")
Call Wait(Sess)

Loop

 

[braven]

SKIP !
thanks a lot there is row and column problem in excel sheet. thanks a lot . you helped me . please look at this below sameple here
Name Accountid
CITY-> NEWYORK
alfanso 123456
akbnto 4578945
cantofc 4579797979
fdfdfj 78797977
fdfdfj 4567877
fdfldjljdlgjlg 1346464464
fjdjdlgjdlgj 4466446
gdlgjdlgjljgl 44644
glgjldjgl 146664
gjjgljldj 4646466
ghdlgjldjgldj 797979797
gjdgjldjl 79979878
gjldjdlgljg 797997
g;dgdgjj 797977777
city-> WASHINTON

alfanso 123456
akbnto 4578945
cantofc 4579797979
fdfdfj 78797977
fdfdfj 4567877
fdfldjljdlgjlg 1346464464
fjdjdlgjdlgj 4466446
gdlgjdlgjljgl 44644
glgjldjgl 146664
gjjgljldj 4646466
ghdlgjldjgldj 797979797
gjdgjldjl 79979878
gjldjdlgljg 797997
g;dgdgjj 797977777
but it should be
like this third column adn fourth column
CITY-> NEWYORK city-> WASHINTON
alfanso 123456 alfanso 123456
akbnto 4578945 akbnto 4578945
cantofc 4579797979 cantofc 4579797979
fdfdfj 78797977 fdfdfj 78797977
the code is here
If sPrevCity <> sCity Then
' J = J +1
RW = RW +1
x1_ws.Cells(RW,j) = Trim(Sess.Screen.GetString(5, 12, 13))


For i = 8 To 22
Journalid = Trim(Sess.Screen.GetString(i, 12, 10))
Description = Trim(Sess.Screen.GetString(i, 34, 19))



' j = j+1
RW = RW +1


x1_ws.Cells(RW, j).Value = Name
x1_ws.Cells(RW , j+1).Value = Accountid

sPrevCity = sCity

Next


End if

 

[SkipVought]

Is there a problem? If so, please explain clearly.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[braven]

for example
city name is newyork
excel sheet row3 to 26 name, account id column a, b
another city name is washinton
it should be row3 to 26 but name and account id should be column c and d
next city column should be e and f
but the above code it goes to row 3 to 26 and keep on going in the same row

 

[SkipVought]

That does not make any sense to me.

another city name is washinton
it should be row3 to 26 but name and account id should be column c and d
next city column should be e and f
but the above code it goes to row 3 to 26 and keep on going in the same row

Huh?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[braven]

skip
city newyork
row3 colum A =-NAME
ROW3 COLUMN B - ACCOUNT Id
next city
should be row3 column c name
and row3 column d acccount id
and next city
row3 column e name
row 3 column f account ig
row are 8 3 to 26 in excel
my code goes row3 column a - name
row3 column b - account id
and next city
goes row 27 and column a - name
row 27 and column b - account id

 

[SkipVought]

Looking at your code, the row loop on the screen belongs outside the test for precious city, and the assignment for previous city als belongs outside the loop.

I assume that this is all INSIDE the the SCREEN loop.

    If sPrevCity <> sCity Then
      ' J = J +1
        RW = 3
        x1_ws.Cells(RW, j) = Trim(Sess.Screen.GetString(5, 12, 13))
    End If
    For i = 8 To 22
      Journalid = Trim(Sess.Screen.GetString(i, 12, 10))
      Description = Trim(Sess.Screen.GetString(i, 34, 19))
       
    
    
       ' j = j+1
       RW = RW + 1
                      
             
      x1_ws.Cells(RW, j).Value = Name
          x1_ws.Cells(RW, j + 1).Value = Accountid
      
          
    Next
    sPrevCity = sCity

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[braven]

i tried that also it goes in same row only not changing into column

 

[SkipVought]

What does that mean? You are never very clear!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[braven]

skipafter next
or before next
sPrevCity = sCity
this one has not changed row to coloumn
with name and accountid in excel sheet instead of rows it should be in next column with new city name and correspnind name and decrioton would be the rows 3 to 26

 

[braven]

hi skip
i dont have screen problem when it writes into excel name and accountid did not write in column "c" and row 3 to 26 ratjer it writes only in rows in 28th

 

[SkipVought]

Are you saying that when the city changes, then the column pair does not change and the new city is not put in the new column? Is the row also not resetting to 3?

Please post ALL your code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[braven]

SKIP
thank you once again. here is the code:
Sub Main()


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

Sess.Screen.SENDKEYS("<Home>GD<Tab>*<EraseEOF><Tab>abcdEnter>")

file_name = "C:\Documents \TESTlist.xls."
sFile = file_name
Set xl = CreateObject("Excel.Application")
'Set xl_wb = xl.Workbooks.Open(file_name)
Set xl_wb = xl.Workbooks.Add

xl_wb.SaveAs(sFile)
Set x1_ws = xl_wb.Sheets("Sheet1")
x1_ws.Name = "A"
xl.Visible = True
xl.DisplayAlerts = False

x1_ws.Range("A").EntireRow.Font.Size = 10

x1_ws.Columns("A").ColumnWidth = 13
x1_ws.Columns("B").ColumnWidth = 25

x1_ws.Cells(1,1)= "Name"
x1_ws.Cells(1,2)= "Account ID"

j = 1
RW = 1
last_row = x1_ws.Range("B1").CurrentRegion.Rows.Count

Do

sCity = Trim(Sess.Screen.GetString(5,20,5))

If sPrevCity <> sCity Then

RW = RW +1
x1_ws.Cells(RW,j) = Trim(Sess.Screen.GetString(5, 12, 13))
End if
For i = 8 To 22
Name = Trim(Sess.Screen.GetString(i, 12, 10))
Accountid = Trim(Sess.Screen.GetString(i, 34, 19))

RW = RW +1

x1_ws.Cells(RW, j).Value = Name

x1_ws.Cells(RW , j+1).Value = Accountid

Next

sPrevCity = sCity

if Ucase(Sess.Screen.GetString(24, 8, 11)) = "END OF LIST" then
Sess.Screen.SENDKEYS("<Enter>")

exit sub
End if

Sess.Screen.SendKeys ("<PF8>")
Call Wait(Sess)

Loop
Call Wait(Sess)
End Sub


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

 

[braven]

skip
do i have any luck in this regard.

 

[SkipVought]

I could not possible know. What are you looking for?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}