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

 

[SkipVought]

Hi,

Set xl_sheet_1 = xl_wb.Sheets("Sheet1")
 xl_wb.Sheets.Add After:=xl_wb.Sheets(xl_wb.Sheets.Count), Count:=10

Skip,

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

 

[braven]

thanks Skip works great

 

[braven]

hi skip
could you pleasegive me the solution which i posted page by page
thanks skip

 

[braven]

sorry skip how to name each sheets do i have to sepate sheet name or the above code name: "a", "B" and so on

 

[SkipVought]

Please explain what you need in detail. I don't understand your question.

Skip,

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

 

[braven]

FIRST OF ALL thank you very much for looking into my query.
i have 10 screens
each screen grab into excel sheet.
each screen has so many pages.
if the city name is differnt same sheet and different column with corresponding row and column
city name has 5,20,5 25 the same posion in all screen and all pages
corresponding rows 8 to 22 column 12 to 39
i did it so far create excel sheet and grab first screen all pages in column 1 and column 2 first screen there is no no different city name so it came along everythng in column 1 and column2
created second sheet also and second screen has 10 pages first two has same city and third page has different city from first one
so sheet2 column 1 adn column 2 grab frist 2 pages. i did
and third page has to go third column with name and accountnnumner column 3 and column 4 and so on. how to do it>

 

[braven]

skip
so far i have the below code: for the above query
Sub Main()
Dim xl_sheet_1 As Object
Dim L as Integer, next_row1 As Integer, last_row1 As Integer
Dim x1_sheet_1 as Object, xl_sheet_2 as Object
Dim Name As String, Accountid As String
Dim aScreens(13) As String
Dim sScreen As String, RW AS INTEGER
Dim iScreen As Integer
Dim k As Integer, next_row As Integer, last_row2 As Integer
Dim xl As Object, xl_wb As Object, file_name As String

Dim i As Integer, j As Long,
Dim dict As Object
Dim Count As Variant

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

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'aScreens(0) = "AK"
aScreens(1) = "BK"
aScreens(2) = "CK"
aScreens(3) = "DK"
aScreens(4) = "EK"
aScreens(5) = "FKL"
aScreens(6) = "GK"
aScreens(7) = "LK"
aScreens(8) = "MK"
aScreens(9) = "NK"

For aScreen = 0 to 9

Sess.Screen.SENDKEYS("<Enter>")
For iScreen = 1 To UBound(aScreens)
sScreen = aScreens(iScreen)
Sess.Screen.SendKeys sScreen
Sess.Screen.SENDKEYS("<Home><BackTab>sScreen<Enter>")

Sess.Screen.SENDKEYS("<Enter>")

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


file_name = "C:\Documents and Settings\LIST.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 xl_sheet_1 = xl_wb.Sheets("Sheet1")
xl_wb.Sheets.Add After:=xl_wb.Sheets(xl_wb.Sheets.Count), Count:=10

x1_sheet_1.Name = "A1"
xl_sheet_2.Name = "B1"
xl_sheet_3.Name = "C1"

xl.Visible = True
xl.DisplayAlerts = False


' xl_sheet_1.Range("A").EntireRow.Font.Size = 9

xl_sheet_1.Columns("A").ColumnWidth = 17
xl_sheet_1.Columns("B").ColumnWidth = 25


xl_sheet_1.Cells(1,1) = " List"
xl_sheet_1.Cells(1,2) = Trim(Sess.Screen.GetString(5, 12, 13))

xl_sheet_1.Cells(2,1)= "Name"
xl_sheet_1.cells(2,2)= "Accountid"


RW=2

'last_row = xl_sheet_1.Range("B1").CurrentRegion.Rows.Count
Do
For i = 8 To 22
Name = Trim(Sess.Screen.GetString(i, 12, 10))
Accountid = Trim(Sess.Screen.GetString(i, 34, 19))

RW = RW + 1

xl_sheet_1.Cells(RW, "A").Value = Name
xl_sheet_1.Cells(RW, "B").Value = Accountid"


Next i

Sess.Screen.SendKeys ("<PF8>")
Call Wait(Sess)
IF xl_sheet_2.Cells(1,2) <> Trim(Sess.Screen.GetString(5, 12, 13)) THEN

xl_sheet_2.Cells(1,3) = Trim(Sess.Screen.GetString(5, 12, 13))
xl_sheet_2.Cells(k, "C").Value = Name
xl_sheet_2.Cells(k, "D").Value = Accountid
END IF

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

' Sess.Screen.SendKeys ("<PF8>")
exit sub
End if
Sess.Screen.SendKeys ("<PF8>")
Call Wait(Sess)
Loop
Call Wait(Sess)

Next ascreen

' Sess.Screen.SendKeys ("<enter>")
Call Wait(Sess)

End Sub

 

[SkipVought]

braven,

You do not express yourself very well, and consequently, it is very difficult to determine exactly what you are asking.

It seems that you have 10 screens with some sort of city-related data.

The City Name is always in row 5, 20-25. Six characters does not appear to be adequate for a City Name, and that seems rather odd to me.

Then in rows 8 thru 22 in 12-39, you have some other data, apparently related to the City Name on the screen.

Each screen goes in a separate Excel sheet, with the City Name in Row 1, and column set headings in row 2: Name & Accountid

So your logic is that when the City Name changes in a screen, you want to start a new column set.

Here are my suggestions. I am only showing you relevant code snippits that you can then incorporate yourself intor your code.

1. Do not add the sheets all at once. Rather, add the sheets when you start a new screen, and assign the screen name to the sheet name...

For iScreen = 0 to UBound(aScreens)
  Set xl_ws = xl_wb.Sheets.Add(After:= xl_wb.Sheets(xl_wb.Sheets.Count))
  xl_ws.Name = aScreens(iScreen)
'...
Next

2. Since you are add a column set for each City Name...

Dim sCity as string, sPrevCity as string, iCol as integer
iCol = 1
For iScreen = 0 to UBound(aScreens)
  Set xl_ws = xl_wb.Sheets.Add(After:= xl_wb.Sheets(xl_wb.Sheets.Count))
  xl_ws.Name = aScreens(iScreen)

'... to process changes in City Name
  sCity = Trim(Sess.Screen.GetString(5,12,13))
  If sPrevCity <> sCity Then
    iCol = iCol + 2
    RW = 3
  End if
  xl_ws.Cells(2, iCol).Value = "Name" 
  xl_ws.Cells(2, iCol+1).Value = "Accountid"

'...  within your loop to process rows 8-22
  xl_ws.Cells(RW, iCol).Value = Name  
  xl_ws.Cells(RW, iCol+1).Value = Accountid
'...
  sPrevCity = sCity
Next

Hope this helps.


Skip,

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

 

[braven]

thank you so much Skip
i am trying to incorporate my code but i am struggling with cannot access file it says read only
sFile = "'c:\xx.xls"
Set xl = CreateObject("Excel.Application")
Set xl_wb = xl.Workbooks.Add

xl_wb.SaveAs(sFile)
why i could not access excel file.

 

[SkipVought]

When you say, "access excel file," are you trying to OPEN a workbook? You might try using the Open method if so.

Skip,

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

 

[braven]

yes you are right, i did it's now open the workbook.
and another your code
the below line has error object set ot nothing
i declared as x1_ws as object
xl_ws.Cells(2, iCol).Value = "Name"
xl_ws.Cells(2, iCol+1).Value = "Accountid"

 

[SkipVought]

where do you Set the object?

Skip,

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

 

[braven]

before main() declard as x1_ws as object
inside the do loop

For iScreen = 0 to UBound(aScreens)
Set xl_ws = xl_wb.Sheets.Add(After:= xl_wb.Sheets(xl_wb.Sheets.Count))
xl_ws.Name = aScreens(iScreen

 

[SkipVought]

   xl_ws.Cells(2, iCol).Value = "Name"

what is the value of iCol in your debugger?

Skip,

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

 

[braven]

iCol= 3

 

[SkipVought]

Please post all your code up to that point.

Skip,

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

 

[braven]

this is my part of the code
For iScreen = 1 To UBound(aScreens)
' sScreen = aScreens(iScreen)
' Sess.Screen.SendKeys sScreen
' Sess.Screen.SENDKEYS("<Home><BackTab>sScreen<Enter>")

Sess.Screen.SENDKEYS("<Enter>")

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


Set xl_ws = xl_wb.Sheets.Add(After:= xl_wb.Sheets(xl_wb.Sheets.Count))
xl_ws.Name = Trim(Sess.Screen.GetString(24, 63, 4))


icol = 1
sCity = Trim(Sess.Screen.GetString(5,20,5))
If sPrevCity <> sCity Then
iCol = iCol + 2
RW = 3
End if
'last_row = xl_sheet_1.Range("B1").CurrentRegion.Rows.Count
Do
For i = 8 To 22
Name = Trim(Sess.Screen.GetString(i, 12, 10))
Accountid = Trim(Sess.Screen.GetString(i, 34, 19))


x1_ws.Cells(2, iCol).Value = Name
xl_ws.Cells(2, iCol+1).Value = Accountid



sPrevCity = sCity


Next i

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



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


exit sub



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

Call Wait(Sess)

 

[SkipVought]

you are TYPING a character ONE, rather than lower-case character L

What you have typed...

     x[b][red]1[/red][/b]_ws.Cells(2, iCol).Value = Name
     xl_ws.Cells(2, iCol + 1).Value = Accountid

Skip,

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

 

[braven]

full code here
Declare Sub Wait(Sess As Object)


Dim x1_wb as Object



Dim L as Integer, next_row1 As Integer, last_row1 As Integer
Dim x1_sheet_1 as Object, xl_sheet_2 as Object
Dim Name As String, Accountid As String
Dim aScreens(13) As String
Dim sScreen As String,
Dim iScreen As Integer

Dim xl As Object, xl_wb As Object, file_name As String
Dim x1_ws as Object
Dim i As Integer, j As Long, m as Long
Dim dict As Object
Dim Count As Variant
Dim mScreen as Integer
Dim bScreen as String
dim x as object
Dim Sys As Object, Sess As Object

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




file_name = "C:\Documents and Settings\test.xls"


sFile = file_name
Set xl = CreateObject("Excel.Application")

'Set xl_wb = xl.Workbooks.Open(file_name)
Set xl_wb = xl.Workbooks.Add
Set xl_ws = xl_wb.Sheets.Add(After:= xl_wb.Sheets(xl_wb.Sheets.Count))
xl_wb.SaveAs(sFile)
xl.Visible = True
xl.DisplayAlerts = False

For iScreen = 1 To UBound(aScreens)
' sScreen = aScreens(iScreen)
' Sess.Screen.SendKeys sScreen
' Sess.Screen.SENDKEYS("<Home><BackTab>sScreen<Enter>")

Sess.Screen.SENDKEYS("<Enter>")

Sess.Screen.SENDKEYS("<Home>GD<Tab>*<EraseEOF><Tab>abcd<Enter>")
Const xlFilterCopy As Integer = 2


Set xl_ws = xl_wb.Sheets.Add(After:= xl_wb.Sheets(xl_wb.Sheets.Count))
'xl_ws.Name = Trim(Sess.Screen.GetString(24, 63, 4))





icol = 1
sCity = Trim(Sess.Screen.GetString(5,20,5))
If sPrevCity <> sCity Then
iCol = iCol + 2
RW = 3
End if

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


x1_ws.Cells(2, iCol).Value = Name
' xl_ws.Cells(2, iCol+1).Value = Accountid



sPrevCity = sCity





Next i

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




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





' Sess.Screen.SendKeys ("<PF8>")
exit sub
End if


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





Next
End Sub



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