ilogic for excel spreadsheet column sorting
Hi @D.Wheeler3GADA. I may be able to help you out there. I created something for you, but have not tested much yet, but it worked OK on a small sample. I just threw some of my custom functions in there, because I find them helpful, but you could probably get rid of most, if not all of them, to simplify it later. The Sub Main area is fairly straight to the point though. It is opening Excel visibly, because I didn't know which way you needed it, plus its easier to see what happened right away when testing. Plus, I just leave it open when its done, instead of saving, closing, quitting, but I left some code in there, but commented out, for doing so, if you want.
Here is the iLogic/vb.net code:
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Sub Main
Dim oXLFile As String = "C:\Temp\MyExcelFile.xlsx"
Dim oSheetName As String = "Sheet1"
Dim oExcel As Excel.Application = GetExcel(True)
If IsNothing(oExcel) Then Exit Sub
Dim oWB As Workbook = GetWorkbook(oExcel, False, oXLFile)
If IsNothing(oWB) Then Exit Sub
Dim oWS As Worksheet = GetWorksheet(oWB, False, oSheetName)
If IsNothing(oWS) Then Exit Sub
Dim oRangeToSort As Range = oWS.UsedRange
oRangeToSort.Select
' Dim oLastRowUsed As Integer = oRangeToSort.Rows.Count
' Dim oLastColumnUsed As Integer = oRangeToSort.Columns.Count
Dim oKey As Range = oRangeToSort.Range("A1").EntireColumn
oRangeToSort.Sort(oKey, XlSortOrder.xlAscending, , , , , , _
XlYesNoGuess.xlNo, , False, XlSortOrientation.xlSortColumns, _
XlSortMethod.xlStroke, XlSortDataOption.xlSortNormal, _
XlSortDataOption.xlSortNormal, XlSortDataOption.xlSortNormal)
'oWS = Nothing
'oWB.Save
'oWB.Close
'oWB = Nothing
'oExcel.Quit
'oExcel = Nothing
End Sub
Function GetExcel(Optional oVisible As Boolean = False) As Excel.Application
Dim oXL As Excel.Application
Try
'try to find an already running instance of the Excel Application
oXL = GetObject(, "Excel.Application")
Catch
'it wasn't found open, so create an instance of it (start the application)
oXL = CreateObject("Excel.Application")
Catch
Return Nothing
End Try
oXL.Visible = oVisible
Return oXL
End Function
Function GetWorkbook(oExcelApp As Excel.Application, oNew As Boolean, Optional oTemplateOrFullFileName As String = vbNullString) As Workbook
If oExcelApp Is Nothing Then Return Nothing
Dim oFileProvided As Boolean = False
If oTemplateOrFullFileName <> "" Then oFileProvided = True
Dim oFileExists As Boolean = False
If oFileProvided Then oFileExists = System.IO.File.Exists(oTemplateOrFullFileName)
If oNew = False And oFileExists = False Then Return Nothing
Dim oWBs As Workbooks = oExcelApp.Workbooks
Dim oWB As Workbook = Nothing
If oNew = True AndAlso oFileExists Then
oWB = oWBs.Add(oTemplateOrFullFileName)
Return oWB
ElseIf oNew = True AndAlso oFileExists = False Then
oWB = oWBs.Add
Return oWB
End If
If oWBs.Count > 0 Then
For Each oWkbk As Workbook In oWBs
If oWkbk.FullName = oTemplateOrFullFileName Then Return oWkbk
Next
ElseIf oWBs.Count = 0 Then
oWB = oWBs.Open(oTemplateOrFullFileName)
Return oWB
End If
End Function
Function GetWorksheet(oWorkbook As Workbook, oNew As Boolean, Optional oSheetName As String = vbNullString) As Worksheet
If oWorkbook Is Nothing Then Return Nothing
Dim oNameProvided As Boolean = False
If oSheetName <> "" Then oNameProvided = True
If oNew = False And oNameProvided = False Then Return Nothing
Dim oWSs As Excel.Sheets = oWorkbook.Worksheets
Dim oWS As Worksheet = Nothing
If oNew Then
oWS = oWSs.Add(, , 1, XlSheetType.xlWorksheet)
Else 'oNew was False, so try to find existing
If oWSs.Count = 0 Then Return Nothing
Try
oWS = oWSs.Item(oSheetName)
Catch
Return Nothing
End Try
End If
If oWS IsNot Nothing Then
If oNameProvided Then
If oWS.Name <> oSheetName Then oWS.Name = oSheetName
End If
End If
Return oWS
End Function
If this solved your problem, or answered your question, please click ACCEPT SOLUTION .
Or, if this helped you, please click (LIKE or KUDOS) 👍.
Wesley Crihfield
(Not an Autodesk Employee)
Hi @D.Wheeler3GADA. For your information and research, here is a link to that Sort method I am using. (You may have to change the drop-down on the webpage from C# to VB, in order to see the block of code that way.) It is definitely a pretty big one, with a lot of possible input variables, but all input variables are 'Optional', and as you can see, I left many of them out, because I did not think they were necessary. And some I only filled in a value for, because when used multiple times, some of those option/choices are retained in memory, and the only way to use a different option than the last time is to specify it.
The custom Functions are also not really needed, but are convenient tools for varying needs. As long as you know very specifically what you want to have happen every time, you can avoid using them to shorten the code by transferring a few lines out of them to the Sub Main area. For instance, if you want to always start a new instance of the Excel application, even if it may already be running; or if you always want to retrieve an already running instance, and never want to create a new session, you can skip the GetExcel function. When the Excel file already exists, the only slight benefit you might get from the GetWorkbook Function is that it will try to retrieve the already open document, before trying to open it. And I guess if the named sheet already exists, you don't really get much benefit from the GetSheet function either, so you could fairly easily eliminate that too, by transferring a little code out. Those were just ready made tools that I had sitting in my 'arsenal', so I included them in the post out of convenience and future aid to you and others.
Wesley Crihfield
(Not an Autodesk Employee)
Hi @mcloughlin_b. The 'UsedRange' property of the Excel.Worksheet Interface is pretty sweet. However, if you only want to be working with the data starting on row 3, and you may not know how many rows there are (or the number of rows varies), then you can you can likely still take advantage of the UsedRange property, like I am doing in Lines 15 & 16 the example above, for finding the last used row, and last used column. That gives us the location / address of the lower, right cell in the 'used range'. Then, instead of using the 'used range' itself as the range to sort, you can specify a range that starts with a specified starting cell address ("A3"), to the specified lower, right cell address, for the 'Range' to sort.
oRangeToSort = oWS.Range("A3", "C" & oLastRowUsed.ToString)
Wesley Crihfield
(Not an Autodesk Employee)
