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

 

[streetprog]

Thanks, Mr. Wilson. I'll have to play around with this and see what I can come up with. I haven't used arrays too much in the past, but I have used them on occasion--nothing this advanced, though.

 

[IknowMe]

Thanks, Mr. Wilson.

Umm err ... there goes my alter ego, time to move the bat cave again.

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

 

[streetprog]

Ha!! I must have some sort of dyslexia. It took me a while to figure out that it's an M. I tried the test code you used for the array and it works fine. When I used the portion that was remarked out, I had a little trouble. But I'll keep at it until I can get what I want. Thanks.

 

[IknowMe]

Not a prob I am actually Mr. Wilson but that handle was taken so I thre in my first initial M. hence MrMilson.

Anyhow, Post a sample of how the data's appearing on your screen and I may be able to help more.

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

 

[streetprog]

I made a few changes because I could not get parts to work as I wanted them.

    Dim IDArray() as string
    Redim IDArray(152)   '152 is the total number of values possible

    With Sess0.Screen
        x = 1
        i = 0
        
        For Row = 6 to 20
        For Col = 2 to 43 step 41
            Select Case .GetString(Row, Col, 4)
                Case 6840 to 6915
                    IDArray(i) = .GetString(Row, Col + 10, 11)
                    i = i + 1
                    IDArray(i) = .GetString(Row, Col + 22, 11)
                    i = i + 1
            End Select
        Next : Next 
    End With
    
    'Sort

    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)
    
    y = 6840
    
    for x = 1 to uBound(IdArray) Step 2
        msgbox str(y) & "    " & IdArray(x) & "/" & IdArray(x+1)
        y = y + 1
    next x

This works pretty well except for a few parts. First, because I won't know exactly how many arrays to capture, I had to use the upper limit of 152. As a result, there usually a number of IdArray with a "" value and so they are sorted to the beginning. Instead of my IDs starting at the beginning, they finish at the end limit. Is there any way to remove null values?

 

[IknowMe]

try something like this to avoid putting blanks in your array

Select Case .GetString(Row, Col, 4)
Case 6840 to 6915
if .GetString(Row, Col + 10, 11) <> "" then
IDArray(i) = .GetString(Row, Col + 10, 11)
i = i + 1
IDArray(i) = .GetString(Row, Col + 22, 11)
i = i + 1
End if
End Select


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

 

[streetprog]

Is there a way to limit the array to the actual number of arrays you capture? The max number of values I would need to sort is 152, but the actual number will rarely approach that limit.

So if it stores 50 values, I am left with 102 empty array values. That was the other reason I was getting blank (or empty) values in my array.

Thanks again for all of your help!