VBA Excel Sorting

vidooq

New Member
Hi Guys,


I created a below code, and could not find what is wrong there - check code does not show anything.

Exception details after calculation stage:
bp sort.png.


Code:

dim wb as excel.workbook
dim ws as excel.worksheet
'Dim wb as Object = GetWorkbook(handle,workbookname)
'Dim ws as Excel.Worksheet

Dim Xlapp as Object=getobject(,"Excel.Application")

for each wb in Xlapp.workbooks

if wb.name = [workbookname] then
wb.activate
exit for
end if
next
ws = wb.worksheets(worksheetname)
ws.activate

ws.Range("A3:K"&[Number_of_Rows]-1).Sort.SortFields.Clear
ws.Range("A3:K"&[Number_of_Rows]-1).Sort.SortFields.Add (Key:= ws.range("E3:E"&[Number_of_Rows]), SortOn:=1, Order:=2, DataOption:=1)
With ws.Range("A3:K"&[Number_of_Rows]-1) .Sort
.SetRange (ws.Range("A3:K"&[Number_of_Rows]-1))
.Header = 0
.MatchCase = False
.Orientation = 1
.SortMethod = 1
.Apply
End With

handle, Number_of_Rows, workbookname, and worksheetname are Inputs for the code above.


Many Thanks,

Jarek
 

cs.andras

Active Member
Hi Jarek, @vidooq
Have you copied your code over to Excel VBA and tested it there? I've replaced the variables with some standard numbers...etc. and the code came back with some red lines you should look into.
+1: I'm not sure if you should use variables with [] within a Code block. I think they are just normal VB variables w/o the brackets.
+2: Don't trust the check code. It's cr*p really... use your own wits, or another IDE to check your code and try to copy a working code over into BP.
 

vidooq

New Member
Hi cs.andras,


Code was modified via enumeration standards - in ex. https://msdn.microsoft.com/en-us/library/bb216447(v=office.12).aspx

I've already tried removing [ ] and no results - I know that it shoudl be a raw VBA & few developer advised that already but no one could resolve that end - to - end.

Can you advise any IDE to use for checks & which lines contained errors when you looked at that in Excel?


Many Thanks for reply,

Jarek
 

cs.andras

Active Member
@vidooq I've used simple Excel VBA as the IDE, the lines were:
Dim Xlapp as Object=getobject(,"Excel.Application")
and
ws.Range("A3:K"&[Number_of_Rows]-1).Sort.SortFields.Add (Key:= ws.range("E3:E"&[Number_of_Rows]), SortOn:=1, Order:=2, DataOption:=1)
So obviously there is something up with the code as it isn't even accepted within Excel itself - as I understand.
 

vidooq

New Member
@cs.andras Dim Xlapp as Object=getobject(,"Excel.Application") should be fine

it works for code below: - inputs are the same

dim wb as excel.workbook
dim ws as excel.worksheet
'Dim wb as Object = GetWorkbook(handle,workbookname)
'Dim ws as Excel.Worksheet

Dim Xlapp as Object=getobject(,"Excel.Application")

for each wb in Xlapp.workbooks

if wb.name = [workbookname] then
wb.activate
exit for
end if
next
ws = wb.worksheets(worksheetname)
ws.activate

ws.columns("A:A").ColumnWidth=20
ws.columns("B:B").ColumnWidth=60
ws.columns("C:D").ColumnWidth=10
ws.columns("E:K").ColumnWidth=15

error in
ws.Range("A3:K"&[Number_of_Rows]-1).Sort.SortFields.Add (Key:= ws.range("E3:E"&[Number_of_Rows]), SortOn:=1, Order:=2, DataOption:=1) shows roughly the same as blue prism does.

I've modified that part for parameters and still does not work: :(


ws.Range("A3:K"&[Number_of_Rows]-1).Sort.SortFields.Clear
ws.Range("A3:K"&[Number_of_Rows]-1).Sort.SortFields.Add (Key:= ws.Range("E3:E"&[Number_of_Rows]-1), SortOn:=0, Order:=2, DataOption:=0)
With ws.Range("A3:K"&[Number_of_Rows]-1) .Sort
.SetRange (ws.Range("A3:K"&[Number_of_Rows]-1))
.Header = 0
.MatchCase = False
.Orientation = 1
.SortMethod = 1
.Apply
End With
 

Meghagg

New Member
Hi Andras and Vidooq,

Did the sorting code worked for you, I have the same requirement of sorting the excel column and tried a lot but still facing the errors.
If you guys are having the working code, Could you please share it here.

Thanks.
 

VJR

Well-Known Member
Hi Meghagg,

Here is a working code of sorting data in excel using a customised action in the MS Excel VBO.

Code:
Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, varUsedRange As Object


Try

sw = GetWorkbook(Handle, Source_Workbook)
ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)


sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet

'You can use your own range if needed
varUsedRange = sheet.UsedRange().address

sheet.Sort.SortFields.Clear
sheet.Sort.SortFields.Add (Key:=sheet.Columns("A"),SortOn:=0, Order:=1, DataOption:=0)

'Parameter Values
'Key:= assuming column A is supposed to be the key column. can change as per requirement
'SortOn:=0 for xlSortOnValues
'Order:=1 for xlAscending
'DataOption:=0 for xlSortNormal



With sheet.Sort
    .SetRange (sheet.range(varUsedRange))    'can use Range("A1:B100") as per requirement

    If Header = False then    'Header is a Flag type data item passed as parameter to this Object
        .Header = 2    '2 for xlNo     
    else
        .Header = 1    '1 for xlYes        'Default to True
    end if

    .MatchCase = False
    .Orientation = 1   '1 for xlTopToBottom
    .SortMethod = 1    '1 forxlPinYin
    .Apply
End With


Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
    sw = Nothing
    ss = Nothing
    dw = Nothing
    ds = Nothing
    excel = Nothing
    sheet = Nothing
    varUsedRange = Nothing 
End Try

1.jpg: is how the action of the VBO looks like.

Inputs before running the new Sort action:
1523411507272.png

Output after running the new Sort action:
1523411568330.png

You can customise the code and the parameters as per your needs.
Let me know if you need any further assistance or are facing any issues.
 

Attachments

  • 1. Object diagram.JPG
    1. Object diagram.JPG
    24.3 KB · Views: 90

Meghagg

New Member
Hi,

Thanks for replying.
I tried the below code, updating the parameter according to my requirement (Not facing any error after doing Check code),
but still facing the 'Exception from HRESULT: 0x800A03EC' while running it.

Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, varUsedRange As Object

Try

sw = GetWorkbook(Handle, Workbook)
ss = GetWorksheet(Handle, Workbook, Worksheet)

sw.Activate()
ss.Activate()

excel = ss.Application
sheet = excel.ActiveSheet

'varUsedRange = sheet.UsedRange().address


sheet.Sort.SortFields.Clear
sheet.Sort.SortFields.Add (Key:=sheet.Columns("O2"),SortOn:=0, Order:=2, DataOption:=0)

With sheet.Sort

'.SetRange (sheet.range(varUsedRange))

.Header = 1
.MatchCase = False
.Orientation = 1
.SortMethod = 1
.Apply

End with

Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
sw = Nothing
ss = Nothing
dw = Nothing
ds = Nothing
excel = Nothing
' varUsedRange = Nothing

End Try

Please check and help.

Thanks once again.
 

VJR

Well-Known Member
Hi Meghagg,

Can you please highlight in bold or in some color what all parameters you changed after the code I submitted.
 

VJR

Well-Known Member
Hi Meghagg,

I can see one thing that the below line of code is commented.
Give a range where you have the data in or use UsedRange like I have used.
Refer my comments shown next to it.

.SetRange (sheet.range(varUsedRange)) 'can use Range("A1:B100") as per requirement
 

Meghagg

New Member
Hi VJ,

Thanks a lot:)

It is working now without Range as well. It just I was passing sheet.Columns("O2") instead of sheet.Columns("O").
 
  • Like
Reactions: VJR

VJR

Well-Known Member
okay....good to know its working now. As a side note, setting the range is a good programming practice while sorting the data.
 

wpc18

New Member
Hi Getting an Error
Failed to copy worksheet: Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
 

sameerasnd

New Member
Hi Meghagg,

Here is a working code of sorting data in excel using a customised action in the MS Excel VBO.

Code:
Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, varUsedRange As Object


Try

sw = GetWorkbook(Handle, Source_Workbook)
ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)


sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet

'You can use your own range if needed
varUsedRange = sheet.UsedRange().address

sheet.Sort.SortFields.Clear
sheet.Sort.SortFields.Add (Key:=sheet.Columns("A"),SortOn:=0, Order:=1, DataOption:=0)

'Parameter Values
'Key:= assuming column A is supposed to be the key column. can change as per requirement
'SortOn:=0 for xlSortOnValues
'Order:=1 for xlAscending
'DataOption:=0 for xlSortNormal



With sheet.Sort
    .SetRange (sheet.range(varUsedRange))    'can use Range("A1:B100") as per requirement

    If Header = False then    'Header is a Flag type data item passed as parameter to this Object
        .Header = 2    '2 for xlNo    
    else
        .Header = 1    '1 for xlYes        'Default to True
    end if

    .MatchCase = False
    .Orientation = 1   '1 for xlTopToBottom
    .SortMethod = 1    '1 forxlPinYin
    .Apply
End With


Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
    sw = Nothing
    ss = Nothing
    dw = Nothing
    ds = Nothing
    excel = Nothing
    sheet = Nothing
    varUsedRange = Nothing
End Try

1.jpg: is how the action of the VBO looks like.

Inputs before running the new Sort action:
View attachment 544

Output after running the new Sort action:
View attachment 545

You can customise the code and the parameters as per your needs.
Let me know if you need any further assistance or are facing any issues.
Hi Meghagg,

Here is a working code of sorting data in excel using a customised action in the MS Excel VBO.

Code:
Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, varUsedRange As Object


Try

sw = GetWorkbook(Handle, Source_Workbook)
ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)


sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet

'You can use your own range if needed
varUsedRange = sheet.UsedRange().address

sheet.Sort.SortFields.Clear
sheet.Sort.SortFields.Add (Key:=sheet.Columns("A"),SortOn:=0, Order:=1, DataOption:=0)

'Parameter Values
'Key:= assuming column A is supposed to be the key column. can change as per requirement
'SortOn:=0 for xlSortOnValues
'Order:=1 for xlAscending
'DataOption:=0 for xlSortNormal



With sheet.Sort
    .SetRange (sheet.range(varUsedRange))    'can use Range("A1:B100") as per requirement

    If Header = False then    'Header is a Flag type data item passed as parameter to this Object
        .Header = 2    '2 for xlNo    
    else
        .Header = 1    '1 for xlYes        'Default to True
    end if

    .MatchCase = False
    .Orientation = 1   '1 for xlTopToBottom
    .SortMethod = 1    '1 forxlPinYin
    .Apply
End With


Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
    sw = Nothing
    ss = Nothing
    dw = Nothing
    ds = Nothing
    excel = Nothing
    sheet = Nothing
    varUsedRange = Nothing
End Try

1.jpg: is how the action of the VBO looks like.

Inputs before running the new Sort action:
View attachment 544

Output after running the new Sort action:
View attachment 545

You can customise the code and the parameters as per your needs.
Let me know if you need any further assistance or are facing any issues.

Thanks for the Help. It is really use full .
 
Top