Formatting an exported Excel spreadsheet
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
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