Public Class Form1
Private Sub ExportExcel()
Dim xlApp As Object
If xlApp Is Nothing Then
MessageBox.Show("Excel is not properly installed!!")
Return
End If
Dim i As Integer
Dim xlWorkBook As Object
Dim xlhdWorkSheet As Object
xlWorkBook = xlApp.Workbooks.Add
xlhdWorkSheet = xlApp.Workbooks.Item(1).Worksheets(1)
With xlhdWorkSheet
.PageSetup.Orientation = 2
.PageSetup.LeftMargin = 0.5
.PageSetup.RightMargin = 0.2
.PageSetup.BottomMargin = 0.2
.PageSetup.TopMargin = 0.4
.Cells(1, 5) = "Title"
.Range(.Cells(1, 1), .Cells(1, 5)).Merge() 'Merge column
.Range(.Cells(1, 1), .Cells(2, 5)).Merge() 'Merge row
.Range(.Cells(1, 1), .Cells(1, 5)).WrapText = True 'Wrap Text
.Cells(3, 1) = "ColumnA"
.Cells(3, 2) = "ColumnB"
.Cells(3, 3) = "ColumnC"
.Cells(3, 4) = "ColumnD"
.Cells(3, 5) = "ColumnE"
.Range(.Cells(1, 1), .Cells(3, 5)).Font.Bold = True
.Range(.Cells(1, 1), .Cells(3, 5)).HorizontalAlignment = -4108
.Range(.Cells(1, 1), .Cells(3, 5)).VerticalAlignment = -4108
.Range(.Cells(3, 1), .Cells(3, 5)).Interior.Color = RGB(220, 220, 220)
End With
For i = 4 To 8
With xlhdWorkSheet
.Cells(i, 1) = i
.Range(.Cells(i, 1), .Cells(i, 1)).HorizontalAlignment = -4108
.Cells(i, 2) = i
.Range(.Cells(i, 2), .Cells(i, 2)).HorizontalAlignment = -4108
.Cells(i, 3) = i
.Range(.Cells(i, 3), .Cells(i, 3)).HorizontalAlignment = -4108
.Cells(i, 4) = i
.Range(.Cells(i, 4), .Cells(i, 4)).HorizontalAlignment = -4108
.Cells(i, 5) = i
.Range(.Cells(i, 5), .Cells(i, 5)).HorizontalAlignment = -4108
End With
Next
'Summary
With xlhdWorkSheet
.Cells(i, 1) = "=SUM(A" & 3 & ":A" & i - 1 & ")"
.Cells(i, 2) = "=SUM(B" & 3 & ":B" & i - 1 & ")"
.Cells(i, 3) = "=SUM(C" & 3 & ":C" & i - 1 & ")"
.Cells(i, 4) = "=SUM(D" & 3 & ":D" & i - 1 & ")"
.Cells(i, 5) = "=SUM(E" & 3 & ":E" & i - 1 & ")"
.Range(.Cells(i, 1), .Cells(i, 5)).Font.Bold = True
.Range(.Cells(i, 1), .Cells(i, 5)).HorizontalAlignment = -4108
.Range(.Cells(i, 1), .Cells(i, 5)).Interior.Color = RGB(220, 220, 220)
End With
With xlhdWorkSheet
.Range(.Cells(1, 1), .Cells(i, 5)).EntireColumn.AutoFit()
.Range(.Cells(1, 1), .Cells(i, 5)).Borders.LineStyle = True
.Range(.Cells(1, 1), .Cells(i, 5)).Font.Size = 8.5
.Range(.Cells(1, 1), .Cells(i, 5)).Font.Name = "Tahoma"
.Columns(1).ColumnWidth = 10
.Columns(2).ColumnWidth = 10
.Columns(3).ColumnWidth = 10
.Columns(4).ColumnWidth = 10
.Columns(5).ColumnWidth = 10
End With
Dim dateTime As String = Format(Date.Today, "yyMMdd") & "_" & Format(TimeOfDay, "hhmm")
xlWorkBook.Worksheets(1).Name = "Sheet 1"
xlWorkBook.Worksheets(1).Activate()
xlApp.ActiveWindow.DisplayZeros = True
xlApp.ActiveWindow.Zoom = 100
xlApp.ActiveWindow.WindowState = -4137
xlhdWorkSheet.SaveAs(Filename:=Application.StartupPath & "\" & "Sheet1_" & dateTime & ".xlsx")
xlApp.Visible = True
End Sub
