Hi,

I used a function many moons ago that worked, but now I cannot get it to work again and was hoping someone could give me clue why?
The Function is short and shown below:

Function GetValue2(Path, File, Sheet, ref)
Dim Arg As String
Arg = "'" & Path & "[" & File & "]" & Sheet & "'!" & Range(ref).Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(Arg)
End Function

I would pass
Path - H:\
File - Accounts2005
Sheet - 2005
ref - A1

and it would give me the value in A1 of the relevant sheet.

I am using Excel 2003. Will I need to have a reference added in the VBA Project to get this run?

Any help would be much appreciated
Mike

GetValue = ExecuteExcel4Macro(Arg)

This line is the key. It is calling a third process which does the actual data import from the closed file.

The best way of doing this IMO is with ADO

ExcelADO demonstrates how to use ADO to read and write data in Excel workbooks

Mike,

Try http://www.j-walk.com/ss/excel/tips/tip82.htm

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.

HTH.


Bill,

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.

regards,
Fazza

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

Thanks, Bill.

It is the programmatic reference setting that particularly interests me.

Maybe a google search if noone responds here.

regards,
Fazza