Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
4 days, 16 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Formating an Excel Document
Posted on 2006-06-19
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
9
7
17 Comments
oSheet.Range("C:C").Entire
Will make the columns expand to fit, don't need that answer anymore.
Will make the columns expand to fit, don't need that answer anymore.
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("Wor
End If
Return CType(_worksheet.Cells.Sel
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
' Select all cells, returning the selection as a range
Public Function SelectAll() As Excel.Range
If _worksheet Is Nothing Then
Throw New ArgumentNullException("Wor
End If
Return CType(_worksheet.Cells.Sel
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
for the line = .Columns(3).NumberFormat = "Currency"
Error:
An unhandled exception of type 'System.Runtime.InteropSer
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("Wor
End If
Return CType(.Sheets.Item("Usage"
Error:
An unhandled exception of type 'System.InvalidCastExcepti
Additional information: Specified cast is not valid.
.Columns(1).NumberFormat = "$#,##0.00"
.Range("A1", "Z100").HorizontalAlignmen
.Range("A1", "Z100").Columns.AutoFit()
.Range("A1", "Z100").HorizontalAlignmen
.Range("A1", "Z100").Columns.AutoFit()
or for entire sheet:
eApp.Cells.HorizontalAlign
eApp.Columns.AutoFit()
eApp.Cells.HorizontalAlign
eApp.Columns.AutoFit()
.Range("A1", "Z100").HorizontalAlignmen
errors out saying that xlHALign is not found
the formatting worked great
errors out saying that xlHALign is not found
the formatting worked great
I have VS2005. Not sure about 2003.
.Range("A1", "Z100").HorizontalAlignmen
Could you post a whole Sub for formatting?
.Range("A1", "Z100").HorizontalAlignmen
Could you post a whole Sub for formatting?
Private Function fncExcelExport(ByVal ds As DataSet, ByVal ds2 As DataSet)
Dim Excel As New Microsoft.Office.Interop.E
Dim intColumn, intRow, intColumnValue As Integer
With Excel
.SheetsInNewWorkbook = 2
.Workbooks.Add()
.Sheets.Item("Sheet1").Nam
.Sheets.Item("Sheet2").Nam
.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.Coun
If ds2.Tables(0).Rows(intRow)
.Cells(intRow + 2, intColumnValue + 1).Value = "Dining Hall"
ElseIf ds2.Tables(0).Rows(intRow)
.Cells(intRow + 2, intColumnValue + 1).Value = "Bullet Hole"
ElseIf ds2.Tables(0).Rows(intRow)
.Cells(intRow + 2, intColumnValue + 1).Value = "Ike's Sub Shop"
Else
.Cells(intRow + 2, intColumnValue + 1).Value = ds2.Tables(0).Rows(intRow)
End If
Next
Next
.Rows(1).Font.Bold = True
.Rows(1).Font.Underline = True
.Range("A:F").EntireColumn
'.Range("A1", "Z100").HorizontalAlignmen
.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
.Cells(intRow + 2, intColumnValue + 1).Value = ds.Tables(0).Rows(intRow).
Next
Next
.Rows(1).Font.Bold = True
.Rows(1).Font.Underline = True
.Range("A:F").EntireColumn
.ActiveWorkbook().SaveAs("
.ActiveWorkbook.Close()
End With
NormalExit:
Excel.Quit()
Excel = Nothing
GC.Collect()
Exit Function
End Function
Dim Excel As New Microsoft.Office.Interop.E
Dim intColumn, intRow, intColumnValue As Integer
With Excel
.SheetsInNewWorkbook = 2
.Workbooks.Add()
.Sheets.Item("Sheet1").Nam
.Sheets.Item("Sheet2").Nam
.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.Coun
If ds2.Tables(0).Rows(intRow)
.Cells(intRow + 2, intColumnValue + 1).Value = "Dining Hall"
ElseIf ds2.Tables(0).Rows(intRow)
.Cells(intRow + 2, intColumnValue + 1).Value = "Bullet Hole"
ElseIf ds2.Tables(0).Rows(intRow)
.Cells(intRow + 2, intColumnValue + 1).Value = "Ike's Sub Shop"
Else
.Cells(intRow + 2, intColumnValue + 1).Value = ds2.Tables(0).Rows(intRow)
End If
Next
Next
.Rows(1).Font.Bold = True
.Rows(1).Font.Underline = True
.Range("A:F").EntireColumn
'.Range("A1", "Z100").HorizontalAlignmen
.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
.Cells(intRow + 2, intColumnValue + 1).Value = ds.Tables(0).Rows(intRow).
Next
Next
.Rows(1).Font.Bold = True
.Rows(1).Font.Underline = True
.Range("A:F").EntireColumn
.ActiveWorkbook().SaveAs("
.ActiveWorkbook.Close()
End With
NormalExit:
Excel.Quit()
Excel = Nothing
GC.Collect()
Exit Function
End Function
.Range("A1", "Z100").HorizontalAlignmen
WORKED!!
WORKED!!
nope i just excel, figured why bring all of office if im just using excel. Live and Learn
Thanks for your help sorry
Thanks for your help sorry
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").Nam
.Sheets.Item("Sheet2").Nam
.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.Coun
If ds2.Tables(0).Rows(intRow)
.Cells(intRow + 2, intColumnValue + 1).Value = "Dining Hall"
ElseIf ds2.Tables(0).Rows(intRow)
.Cells(intRow + 2, intColumnValue + 1).Value = "Bullet Hole"
ElseIf ds2.Tables(0).Rows(intRow)
.Cells(intRow + 2, intColumnValue + 1).Value = "Ike's Sub Shop"
Else
.Cells(intRow + 2, intColumnValue + 1).Value = ds2.Tables(0).Rows(intRow)
End If
Next
Next
.Rows(1).Font.Bold = True
.Rows(1).Font.Underline = True
.Range("A:F").EntireColumn
.Range("A1", "Z100").HorizontalAlignmen
.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
.Cells(intRow + 2, intColumnValue + 1).Value = ds.Tables(0).Rows(intRow).
Next
Next
.Rows(1).Font.Bold = True
.Rows(1).Font.Underline = True
.Range("A:F").EntireColumn
.ActiveWorkbook().SaveAs("
.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
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.
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").Nam
.Sheets.Item("Sheet2").Nam
.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.Coun
If ds2.Tables(0).Rows(intRow)
.Cells(intRow + 2, intColumnValue + 1).Value = "Dining Hall"
ElseIf ds2.Tables(0).Rows(intRow)
.Cells(intRow + 2, intColumnValue + 1).Value = "Bullet Hole"
ElseIf ds2.Tables(0).Rows(intRow)
.Cells(intRow + 2, intColumnValue + 1).Value = "Ike's Sub Shop"
Else
.Cells(intRow + 2, intColumnValue + 1).Value = ds2.Tables(0).Rows(intRow)
End If
Next
Next
.Rows(1).Font.Bold = True
.Rows(1).Font.Underline = True
.Range("A:F").EntireColumn
.Range("A1", "Z100").HorizontalAlignmen
.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
.Cells(intRow + 2, intColumnValue + 1).Value = ds.Tables(0).Rows(intRow).
Next
Next
.Rows(1).Font.Bold = True
.Rows(1).Font.Underline = True
.Range("A:F").EntireColumn
.ActiveWorkbook().SaveAs("
.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
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.
Featured Post
Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article explains how to create and use a custom WaterMark textbox class. The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.
IMAGE OF WATERMARKS
STEPS
Create VB …
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should. An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses
Course of the Month4 days, 16 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.