Using SQL I can access and edit cells in Excel workbooks without opening them in a vba script. However I have not yet found a way to copy or add a sheet in a similar way. Atm I use Workbook.Open and Copy to accomplish it. Is this possible?
I got the same problem and searched for hours until found this one using ADODB-Connection Object:
path= <yourFilePath>
set cn = new ADODB.connection
'create connection String
connStr="Provider=Microsoft.ACE.OLEDB.12.0; & _
"Data Source=" & path & ";" & _
"Extended Properties='Excel 12.0;HDR=YES;"";ReadOnly=0'"
cn.Open connStr
set cmd = new adodb.command
cmd.ActiveConnection = cn
'create sql-string
cmd.CommandText = "CREATE TABLE [<TableName>] (<anyString> char(255))"
cmd.Execute
see: VBA to add new sheet in a closed excel without opening & get the sheet name added?
I think that's what you and everyone else who stumble about this post is looking for.
Further you can create a String INSERT INTO [<TableName>$] VALUES('<values>')
to add some data to the sheet.