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]

Afternoon,

I did a little digging and here is the solution i found.

first ensure this reference file is checked. Located under the Tools menu bar in the VBA Editor See attached Image1_SortExcelRows & Image2_ExcelSortList_References_ScreenShot.

This will enable you to access the object library from Excel.

You where on the right track, please see the code below and let me know what you think. if this solves your issue please like the post and mark as a solution.

As always please reach out with further questions or concerns.

Sub SortExcelRange()

'set ref to workbook

Dim oWorkbook As Workbook
Set oWorkbook = GetObject("YOUR FILE PATH HERE WITH FILE NAME AND EXTENSION")

'set ref to active sheet

Dim oActiveSheet As WorkSheet
Set oActiveSheet = oWorkbook.Sheets.Item(1)

'adjust range as needed, i used b2 as the column to sort

oActiveSheet.Range("a1:c12").Sort Key1:=oActiveSheet.Range("b2"), order1:=xlAscending

End Sub

[C_Haines_ENG]

Are you editing the Inventor VBA code in the VBA editor and not in the built in one? Would you be able to paste the "Imports" code so I can add it to my inventor? Doesnt work without that it seems. 

[AndrewHumiston]

yes i'm running it in the Inventor VBA, do you have the Microsoft Excel Reference file checked? you should not need to import anything.

[AndrewHumiston]

Ensure this box is checked, it might be at the top of your list.

it is found here:

[C_Haines_ENG]

Okay so I did find it, but it still doesnt work. "Method arguments must be enclosed in parentheses." and "'Range' is a type and cannot be used as an expression."

[AndrewHumiston]

is that error coming from the sort line?

[AndrewHumiston]

Here are the reference files i have selected:

[C_Haines_ENG]

The method argument one yes. 

[AndrewHumiston]

please ensure your references match mine. that might be an issue.

[C_Haines_ENG]

Do I have to run the script in the VBA Editor? Can these references not be imported into the default editor?

[AndrewHumiston]

It is setup to run in a VBA editor.

are  you using ilogic?

[C_Haines_ENG]

Yes. Is iLogic.

[C_Haines_ENG]

Is this problem not possible in iLogic? I was under the impression that iLogic and VBA were nearly identical minus some formatting. 

[AndrewHumiston]

i have it working in VBA, its a matter of getting access to the same properties in ilogic. i typically use VBA for this as its easier to format inside vba.

let me toy around with ilogic and see what i can come up with.

[AndrewHumiston]

i have it working in VBA, its a matter of getting access to the same properties in ilogic. i typically use VBA for this as its easier to format inside vba.

let

[C_Haines_ENG]

Any luck with converting it to iLogic? I fear lack of formatting options in iLogic wont allow you to specify the parameters for how the sort should be done. 

[AndrewHumiston]

that is what i am running into. we use a lot of excel sheets to drive our cad models and certain features i haven't figured out how to get access from ilogic.

[C_Haines_ENG]

I can get you started, this is why I was mentioning imports earlier.

Imports Microsoft.Office.Interop 'To use excel 
Imports Microsoft.Office.Interop.Excel 'To use excel
AddReference "Microsoft.Office.Interop.Excel" 'To use excel 
BOMPath = "C:\BOM Calculator\PART_BOM.xlsx" 'FINAL DESTINATION OF BOM RESULTS

'[ CREATE EXCEL APPLICATION, INSERT HEADER, AND CLOSE PREVIOUS INSTANCE IF STILL OPEN]

Try : ExcelAPP = GetObject(BOMPath) : ExcelAPP.Save : ExcelAPP.Application.Quit : My.Computer.FileSystem.DeleteFile(BOMPath) : Catch : End Try

ExcelAPP = CreateObject("Excel.Application") : ExcelAPP.Visible = True: ExcelAPP.DisplayAlerts = False

'AppActivate(ExcelAPP)
ExcelWB = ExcelAPP.Workbooks.Add
ExcelWB.SaveAs(BOMPath)

With ExcelAPP
	
	.Range("A1") = "TEST"
	
End With

ExcelAPP = Nothing
	

[AndrewHumiston]

let me tinker with this!