syntax error 1

[ram567]

could you help me what is the syntax error
obj.Worksheets("Sheet1"("B2:B109").AdvancedFilter Action:=xlFilterCopy,CopyToRange:=("obj.Worksheets("Sheet1") G1").Unique:=True

 

[WinblowsME]

   obj.Worksheets("Sheet1").Range("B2:B109").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=obj.Worksheets("Sheet1").Range("G1"), Unique:=True

 

[ram567]

Thanks Winblow
but i run in attachmate extra! this code
i have error
advancedfilter methord range filed in microsoft excel

 

[WinblowsME]

   Const xlFilterCopy As Integer = 2

   obj.Worksheets("Sheet1").Range("B2:B109").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=obj.Worksheets("Sheet1").Range("G1"), Unique:=True

 

[ram567]

TAHNKS FOR YOU RESPONSE
THE BELOW CODE SHOWS HERE IF I PUT THE ABOVE CODE HERE IT DOES NOT COPY INTO EXCEL. I NEED THIS CODE BECASUE IT COPY IN ABCD AND B GET UNIQUE NUMBER INTO G1
COULD YOU HELP ME
Dim ExtraScreen As Object
Const xlFilterCopy As Integer = 2

obj.Worksheets("Sheet1").Cells(1,1).Font.Size = 12
obj.Worksheets("Sheet1").Cells(1,1)= "ACCOUNTS"
obj.Worksheets("Sheet1").Cells(1,2)= "PARTNUMBER"
obj.Worksheets("Sheet1").Cells(1,3)= "PARTNAME"
obj.Worksheets("Sheet1").Cells(1,4)= "PARTKEY"


'Set up Column Widths
obj.Worksheets("Sheet1").Columns("A").ColumnWidth = 13
obj.Worksheets("Sheet1").Columns("B").ColumnWidth = 16
obj.Worksheets("Sheet1").Columns("C").ColumnWidth = 27
obj.Worksheets("Sheet1").Columns("D").ColumnWidth = 17



RW = 3
Do
'Sess0.Screen.GetString(3,20, 25))

For x = 6 To 23
ACCT = Trim(Sess0.Screen.GetString(x, 11, 5))
PARTNO = Trim(Sess0.Screen.GetString(x, 22, 10))
NAME = Trim(Sess0.Screen.GetString(x, 35, 20))
PARTKEY = Trim(Sess0.Screen.GetString(x, 59, 10))
if PARTNUMHER <> "XXXXXXXX" then
RW = RW + 1
With obj.Worksheets("Sheet1")
.Cells(Rw, "A").Value = ACCT
.Cells(Rw, "B").Value = PARTNO
.Cells(Rw, "C").Value = NAME
.Cells(Rw, "D").Value = PARTKEY
obj.Worksheets("Sheet1").Cells.Sort Key1:=obj.Worksheets("Sheet1").Columns("D"), Header:= 1
obj.Worksheets("Sheet1").Range("B1").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=obj.Worksheets("Sheet1").Range("G1"), Unique:=True

wb.Save

End with
Else

Exit For
End if
Next

Sess0.Screen.SendKeys ("<PF8>") 'next screen

 

[WinblowsME]

How is obj declared? What is the Do for? You might want to post the entire code from top to bottom.

 

[ram567]

the whole code here:

Sub Main
' Get the main system object
Dim Sessions As Object
Dim System As Object
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 Sessions collection object.Stopping macro playback."
STOP
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
'--------------------------------------------------------------------------
sFile = "C:\testRAM567.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("test")
'---------------------------------------------------------------------------
'THIS copies the data from the VT session row by row into
'Excel sheet beginning in Cell A2-C2 and downward
RW = 3
Do
'Sess0.Screen.GetString(3,20, 25))

For x = 6 To 23
ACCT = Trim(Sess0.Screen.GetString(x, 11, 5))
PARTNO = Trim(Sess0.Screen.GetString(x, 22, 10))
NAME = Trim(Sess0.Screen.GetString(x, 35, 20))
PARTKEY = Trim(Sess0.Screen.GetString(x, 59, 10))
if PARTNUMHER <> "XXXXXXXX" then
RW = RW + 1
With obj.Worksheets("Sheet1")
.Cells(Rw, "A").Value = ACCT
.Cells(Rw, "B").Value = PARTNO
.Cells(Rw, "C").Value = NAME
.Cells(Rw, "D").Value = PARTKEY
obj.Worksheets("Sheet1").Cells.Sort Key1:=obj.Worksheets("Sheet1").Columns("D"), Header:= 1
obj.Worksheets("Sheet1").Range("B1").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=obj.Worksheets("Sheet1").Range("G1"), Unique:=True

wb.Save

End with
Else

Exit For
End if
Next

Sess0.Screen.SendKeys ("<PF8>") 'next screen

 

[WinblowsME]

Make sure you edit the red line to suit your needs.

' 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

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

   Const xlFilterCopy As Integer = 2

   Dim xl As Object, xl_wb As Object, xl_sheet As Object, file_name As String
   Dim account As String, part_no As String, part_name As String, part_key As String
   Dim i As Integer, j As Long

   file_name    = "C:\testRAM567.xls"

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

   xl.Visible       = True
   xl.DisplayAlerts = False

   xl_sheet.Range("A:A").EntireRow.Font.Size = 12
   xl_sheet.Columns("A").ColumnWidth = 13
   xl_sheet.Columns("B").ColumnWidth = 16
   xl_sheet.Columns("C").ColumnWidth = 27
   xl_sheet.Columns("D").ColumnWidth = 17

   xl_sheet.Cells(1, 1) = "ACCOUNTS"
   xl_sheet.Cells(1, 2) = "PARTNUMBER"
   xl_sheet.Cells(1, 3) = "PARTNAME"
   xl_sheet.Cells(1, 4) = "PARTKEY"

   j = 2

   Do
      For i = 6 To 23
         account   = Trim(Sess.Screen.GetString(i, 11, 5))
         part_no   = Trim(Sess.Screen.GetString(i, 22, 10))
         part_name = Trim(Sess.Screen.GetString(i, 35, 20))
         part_key  = Trim(Sess.Screen.GetString(i, 59, 10))

         If part_no <> "XXXXXXXX" Then
            xl_sheet.Cells(j, "A").Value = account
            xl_sheet.Cells(j, "B").Value = part_no
            xl_sheet.Cells(j, "C").Value = part_name
            xl_sheet.Cells(j, "D").Value = part_key
            j = j + 1
         Else
            Exit For
         End If
      Next

      Sess.Screen.SendKeys ("<PF8>")
      Call Wait(Sess)
   [COLOR=red]Loop While Ucase(Sess.Screen.GetString(24, 1, 9)) <> "LAST PAGE"[/color]

   xl_sheet.Cells.Sort Key1:=xl_sheet.Range("D1"), Header:=1
   xl_sheet.Range("B2:B109").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=xl_sheet.Range("G1"), Unique:=True

   xl_wb.Save
   xl_wb.Close
   xl.Quit

   Set xl_sheet = 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

 

[ram567]

Winblow
thank you very much first of all.
btu the
below line is not working it is not showing any error but it is not copied in COLUMN G1
xl_sheet.Range("B2:B109").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=xl_sheet.Range("G1"), Unique:=True
and another one this above code it copied only one page and macro done.
what i provided that code it copied the whole thing page wise, both the codes advanced filter is not working
could you see that.
thanks in advance

 

[ram567]

wINBLOW
IT WORKES WHAT I WANT IT. BUT IT WORKES ONLY ONE PAGE, I AM WONDERING THOUGH IT IS SEND KEYS PF8 IN THE ABOVE CODE, WHY IT DOES NOT GO NEXT PAGE, BEFORE THAT THE MACRO DONE.
COULD YOU CHECK THAT PLEASE. THANKS A LOT ONCE AGAIN

 

[ram567]

Winblow
thank you so much. it workes perfectly 100 % thanks a lot i learnt from you guyes.
what i did the above your ocde
i put Loop Until Ucase(Sess.Screen.GetString(24, 1, 9)) <> "LAST PAGE"
and then the below codes i put it after j = j +1

xl_sheet.Cells.Sort Key1:=xl_sheet.Range("D1"), Header:=1
xl_sheet.Range("B2:B109").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=xl_sheet.Range("G1"), Unique:=True

 

[ram567]

winblow
thanks for your help but the below code i have problem
part no <> "xxxxxx" then
if i put it what happens that page after xxxxxx not write into excel
just now i noticed.
could you help me
Do
For i = 6 To 23
account = Trim(Sess.Screen.GetString(i, 11, 5))
part_no = Trim(Sess.Screen.GetString(i, 22, 10))
part_name = Trim(Sess.Screen.GetString(i, 35, 20))
part_key = Trim(Sess.Screen.GetString(i, 59, 10))

If part_no <> "XXXXXXXX" Then
xl_sheet.Cells(j, "A").Value = account
xl_sheet.Cells(j, "B").Value = part_no
xl_sheet.Cells(j, "C").Value = part_name
xl_sheet.Cells(j, "D").Value = part_key
j = j + 1
Else
Exit For
End If
Next

Sess.Screen.SendKeys ("<PF8>")
Call Wait(Sess)
Loop Until Ucase(Sess.Screen.GetString(24, 1, 9)) <> "LAST PAGE"