Importing Data From Excel Using ADO

Hi! I'm attempting to write an Excel VBA macro that will import data from a closed Excel workbook. I found a procedure online at:

http://www.exceltip.com/st/Import_da...Excel/429.html

I tried the procedure at home and it worked fine, but when I do it at work, I receive this error:

Run-time error "-2147217900 (80040e14)':

[Microsoft][ODBC Excel Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Here is the procedure, with the faulty code highlighted:

Please Login or Register  to view this content.

I found this link http://www.prairiefyre.com/ciskb/def...p?id=37&Lang=1 that said this might be an invalid query for the reason that:

An ODBC Connection to an Excel document has a limited set of operations. The SQL Queries that can be executed on an Excel spreadsheet are limited to 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. If using a query that is more advanced, the 6160 will return the following error when testing the ODCB Connection:

TESTING ODBC CONNECTION...
CONNECTION TEST PASSED
TESTING QUERY: IF EXISTS (SELECT * FROM NAME) return 'TRUE'ELSE return 'FALSE'
TEST FAILED WITH ERROR:
ERROR [42000] [Microsoft][ODBC Excel Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

If this is true, can I make a different connection besides ODBC that will allow me to run this procedure? Or can I edit the code in some way that I won't have to use this statement? Or how else can I access objects from a closed workbook?

Thanks a bunch!

Dan

Hello Dan_Dollar,

I think you overlooked a key point of the post. You must set a reference to the ADO library in Visual Basic for Applications.

At the end of the post...

The macro example assumes that your VBA project has added a reference to the ADO object library.
You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft
ActiveX Data Objects x.x Object Library.
Use ADO if you can choose between ADO and DAO for data import or export.

Sincerely,
Leith Ross

Hi Leith -

Sorry, I should have pointed out that I had already done that - the reference that I've established is "Microsoft ActiveX Data Objects 2.8 Library" - I was thinking it might be the wrong library, but I tried it at home and the code worked...strange...

Thanks!

Dan

I don't know if this rude or not, but...

BUMP!

Hello Dan,

No, it isn't rude. It maybe as you suspected that your machine at work may not fully support the ADO/SQL commands you have in your code. Since it works everywhere except work, I feel it is a good possibility. You didn't mention what you are at home and at work as far as Windows versions and Office versions. That may yield a clue.

Sincerely,
Leith Ross

Hi Leigh -

Thanks for the response - I'm pretty sure both my laptop at home and at work run XP and Excel 2003 - but it's not a huge deal, I figured out a different way to design my workbook for now.

I think I just need to find someone who is familiar with ODBC connections, so I can find out if that is the problem, and if it is, if there is a possibility of using a different connection.

Thanks again!

Dan