Sort Excel Rows in Alphabetical Order

[C_Haines_ENG]

Hello all!

I cant seem to get this one to work even with the hyper lengthly documentation online. It seems it may not be possible to do this as the sorting method requires a rather random formatting to get the items to sort.

Range("A1:A12").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo

You can see beside the the Range.Sort there is a space, and the a key for what row to sort items by. I dont believe Inventors version of VBA supports this. Is there a way to get around this and has anyone successfully done this? Even previous posts of this topic were never anwsered. 

Thanks!

[AndrewHumiston]

@Curtis_Waguespack maybe you can assist here?

i have gotten the code work in VBA , but i can't get the sort property to work in ilogic. i've tried about all the combinations i can think of. i know some functions are not available in vb.net, or use a different syntax. Any help would be appreciated or a simple its not possible would also make my head stop spinning. thank you in advance

here is the VBA code:

Sub SortExcelRange()

'set ref to workbook
Dim oWorkbook As Workbook
Set oWorkbook = GetObject("C:\Work\Designs\R and D\8551-8599\8585\AutodeskBlog\SortExcelRow.xlsx")

'set ref to active sheet
Dim oActiveSheet As WorkSheet
Set oActiveSheet = oWorkbook.Sheets.Item(1)

'sort the rows. column b is primaary column in this example.
Call oActiveSheet.Range("a2:c12").Sort(oActiveSheet.Range("b2"), xlAscending)

End Sub

here is the ilogic (vb.net), i can't get the sort fields to take the inputs. 

Imports Microsoft.Office.Interop 'To use excel
Imports ExcelApp = Microsoft.Office.Interop.Excel 'To use excel
AddReference "Microsoft.Office.Interop.Excel" 'To use excel
AddReference "microsoft.office.interop.excel.dll"
'set ref to workbook
Dim oExcelApp As ExcelApp.Application = CreateObject("Excel.application")

Dim oExcelWorkbook As Excelapp.Workbook = oExcelApp.Workbooks.Open("C:\Work\Designs\R and D\8551-8599\8585\AutodeskBlog\SortExcelRow.xlsx")

'set ref to active sheet
Dim oActiveSheet As Excelapp.Worksheet = oExcelWorkbook.Sheets("Sheet1")

'sort the rows. column b is primaary column in this example.
Dim oRangeSort As Excel.Range = oActiveSheet.Range("A2:C12")
Dim oRangeSortColumn As Excel.Range = oActiveSheet.Range("b2:b12")
oExcelApp.ActiveWorkbook.Worksheets("Sheet1").Sort.sortfields.add2(oRangeSortColumn, xlSortOnValues, xlDescending, , xlSortNormal, )

[J_Pfeifer_]

Skimmed through and I typically program inside ilogic but... Today I needed to create a macro inside of VBA. Remember, you can always call to a macro from an ilogic rule. So if you have a bit of code that functions within VBA, use InventorVB.RunMacro("Documentname", "Modulename", "macroname"). 

Referencing parameters inside VBA reference:

https://forums.autodesk.com/t5/inventor-programming-ilogic/parameter-in-vba/td-p/7940657

[AndrewHumiston]

That's usually where I start, I am more used to the de-bugging tools, i just was looking for a second opinion to help this person out.

the one issue I ran into was deploying a VBA project among a few users can be tricky but not impossible.

thanks for your input!

[C_Haines_ENG]

So It cant be done in iLogic?

[AndrewHumiston]

I'm not saying it can't be done, i don't believe anything is impossible. Right now i don't know how to do it in ilogic. 

[C_Haines_ENG · Accepted solution]

I FINIALLY GOT IT WORKING! After MONTHS and about 40 more attempts, I found one backend website that had about HALF of the solution haha.

Imports Microsoft.Office.Interop 'To use excel  
Imports Microsoft.Office.Interop.Excel 'To use excel 
AddReference "Microsoft.Office.Interop.Excel" 'To use excel 

ExcelApp = CreateObject("Excel.Application")
ExcelWB = ExcelApp.Workbooks.Add

Dim DataRange as String = "A5:D9" 'Range of all data you want sorted
Dim KeySearch As String = "B5" 'Range of the key item you want to sort by.

ExcelApp.Range(DataRange).Sort(ExcelApp.Range(KeySearch), Excel.XlSortOrder.xlAscending, , , , , , Excel.XlYesNoGuess.xlNo)

For those coming across this you can follow microsofts vba database for all the other options. 

[AndrewHumiston]

Heck yeah! nice work!