COMMUNITY FORUM

Thanks to Patrick’s help, I am making progress formatting an excel spreadsheet after exporting it from SmartConnect.  I am successfully doing the following:
1. Rename column headers to my desired names.
2. Format columns with correct number format.
3. Format numbers stored as text to numbers.
4. Create subtotals of amount columns.
5.  Set the column width to Auto Fit for all columns.
 
One remaining task escapes me – sorting my rows before I subtotal them.  Column A has a header of ‘AR Account’.  I want to sort all rows by that column, obviously preserving the column header.   This code works, but it doesn’t know to use Row 1 as the column header.  When I run it, it prompts with message “Microsoft Excel cannot determine which row in your list or selection contains column labels, which are required for this command…..”  I need it to automatically use the first row as column headers.  Here’s my code:
‘xlRange.Sort (xlRange.Columns(“A”))
 
 
 
What do I need to add to it to get it to know to use Row 1 as the header row?  I’ve tried a variety of things with no luck, some based off this:
‘xlWorkSheet.UsedRange.Sort(Key1:=xlWorksheet.Range(“A1”), Order1:=Ascending, Header:=Excel.XlYesNoGuess.xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=Excel.XlSortOrientation.xlSortColumns, DataOption1:=Excel.XlSortDataOption.xlSortNormal)
 
 
 
I feel like I am SO CLOSE!  Thanks in advance!
 
PS here’s the rest of my code, in case it helps somebody else.
dim filenameOnly as string


dim extension as string


dim destpath as string


dim NewOutputFileName as string


dim endDateTime as date


Dim xlApp As Object
 
Dim xlWorkBook As Object
 
Dim xlWorkSheet As Object
 
Dim xlRange as Object


 
　


‘MessageBox.show(GlobalFileName)
 
‘MessageBox.show(GlobalFilePath)
 
 
　
 
‘get the base path, file name, and extension from the global that SQL Task populated
 
 
filenameonly= Path.GetFileNameWithoutExtension(GlobalFileName)
 
extension = Path.GetExtension(GlobalFileName)
 
destpath = Path.GetDirectoryName(GlobalFileName)
 
　
 
‘let VB implicitly convert the End Date from the system into a date field
 
 
endDateTime = GlobalRunEnded
 
　
 
‘Get the output file name that SmartConnect exported to using
 
‘new file output name with timestamp added
 
 
 
NewOutputFileName = destpath + “\” +


filenameonly + “_” +


endDateTime.tostring(“yyyy-MM-dd_hh-mm-ss”) +
 


extension


　


‘MessageBox.show(NewOutputFileName)
 
 
　
 
‘use COM automation from VB to open the file and sheet.
 
 
xlApp = Microsoft.VisualBasic.CreateObject(“Excel.Application”)
 
‘xlApp.Visible = True
 
 
xlWorkBook = xlApp.Workbooks.Open(NewOutputFileName)
 
xlWorkSheet = xlWorkBook.Worksheets(“sheet1”)
 
　
 
‘Fix Column Headers
 
 
 
xlWorkSheet.Cells(1, 1) = “AR ACCOUNT”


xlWorkSheet.Cells(1, 2) = “CUSTOMER ID”
 
xlWorkSheet.Cells(1, 3) = “CUSTOMER NAME”
 
xlWorkSheet.Cells(1, 17) = “ORIGINAL AMOUNT”
xlWorkSheet.Cells(1, 18) = “BALANCE AMOUNT DUE”
 
 
 
 
 
 
 
 
‘Format columns with correct number format


 
 
xlWorkSheet.Columns(“Q:V”).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
 
xlWorkSheet.Columns(“N:O”).NumberFormat = “#,##0_);[Red](#,##0)”


 
　


‘Format numbers stored as text to numbers
 
 
 
xlWorkSheet.Columns(“Q:V”).Value = xlWorksheet.Columns(“Q:V”).Value


xlWorkSheet.Columns(“N:O”).Value = xlWorksheet.Columns(“N:O”).Value
 


　


‘Set xlRange = all cells used in the worksheet (UsedRange)
 
 
xlRange=xlWorksheet.UsedRange
 
　
 
‘Sort by Column A
 
‘xlWorkSheet.UsedRange.Sort(Key1:=xlWorksheet.Range(“A1”), Order1:=Ascending, Header:=Excel.XlYesNoGuess.xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=Excel.XlSortOrientation.xlSortColumns, DataOption1:=Excel.XlSortDataOption.xlSortNormal)
 
 
　
 
‘xlWorksheet.UsedRange.Sort (Key1:=xlWorksheet.Columns(“A”), Order1:=xlAscending)
 
‘, Order1:=Excel.xlSortOrder.xlAscending, Key2:=xlWorksheet.UsedRange.Columns(3), Order2:=XlSortOrder.xlAscending, Key3:=xlWorksheet.UsedRange.Columns(6), Order3:=XlSortOrder.xlAscending, Orientation:=XlSortOrientation.xlSortColumns, Header:=XlYesNoGuess.xlNo, SortMethod:=XlSortMethod.xlPinYin, DataOption1:=XlSortDataOption.xlSortNormal, DataOption2:=XlSortDataOption.xlSortNormal, DataOption3:=XlSortDataOption.xlSortNormal)
 
 
　
 
‘This works, but it doesn’t know to use Row 1 as column headings
 
‘xlRange.Sort (xlRange.Columns(“A”))
 
 
　
 
 
xlRange.Sort (xlRange.Columns(“A”), Excel.XlSortOrder.xlAscending, , , , , , Excel.XlYesNoGuess.xlYes)
 


　


　


　


　


　


　


‘Create Subtotals
 
‘Subtotals all used cells (UsedRange) in Columns 17-22 by Column 1
 
 
 
xlWorkSheet.UsedRange.Subtotal (GroupBy:=1, Function:=-4157, TotalList:=New Integer() {17, 18, 19, 20, 21, 22}, Replace:=True, PageBreaks:=False, SummaryBelowData:=True)
 


　


‘Adjust column widths
 
 
xlWorkSheet.Cells.EntireColumn.AutoFit()
 
　
 
‘finally close the workbook
 
 
xlWorkBook.Save()
 
xlWorkBook.Close()
 
xlApp.Quit()
 
 
return true