Sorting a range of cells

[Derrick Repep]

Hi all,

I have created a .NET app that creates an Excel object in-memory. The first
worksheet is populated with data from an array that contains and header
information (row 1) and count data (rows 2 - m). The worksheet looks
something like this (with fictional data):

Strikes Spares Gutters
Fred 3 2 7
Barney 5 1 3
Wilma 12 0 0

I want to sort rows 2 - m on the first column in ascending (alpha) order. I
have an Excel Worksheet object correctly bound to the first worksheet.
Using the above table as an example, with "Fred" being in cell A2, I use the
following statements in VB.NET to populate the worksheet's cells, resize the
columns, and sort the data:

' Transfer the array data to the first worksheet starting at cell A1
excelWorksheet1.Range("A1").Resize(4, 4).Value = productData

excelWorksheet1.Columns.AutoFit() ' expand columns to display all
data

' Sort the range of cells from A2
excelWorksheet1.Range("A2:D4").Sort("A2",
Excel.XlSortOrder.xlAscending)

The last line generates the following error:
A first chance exception of type
'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll
Additional information: The formula you typed contains an error.

I have tried many, many variations on the sort call, and none of them have
worked. What is the proper way to call this method in order to get rows 2 -
m sorted alphabetically by the first column?

Thank you for your help in advance.

-Derrick

[Scott]

Post by Derrick Repep
Hi all,
I have created a .NET app that creates an Excel object in-memory. The first
worksheet is populated with data from an array that contains and header
information (row 1) and count data (rows 2 - m). The worksheet looks
Strikes Spares Gutters
Fred 3 2 7
Barney 5 1 3
Wilma 12 0 0
I want to sort rows 2 - m on the first column in ascending (alpha) order. I
have an Excel Worksheet object correctly bound to the first worksheet.
Using the above table as an example, with "Fred" being in cell A2, I use the
following statements in VB.NET to populate the worksheet's cells, resize the
' Transfer the array data to the first worksheet starting at cell A1
excelWorksheet1.Range("A1").Resize(4, 4).Value = productData
excelWorksheet1.Columns.AutoFit() ' expand columns to display all
data
' Sort the range of cells from A2
excelWorksheet1.Range("A2:D4").Sort("A2",
Excel.XlSortOrder.xlAscending)
A first chance exception of type
'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll
Additional information: The formula you typed contains an error.
I have tried many, many variations on the sort call, and none of them have
worked. What is the proper way to call this method in order to get rows 2 -
m sorted alphabetically by the first column?
Thank you for your help in advance.
-Derrick

You need to replace the sort "A2" with Range("A2"):


excelWorksheet1.Range("A2:D4").Sort(excelWorksheet1.Range("A2"),
Excel.XlSortOrder.xlAscending)

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities