Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    37

    Red face Unanswered: Getting Values from Closed Books

    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

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    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
    ~

    Bill

  3. #3
    Join Date
    Feb 2006
    Posts
    113
    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

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    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
    ~

    Bill

  5. #5
    Join Date
    Feb 2006
    Posts
    113
    Thanks, Bill.

    It is the programmatic reference setting that particularly interests me.

    Maybe a google search if noone responds here.

    regards,
    Fazza

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •