The function there includes an extra step to check that the file exists.
There are caveats at the end - a worksheet must be active in Excel; there will be an error if all windows are hidden, or if the active sheet is a chart sheet.
I'm unclear about setting references for ADO.
Can this be done via Excel VBA (Excel 2003? What about Excel 2000? Or 2002?). If so, how. If not, how to overcome the need to set references in a distributed workbook (when receiving users do not have references set)?
Can ADO be used for DELETE queries to other files? Excel and Access?
It would help me if I could programmatically set the reference to ADO and then use ADO to update a remote database using SQL DELETE commands.
I'm using the 'ActiveX Data Objects 2.5 Library' which shows up in the default reference list Excel 2002 SP2. I just set the reference and save the code file. Once save it retains the reference as long as the user environment is the same (same dll path) I havn't attemted to set the reference progamatically although I'm sure there's a way to do this.
Once the reference is set you have access to all the functions through MS Jet database. Select/Update/Delete providing you have permissions on the file your accessing.
The Connection string is slightly different depending on if your accessing a .txt/.mdb/.xls file. this takes some palying around to get right.
I'm working on a Class Module to manage ADO DB access. (An db abstration class) I have not found very much information on class modules in Excel VB but what I've created so far is interesting.
Being able to check/set the reference with the class module would be very helpful.
ADO Reference I'm using (path may very) find the reference in the ref list you shouldn't have to browse to the path.
C:\Program Files\Common Files\System\ado\msado15.dll
Microsoft ActiveX Data Objects 2.5 Library