Warm tip: This article is reproduced from stackoverflow.com, please click
excel sql vba excel-vba

Copy and add a sheet to a closed workbook

发布于 2020-03-27 10:25:21

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?

Questioner
Frans Bstrom
Viewed
83
Felix Exner 2019-07-03 22:48

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.