0

I am trying to select everything in the B - E columns from row 4 onwards, where row 4 are the headers. My sheet name is " PRODUCTION_Insurer Index".

My research so far has hinted that because my sheet name has spaces in it, I need to wrap it in single quotes along with adding a $ onto the end, this lead me to the following SQL:

SELECT * FROM [' PRODUCTION_Insurer Index$'] which selects everything just fine. I am having trouble with actually selecting the range.

Furthermore, I researched on Ranges and figured that with names such as Sheet1, I could do SELECT * FROM [Sheet1$B4:E] or something similar to select a range

However the following SQLs all Fail with the error:

The Microsoft Jet database engine could not find the object '' PRODUCTION_Insurer Index$B4:E''. Make sure the object exists and that you spell its name and the path name correctly.

SELECT * FROM [' PRODUCTION_Insurer Index$B4:E']

SELECT * FROM [' PRODUCTION_Insurer Index$'B4:E]

SELECT * FROM [' PRODUCTION_Insurer Index'$B4:E]

SELECT * FROM [' PRODUCTION_Insurer Index$B4:E313']

SELECT * FROM [' PRODUCTION_Insurer Index$'B4:E313]

SELECT * FROM [' PRODUCTION_Insurer Index'$B4:E313]

Adam Lonsdale
  • 37
  • 7

1 Answers1

2

Ohhh, close. Try...

 "Select * From [PRODUCTION_Insurer Index$B4:E313]"
Ciarán
  • 3,017
  • 1
  • 16
  • 20
  • 1
    Hello, Thanks for your reply. I thought this had worked at first, but unfortunately it has the same effect as just doing a select * and providing no range at all! Thanks – Adam Lonsdale Dec 17 '12 at 16:30
  • 1
    Ditch the single quotes, you don't need them – Ciarán Dec 17 '12 at 16:51
  • Did you try it without the quotes? – Ciarán Dec 17 '12 at 18:22
  • 1
    The above works for me on a test, but using the ACE drivers, not Jet. – Fionnuala Dec 18 '12 at 01:05
  • Hello, Unfortunately this is not working with the JET engine the error I get returned is "Invalid bracketing of name ' PRODUCTION_Insurer Index$B4:E'." I may have to look into ACE, but I'm not aware that I can use that as this is an Excel 2000 workbook. – Adam Lonsdale Dec 18 '12 at 09:07
  • 1
    Ah. Apologies I missed the reference to Jet, I am using ACE as well and it worked fine for me. I've used the ACE drivers for .xls files without any problems. However let me have a go with the Jet driver. – Ciarán Dec 18 '12 at 09:15
  • Hmm, Interestingly I cannot get it to work with ACE either, my connection string is "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName & "; Extended Properties=""Excel 8.0;HDR=YES"";" and running the SQL "Select * From [PRODUCTION_Insurer Index$B4:E313]" also returns the invalid bracketing error. – Adam Lonsdale Dec 18 '12 at 09:20
  • Odd. I've just tried with JET and it works just fine too. i.e. The connection string is "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Test.xls;Extended Properties=""Excel 8.0;HDR=No;IMEX=1""" and the Select statement is as above. – Ciarán Dec 18 '12 at 09:32
  • With ACE, specify `Excel 12.0` – Ciarán Dec 18 '12 at 09:33
  • Just had a go with your JET connection string (the only difference was that mine was lacking the IMEX=1) and I still get the bracketing error on that select statement. The only difference I can think is that my sheet name has a space at the beginning. – Adam Lonsdale Dec 18 '12 at 09:39