1

So I am using ADO to import data from several workbooks in a folder with several tabs in them. I have been able to set up all of the tabs to work with this except a tab named "MAT. (2)". When I use the code below, it gives me an error saying "Invalid Bracketing". I can't change the name of the tab due to it being a controlled document. Is there a way I can put the tab name in a format that will be accepted or possibly some way that I could address "Sheet3" instead (I already tried that and it threw an error saying it couldn't find the object).

Set rs = dbConnection.Execute("SELECT * FROM [" & SourceSheet$ & "$" & SourceRange & "]")
Set TargetCell = Range(TargetRange).Cells(1, 1)
TargetCell.CopyFromRecordset rs

Edit -

The connection string that I am using is as follows:

 dbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
 "Data Source=" & SourceFile & ";" & _
 "Extended Properties=""Excel 12.0 Xml;HDR=No"";"
110SidedHexagon
  • 555
  • 2
  • 14
  • 37
  • 1
    Possible answer here: http://stackoverflow.com/questions/24555616/query-sheet-names-with-spaces-using-adodb-in-vba Or open a read-only copy, change the tab name, and then run the query: you can then close the file without saving. – Tim Williams Nov 14 '14 at 18:23
  • The escaped characters didn't seem to do anything. As for opening the workbook, I suppose I could do that, though I was hoping to not have to do that since opening files out where they are located is slow. – 110SidedHexagon Nov 14 '14 at 18:38
  • Is it the `(2)` that is causing the problem or the ** before the `(2)`? –  Nov 14 '14 at 20:43
  • Yeah, there are other tabs that have spaces in them and they work fine for me. – 110SidedHexagon Nov 17 '14 at 12:23
  • You said the filename is `MAT. (2)` - is it the `(2)` or the `.` that is the problem?? Maybe make a copy of the workbook and test which part is the problem. – CBRF23 Nov 21 '14 at 21:29

1 Answers1

0

You could try using single or double quotes to escape the name, rather than brackets. What provider are you using to connect? I had an issue with connecting to a SQL Server database with a hyphenated name. I tried everything I could think of to escape the name, but it wouldn't work. I ended up solving the problem by switching from an ODBC connection to OLE DB connection. Maybe try the ACE OLEDB 12.0 provider and see if you can get it to work that way. OLE DB excel connstrings

EDIT:
Have you tried escaping the database name in backticks? I'm thinking the . is the issue as this character is usually illegal as a database or table name in most SQL based RDBMS's. So try something like: 

Set rs = dbConnection.Execute("SELECT * FROM `"& SourceSheet$ & "$" & SourceRange &`"

or possibly

Set rs = dbConnection.Execute("SELECT * FROM [`" & SourceSheet$ & "$" & SourceRange & "`]"

I'm not sure if either of these will work, but is worth a shot. If you can, you might want to remove the . from any worksheet names as it might cause some funny problems.

CBRF23
  • 1,340
  • 1
  • 16
  • 44
  • Did you try single quotes or double quotes? i.e. `"SELECT * FROM '" & SourceSheet$ & "$" & SourceRange & "'"` or even `"SELECT * FROM ['" & SourceSheet$ & "$" & SourceRange & "']"` or something like that. – CBRF23 Nov 21 '14 at 21:27
  • @110SidedHexagon Did you see my comment on your original question regarding the `.` vs the `(2)` being the problem? I'm wondering if it's possibly the `.` that is causing the issue. – CBRF23 Nov 24 '14 at 20:42
  • Yes, I have confirmed that the (2) is the issue. When I took it off (leaving "Mat." in the code and some practice excel workbooks) it works just fine. – 110SidedHexagon Nov 25 '14 at 14:28