asked on

Formating an Excel Document

This is going to be a multiple part question. It all falls under the same area.
Formating an excel document.

I can do bold = .Rows(1).Font.Bold = True
I can underline = .Rows(1).Font.Underline = True
I can format as text = .Columns(6).NumberFormat = "@"

I can't format a column as currency
I can't center justify the entire sheet
I can't get the columns to expand to encompass the size of the largest cell in them

Millkind

ASKER

oSheet.Range("C:C").EntireColumn.AutoFit

Will make the columns expand to fit, don't need that answer anymore.

Bob Learned

1) Center justify cells:

' Select all cells, returning the selection as a range
Public Function SelectAll() As Excel.Range

If _worksheet Is Nothing Then
Throw New ArgumentNullException("Worksheet not open")
End If

Return CType(_worksheet.Cells.Select(), Excel.Range)

End Function

Setting alignment:

Public Sub CellAlignment(ByVal range As Excel.Range, ByVal horizontal As Excel.Constants, ByVal vertical As Excel.Constants)
range.HorizontalAlignment = horizontal
range.VerticalAlignment = vertical
End Sub

2) NumberFormat = "Currency"

Bob

Millkind

ASKER

for the line = .Columns(3).NumberFormat = "Currency"

Error:
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in microsoft.visualbasic.dll

Additional information: Unable to set the NumberFormat property of the Range class

for the lines =
If .Sheets.Item("Usage") Is Nothing Then
Throw New ArgumentNullException("Worksheet not open")
End If
Return CType(.Sheets.Item("Usage").Cells.Select(), Microsoft.Office.Interop.Excel.Range)

Error:
An unhandled exception of type 'System.InvalidCastException' occurred in Charge Slip Monthly Sender.exe

Additional information: Specified cast is not valid.

ASKER CERTIFIED SOLUTION

vadim63

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vadim63

or for entire sheet:

eApp.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
eApp.Columns.AutoFit()

Millkind

ASKER

.Range("A1", "Z100").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

errors out saying that xlHALign is not found

the formatting worked great

vadim63

Try this:

.HorizontalAlignment = xlCenter

Millkind

ASKER

same thing

vadim63

What is the versions of Office, VS?

Millkind

ASKER

2003 im using the Microsoft Excel 11.0 Object Library

vadim63

I have VS2005. Not sure about 2003.
.Range("A1", "Z100").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter - works fine for me.

Could you post a whole Sub for formatting?

Millkind

ASKER

Private Function fncExcelExport(ByVal ds As DataSet, ByVal ds2 As DataSet)

Dim Excel As New Microsoft.Office.Interop.Excel.Application
Dim intColumn, intRow, intColumnValue As Integer
With Excel
.SheetsInNewWorkbook = 2
.Workbooks.Add()
.Sheets.Item("Sheet1").Name = "Usage"
.Sheets.Item("Sheet2").Name = "Patrons"
.Worksheets(1).Select()
.Columns(5).NumberFormat = "@"
.Columns(3).NumberFormat = "$#,##0.00"
.Cells(1, 1).Value = "Date"
.Cells(1, 2).Value = "Location"
.Cells(1, 3).Value = "Amount"
.Cells(1, 4).Value = "Name"
.Cells(1, 5).Value = "Account Number"
'For displaying the column value row-by-row in the the excel file.
For intRow = 0 To ds2.Tables(0).Rows.Count - 1
For intColumnValue = 0 To ds2.Tables(0).Columns.Count - 1
If ds2.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString.StartsWith("Front") Then
.Cells(intRow + 2, intColumnValue + 1).Value = "Dining Hall"
ElseIf ds2.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString.StartsWith("BH") Then
.Cells(intRow + 2, intColumnValue + 1).Value = "Bullet Hole"
ElseIf ds2.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString.StartsWith("IKES") Then
.Cells(intRow + 2, intColumnValue + 1).Value = "Ike's Sub Shop"
Else
.Cells(intRow + 2, intColumnValue + 1).Value = ds2.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
End If
Next
Next
.Rows(1).Font.Bold = True
.Rows(1).Font.Underline = True
.Range("A:F").EntireColumn.AutoFit()
'.Range("A1", "Z100").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
.Worksheets(2).Select()
.Columns(3).NumberFormat = "@"
.Columns(4).NumberFormat = "$#,##0.00"
.Cells(1, 1).Value = "ID Number"
.Cells(1, 2).Value = "Name"
.Cells(1, 3).Value = "Account Number"
.Cells(1, 4).Value = "Max Limit Remaining"
'For displaying the column value row-by-row in the the excel file.
For intRow = 0 To ds.Tables(0).Rows.Count - 1
For intColumnValue = 0 To ds.Tables(0).Columns.Count - 1
.Cells(intRow + 2, intColumnValue + 1).Value = ds.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
Next
Next
.Rows(1).Font.Bold = True
.Rows(1).Font.Underline = True
.Range("A:F").EntireColumn.AutoFit()
.ActiveWorkbook().SaveAs("C:\stuff\" & co & "-" & DatePart(DateInterval.Month, DateAdd(DateInterval.Month, -1, Date.Today)) & "-" & DatePart(DateInterval.Year, DateAdd(DateInterval.Month, -1, Date.Today)) & ".xls")
.ActiveWorkbook.Close()
End With

NormalExit:
Excel.Quit()
Excel = Nothing
GC.Collect()
Exit Function
End Function

Millkind

ASKER

.Range("A1", "Z100").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter

WORKED!!

vadim63

Yep! I guess, you didn't import "Microsoft.Office.Interop" first. Right?

Millkind

ASKER

nope i just excel, figured why bring all of office if im just using excel. Live and Learn
Thanks for your help sorry

vadim63

What I would suggest:

Put "Imports Microsoft.Office.Interop" before your "Class";

Rename Excel to something (e. g. "ExApp") because of the names conflict:

Dim ExApp As New Excel.Application

Dim intColumn, intRow, intColumnValue As Integer
With ExApp
.SheetsInNewWorkbook = 2
.Workbooks.Add()
.Sheets.Item("Sheet1").Name = "Usage"
.Sheets.Item("Sheet2").Name = "Patrons"
.Worksheets(1).Select()
.Columns(5).NumberFormat = "@"
.Columns(3).NumberFormat = "$#,##0.00"
.Cells(1, 1).Value = "Date"
.Cells(1, 2).Value = "Location"
.Cells(1, 3).Value = "Amount"
.Cells(1, 4).Value = "Name"
.Cells(1, 5).Value = "Account Number"
'For displaying the column value row-by-row in the the excel file.
For intRow = 0 To ds2.Tables(0).Rows.Count - 1
For intColumnValue = 0 To ds2.Tables(0).Columns.Count - 1
If ds2.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString.StartsWith("Front") Then
.Cells(intRow + 2, intColumnValue + 1).Value = "Dining Hall"
ElseIf ds2.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString.StartsWith("BH") Then
.Cells(intRow + 2, intColumnValue + 1).Value = "Bullet Hole"
ElseIf ds2.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString.StartsWith("IKES") Then
.Cells(intRow + 2, intColumnValue + 1).Value = "Ike's Sub Shop"
Else
.Cells(intRow + 2, intColumnValue + 1).Value = ds2.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
End If
Next
Next
.Rows(1).Font.Bold = True
.Rows(1).Font.Underline = True
.Range("A:F").EntireColumn.AutoFit()
.Range("A1", "Z100").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
.Worksheets(2).Select()
.Columns(3).NumberFormat = "@"
.Columns(4).NumberFormat = "$#,##0.00"
.Cells(1, 1).Value = "ID Number"
.Cells(1, 2).Value = "Name"
.Cells(1, 3).Value = "Account Number"
.Cells(1, 4).Value = "Max Limit Remaining"
'For displaying the column value row-by-row in the the excel file.
For intRow = 0 To ds.Tables(0).Rows.Count - 1
For intColumnValue = 0 To ds.Tables(0).Columns.Count - 1
.Cells(intRow + 2, intColumnValue + 1).Value = ds.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
Next
Next
.Rows(1).Font.Bold = True
.Rows(1).Font.Underline = True
.Range("A:F").EntireColumn.AutoFit()
.ActiveWorkbook().SaveAs("C:\stuff\" & co & "-" & DatePart(DateInterval.Month, DateAdd(DateInterval.Month, -1, Date.Today)) & "-" & DatePart(DateInterval.Year, DateAdd(DateInterval.Month, -1, Date.Today)) & ".xls")
.ActiveWorkbook.Close()
End With

NormalExit:
ExApp.Quit()
ExApp = Nothing
GC.Collect()
Exit Function
End Function

And that's it.

And another thing:
I would suggest you to use Try ... Catch ... Finally block. If something in the middle goes wrong, you can catch the error and you can place into Finally block code like this:

Dim p() As Process = Process.GetProcessesByName("EXCEL")
Dim x As Integer
For x = 0 To p.Length - 1
p(x).Kill()
Next

It kills excel process for sure.

Good luck. Thanks for the points.

Millkind

ASKER

Man that is perfect i was wondering why i had excels running around in my task manager.
You Rock