VBA to add new sheet in a closed excel without ope

Question

I have an excel "Closed.Xls" which is not opened currently.

Please let me know how to add new sheets in to this closed excel file. I know how to add new sheets in current excel.

Dim WS as Worksheet
Set WS = Sheets.Add

Please let me know how to

1. add new sheets in a closed excel using VBA, and
2. get the name of the sheet added

Note: I don't want to rename the sheet.

Thanks

Answer 1

Something like this will will do what you want. It is not possible without opening the workbook but if you turn off screen updating it gives the appearance of not being opened.

Option Explicit
Sub Add_Sheet_ClosedBook()
    Dim bk As Workbook
    Dim sh As Worksheet
    Dim shName As String

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    Set bk = .Workbooks.Open _
    ("Path to Book.xls")
    End With

    With bk
        Set sh = .Sheets.Add
        shName = sh.Name
        .Save
        .Close
    End With

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

Answer 2

To get the sheet name use a worksheet variable, ie

Sub Added()
    Dim Wb As Workbook
    Dim ws As Worksheet

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    Set Wb = Workbooks.Open("c:\Temp\closed.xls")
    Set ws = Wb.Sheets.Add
    Debug.Print ws.Name
    Wb.Save
    Wb.Close

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub

Answer 3

It is possible to add a sheet without opening the workbook by using the Microsoft.ACE.OLEDB.12.0 provider:

set cn = new adodb.connection

with cn
   .provider = "Microsoft.ACE.OLEDB.12.0"
   .connectionstring = "Data Source=" & strSomeFilename & ";Extended Properties=""Excel12.0;"""
   .open
end with

set cmd = new adodb.command
cmd.activeconnection = cn
cmd.commandtext = "CREATE TABLE MySheet (ID char(255))"
cmd.execute

This will add a heading "ID" to cell A1 of the new sheet. You can probably find a way to delete/change it if necessary.