Search code examples
vbscriptoledb

Use Microsoft.ACE.OLEDB.12.0 to SET a worksheet name

The following works:

cnStg = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & oFile.Path & "; Extended Properties='Excel 12.0 Xml;HDR=YES';"
cn.Open cnStg 
Set adoWbkAsDatabase = CreateObject("ADOX.Catalog")
adoWbkAsDatabase.ActiveConnection = cn

For i = 0 To adoWbkAsDatabase.Tables.Count
    If Mid(adoWbkAsDatabase.Tables(i).Name, 2, 10) = "XXXXXX XXX" Then
        vSheetName = Split(Trim(Mid(adoWbkAsDatabase.Tables(i).Name, 12, 100)), "$")(0)

but the following:

Set adoWbkAsDatabase.Tables(i).Name = "XXXXXX XXX"

gives

Microsoft VBScript runtime error: Object required: 'adoWbkAsDatabase.Tables(...).Name'`

I kind of realise my approach should not work but is there a way to change the name of a worksheet using Provider=Microsoft.ACE.OLEDB.12.0'?

Solution

  • To my knowledge renaming Excel worksheets via the OLE database interface is not possible. You'd need the Excel COM object for that (i.e. a working Excel installation on the computer running the script):

    Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open(oFile.Path)

wb.Sheets(1).Name = "XXXXXX XXX"

wb.Save
wb.Close
xl.Quit