Problems with sorting data in Excel through Extra 1

[streetprog]

I'm writing a macro to export some data into Excel, have it perform a number of tasks and then pull it back into Extra. I know I can get it to work if I just use Excel VBA to interface with Extra, but it would be easier for the user to run from Extra rather than Excel.

The problem I have is with making VBA code work with Extra. I can get the simple tasks like [Extra.Range("D1").Value = "whatever"] just fine.

How do I get this VBA code to work in Extra?

Columns("D:D").Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortTextAsNumbers

 

[SkipVought]

Hi,

I run all my Attachmate from Excel VBA, but the principles are the same.

Use the CreateObject method to set an Excel Application object and the GetObject method to set a workbook object and open a specific workbook.

Then its a matter of explicitly referencing the range to sort, something like this...

'Excel objects assigned
xlApp.Wbk(1).Sheets(1).Columns("D:D").Sort _
   Key1:=xlApp.Wbk(1).Sheets(1).Range("D1"), Order1:=xlAscending, _
   Header:=xlGuess, _
   OrderCustom:=1, _
   MatchCase:=False, _
   Orientation:=xlTopToBottom, _
   DataOption1:=xlSortTextAsNumbers

Oh, yes, and set a reference to the Microsoft Excel n.m Object Library.

Skip,

_[red][/red]

 

[streetprog]

Ok, I tried that but I got an error "no such property or method" on the line with

xlApp.Wbk(1).Sheets(1).Columns("D:D").Sort _

Here's what I'm using...

Sub Main()
	Dim Sessions As Object
	Dim System As Object
	Set System = CreateObject("EXTRA.System")	
	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

	g_HostSettleTime = 200		' milliseconds

	OldSystemTimeout& = System.TimeoutValue
	If (g_HostSettleTime > OldSystemTimeout) Then
		System.TimeoutValue = g_HostSettleTime
	End If

	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
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	
    Dim xlApp as Object
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Add
    xlApp.Visible = True	

    With Sess0.Screen
        x = 1
        TA = ""
        TB = ""
        
        For Row = 6 to 20
        For Col = 2 to 43 step 41
        
        Select Case .GetString(Row, Col, 4)
            Case 6840 to 6915
                TA = .GetString(Row, Col + 10, 11)
                TB = .GetString(Row, Col + 22, 11)
                xlApp.Range("D" & x).Value = trim(TA)
                xlApp.Range("D" & x + 1).Value = trim(TB)
                
                x = x + 2
                
                TA = ""
                TB = ""
            
        End Select
        
        Next:Next
        
        xlApp.Wbk(1).Sheets(1).Columns("D:D").Sort _
            Key1:=xlApp.Wbk(1).Sheets(1).Range("D1"), Order1:=xlAscending, _
            Header:=xlGuess, _
            OrderCustom:=1, _
            MatchCase:=False, _
            Orientation:=xlTopToBottom, _
            DataOption1:=xlSortTextAsNumbers
    End With

End Sub

I'm not sure what you mean by "set a reference to the Microsoft Excel n.m Object Library." so that may have something to do with it.

 

[streetprog]

Oh, and I also replaced Wbk with Workbooks and then I got "Microsoft Excel: Sort method of Range class failed" for the same line.

 

[SkipVought]

...
xlApp.Workbooks(1).Sheets(1).Columns("D:D").Sort _
...

Skip,

_[red][/red]

 

[streetprog]

Skip, I've made the changes but I'm still getting the "Microsoft Excel: Sort method of Range class failed" when I run this.

 

[SkipVought]

Please post your sort code.

Skip,

_[red][/red]

 

[streetprog]

I'm just using what you posted. Sorry this is such a pain. I really appreciate your help!

xlApp.Workbooks(1).Sheets(1).Columns("D:D").Sort _
            Key1:=xlApp.Workbooks(1).Sheets(1).Range("D1"), Order1:=xlAscending, _
            Header:=xlGuess, _
            OrderCustom:=1, _
            MatchCase:=False, _
            Orientation:=xlTopToBottom, _
            DataOption1:=xlSortTextAsNumbers

 

[SkipVought]

Is your range indeed in workbook 1 and sheet 1, meaning the first workbook in the workbooks collection and the first sheet in the sheets collection?

Skip,

_[red][/red]

 

[streetprog]

Well, I'm adding a new workbook rather than opening an existing one. So by default, it's using Sheet1 of whatever Book(#) that is opens. I tried removing the .Workbooks(1).Sheets(1) reference and just using xlApp.Columns("D").Sort... but I got the same error. I'm using Extra v6.5 Service Pack 2.

 

[SkipVought]

1. did you set a reference to the Excel object library?

2. if you did then can you use the watch window, if there is that facility in Attachmate code editor, or debug.print xlApp.Workbooks(1).Sheets(1).Range("D1"). Is an error indicated here?



Skip,

_[red][/red]

 

[streetprog]

No, I don't know how. If it has something to do with the Functions and Objects Browser, when I click on the Objects tab it crashes my macro editor. The watch facility is available for this editor.

Also, I don't know how to use the debug.print procedure. Thanks.

 

[SkipVought]

Stick a MsgBox statement in there to inspect the value.

Skip,

_[red][/red]

 

[IknowMe]

Not that this is of any help, but I've been trying to find a solution to your issue and get the same error.

I think the problem is that EB doesn't like
xlWorkSheet.Columns("D").Sort Key1:= Range("D1")
because it sees Range as an undefined object.

and Excel doesn't like
xlWorkSheet.Columns("D").Sort Key1:= xlWorkSheet.Range("D1")
because it has no idea what xlWorkSheet is.

I even tried an "Range("& chr(34) & "D1" & chr(34) & ")"
But it still throws the failed range class error from Excel.

In my version of EB there is no way to reference the Excel Object Library as far as I know.

Since you seem to be dealing with your data in Excel have you considered switching your scrape to VBA and running the whole thing there?

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[IknowMe]

I could help more if you explain what else your doing with the info besides the sort. e.x. store your info in an array and run it through a sort function (all in EB).

I'm writing a macro to export some data into Excel, have it perform a number of tasks and then pull it back into Extra.

What does the info look like that your scraping?

Numeric
Alpha
Both ????

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[streetprog]

Yeah, I've decided to go that route. I am pushing the sorted data back into Extra, but the only way I've gotten the sort to work is running from VBA. It will require the user to have the Excel file containing the macro open, but it won't be to big of a hassle.

Skip, thanks for help.

 

[streetprog]

I finished what I was aiming to do, but if you have some pointers I can use such as the sort function, I'm more than happy to learn. Here is a subset of the data:

409-893-518/409-833-779
229-515-739/409-898-806
227-704-483/409-828-654
409-904-380/409-910-965

Each ID follows this format 000-000-000 and each line is composed of two IDs separated by a slash. So I scraped each 11-digit ID into one column, sorted and removed duplicates. The next part is complicated to explain but I move the numbers into two columns in order from left to right.

So I end up with this instead:

227-704-483/229-515-739
409-828-654/409-833-779
409-893-518/409-898-806
409-904-380/409-910-965

Each new line will need a prefix number from 6840 and up like this:

6840    227-704-483/229-515-739
6841    409-828-654/409-833-779
6842    409-893-518/409-898-806
6843    409-904-380/409-910-965

I then delete the existing lines and add the sorted ones. It's complicated, I know, but imagine doing this by hand with up to 150 ID's. I can post my VBA code if you want.

 

[SkipVought]

What result?

MsgBox xlApp.Workbooks(1).Sheets(1).Range("D1")

Skip,

_[red][/red]

 

[streetprog]

That msgbox just gives me the 11-digit ID that my macro scraped from the Extra Session that is located in cell D1. I also used "msgbox Error" to display the error was receiving for the sort and it just says "object error".

It seems that when Extra does simple things like xlApp.Range("D" & x).Value = "something" etc, it works fine. But when you start adding code after the first part (like in our sort code), it crashes. It may just be a limitation in Extra and there is no work-around.

 

[IknowMe]

Example with a little bubble sort. There are other sorts but this is the simplest to learn. Google Sort Algorythm VBA and you'll find lots of explanations between the different types.

Sub Main

'    Dim ExtraSys As Object, ExtraSess As Object, ExtraScreen As Object

'    Set ExtraSys = CreateObject("ACCMGR.System")
'    Set ExtraSess = ExtraSys.ActiveSession
'    Set ExtraScreen = ExtraSess.Screen

'    If ExtraScreen is Nothing Then
'        Msgbox "Could not create the Screen object.  Stopping macro playback."
'        STOP
'    End If
    
'    Dim IdArray() as String
'    Redim IdArray(0)
    
'    With ExtraScreen
'        For Row = 6 to 20
'            For Col = 2 to 43 step 41
'                Select Case .GetString(Row, Col, 4)
'                    Case 6840 to 6915
'                        Redim Preserve IdArray(Ubound(IdArray))
'                        IdArray(Ubound(IdArray)) = Trim(.GetString(Row, Col + 10, 11))
'                        Redim Preserve IdArray(Ubound(IdArray))
'                        IdArray(Ubound(IdArray)) = Trim(.GetString(Row, Col + 22, 11))
'                End Select
'            Next Col
'        Next Row
'    End With

    'For testing
    Redim IdArray(8)
    IdArray(0) = "409-893-518"
    IdArray(1) = "409-833-779"
    IdArray(2) = "229-515-739"
    IdArray(3) = "409-898-806"
    IdArray(4) = "227-704-483"
    IdArray(5) = "409-828-654"                    
    IdArray(6) = "409-904-380"
    IdArray(7) = "409-910-965"
    
    
    'Sort IdArray BubbleSort
    For x = UBound(IdArray) - 1 To 0 Step -1
        for y = 0 to x
            if IdArray(y)>IdArray(y+1) then
                temp=IdArray(y+1)
                IdArray(y+1)=IdArray(y)
                IdArray(y)=temp
            end if
        next
    next

    'Remove Duplicates

    Dupes = uBound(IdArray)
    For x = 0 to uBound(IdArray) - 1
        If IdArray(x) = IdArray(x+1) and IdArray(x) <> "" then 'found dupe
            For y = x to uBound(IdArray) - 1
                IdArray(y) = IdArray(y+1)
            Next
            Dupes = Dupes - 1 
        End If
    Next
    Redim Preserve IdArray(Dupes)
    
    for x = 1 to uBound(IdArray) Step 2
        msgbox str(x + 6839) & "    " & IdArray(x) & "/" & IdArray(x+1)
    next
    
End Sub

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.