Excel Workbook, Font and Cell questions

[rpk_20061975]

I am using following code to export data to Excel

    Dim oExcel As Excel.Application
    Dim i
    Dim ans
    
    Set oExcel = CreateObject("Excel.Application")

    ''Check whether workbook is present in directory
    ans = Dir$(App.Path & "\reports\DayCollection.XLS", vbNormal)
    If ans = "" Then
        MsgBox "Default Excel Workbook was not found. Creating new.", vbInformation
        oExcel.Workbooks.Add App.Path & "\reports\DayCollection.xls"
    Else
        oExcel.Workbooks.Open Filename:=App.Path & "\reports\DayCollection.XLS"
    End If
    
    With oExcel
        .Cells(1, 5) = "ABC Inc."
        .Cells(3, 1) = "Day Collection Report     " & ProgramDate & " " & Time
        .Cells(5, 1) = "S No."
        .Cells(5, 2) = "Related Serials"
        .Cells(5, 3) = "Reg.No."
        .Cells(5, 4) = "Visitors"
        .Cells(5, 5) = "Officer"
        .Cells(5, 6) = "Total Fees"
        .Cells(5, 7) = "Visit Time"

I have few queries regarding above code:

(1) If a default Workbook file is not found in the directory, then how to create a new one with desired name.

(2) How to increase the cell width?

(3) How to set Font size and style which I need to display when Excel Worksheet is opened at last?

[BrianB]

Method 1
If the formatting is complicated it is far better to include a hidden sheet with the formatting required and use Copy.

Method 2
You can use the macro recorder to get the code details. This code snippet shows the principle :-

    Dim MyFolder As String
    MyFolder = App.Path & "\reports\"
    ChDrive MyFolder
    ChDir MyFolder
    '--------------------
    oExcel.Workbooks.Add
    ActiveSheet.Columns(1).EntireColumn.ColumnWidth = 20