Skip to content


Formatting an exported Excel spreadsheet

Sherry Whitten asked 4 years ago
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


‘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”) +



‘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)
‘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
‘finally close the workbook
return true

If you would like to submit an answer or comment, please sign in to the eOne portal.