0

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'?

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
user9423162
  • 27
  • 1
  • 6
  • 2
    Try it without the `Set` keyword, which is making `vbscript` think you are setting an object reference... – Dave Dec 27 '18 at 18:29
  • Possible duplicate of ["Object required" when using Set in an assignment](https://stackoverflow.com/questions/23633643/object-required-when-using-set-in-an-assignment) – Geert Bellekens Dec 28 '18 at 07:42
  • @GeertBellekens Not a duplicate. The OP already suspected that their attempt wouldn't work. The question is about whether or not worksheets can be renamed using the database interface, not about the error this particular approach yielded. Even if you removed the `Set` keyword renaming the workbook like that would still fail. – Ansgar Wiechers Dec 28 '18 at 10:30

1 Answers1

0

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
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328