Sorting excel range through iLogic

[Anonymous]

Hello everyone,

How can I sort a range in Excel through iLogic?

When I try the following code:

Sub Main()
	Dim oDoc As AssemblyDocument = ThisApplication.ActiveDocument
	Dim oBOM As BOM = oDoc.ComponentDefinition.BOM
	Dim objExcel = CreateObject("Excel.Application")
	ThisBOM.Export("Parts Only", "C:\Users\ben.wouters\Desktop\TEST.xlsx", kMicrosoftExcelFormat)
	Dim objWorkbook = objExcel.Workbooks.Open("C:\Users\ben.wouters\Desktop\TEST.xlsx")
	excelSheet = objWorkbook.Worksheets(1)
	objExcel.Columns.AutoFit
	excelSheet.Range("A1:I16").Sort(Key1:=excelSheet.Range("C1"), Order1:=xlAscending, Header:=xlYes)
	objExcel.Application.Visible = True
End Sub

I get an error message saying "Method Sort of class Range has failed".

However when I run the rule with the sort line commented out, it runs just fine. It just doesn't get sorted.

I'v tried a bunch of stuff, but either it throws up the error message above or I get the following: "Exception from HRESULT: 0x800A03EC".

Any help would be greatly appreciated.

[bradeneuropeArthur]

Sub Main()
	Dim oDoc As AssemblyDocument = ThisApplication.ActiveDocument
	Dim oBOM As BOM = oDoc.ComponentDefinition.BOM
	Dim objExcel = CreateObject("Excel.Application")
	ThisBOM.Export("Parts Only", "C:\Users\ben.wouters\Desktop\TEST.xlsx", kMicrosoftExcelFormat)
	Dim objWorkbook = objExcel.Workbooks.Open("C:\Users\ben.wouters\Desktop\TEST.xlsx")
	excelSheet = objWorkbook.Worksheets(1)
	objExcel.Columns.AutoFit
	excelSheet.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:I16"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
	objExcel.Application.Visible = True
End Sub

[Anonymous]

Hey Arthur,

Thanks for your reply. Your code doesn't work for me.

Let me start by mentioning that I'm working with Inventor Professional 2016.

When I copy and paste your code, it doesn't run saying that Methodarguments must be between brackets and that Range isn't declared.

So I tried the following:

	excelSheet.Worksheets("Sheet1").Sort.SortFields.Add (Key:=excelSheet.Range("A1:I16"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal)

But that gives me the error that public member Worksheets for type Worksheet wasn't found.

Replacing the word excelSheet with objWorkbook gives me the error: Exception from HRESULT: 0x800A03EC.

[bradeneuropeArthur]

Try this:

excelSheet.Worksheets("Sheet1").Sort.SortFields.Add (Key:=excelSheet.Range("A1:A16"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal)

[Anonymous]

Hey Arthur,

I get the public member Worksheets for type Worksheet wasn't found error again.

[bradeneuropeArthur]

maybe this will help you:

https://stackoverflow.com/questions/21451458/vba-excel-sort-range-by-specific-column

[Anonymous]

Hey Arthur,

Thanks for the help. I managed to do what I wanted to do by using the FindRow snippet in Inventors iLogic editor.

[Peterjan.Schakel]

Hello Wouteb,

I am currently traying the same thing, Sorting data of excel trough iLogic. I have tried every code described above, with no positive outcome (no error, just nothing happens). Is it possible that you share the code you used?

greets,

Peterjan Schakel