I have been writing some macros using ADODB queries to retrieve data from other workbooks, using Excel 2013. During testing an 'invalid bracketing' error appeared on some cases and with trial and error, I came to the conclusion that ADODB could not handle sheetnames containing a dot followed by a space. In other words:
XYZ S.à r.l. -12.2017 -> error
XYZ S.à r.l -12.2017 -> works ok (dot before space is deleted)
XYZ S.à r.l.-12.2017 -> works ok (space after dot is deleted)
Of course in the query the sheet name is surronded by backticks :-)
The error msg -2147217900 is; Invalid bracketing of name 'XYZ S.à r.l. -12.2017$A9:H49'
My question is: do you know any other similar issue that I should track ? Is that documented anywhere ?
update: the generated SQL statement (but again, this has been tested successfully in a number of other workbooks, and I managed to make it work by renaming the 1st of the 2 worksheetss involved as described above)
select cstr(pr.reference), pr.[name],null, pr.item, pr.country, pr.currency, pr.maturity, pr.sector
from [Excel 12.0 Xml;HDR=Yes;Database=\\mydomain.com\someLongPath\XYZ S.à r.l. - 12.2017 - reviewed - MACRO TESTING.xlsx].`XYZ S.à r.l. -12.2017$A9:H49` pr
where cstr(pr.reference) not in (select cur.reference from [Excel 12.0 Xml;HDR=Yes;Database=\\mydomain.com\someLongPath\KB Sarl - 01.2018 to 03.2018 - MACRO TESTING.xlsx].`TB - 03.2018$A9:H51`cur )
and pr.item is not null
This query gets the data from Previous list whose Reference is absent from Current list. Previous and Current lists are normally in different workbooks.
Just to close this question:
I came to the conclusion that ADODB could not handle sheetnames containing a dot followed by a space (". ").
Consequently I just added a check for this.