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

    Unanswered: Getting data from closed books with VBA

    This one may be a bit tricky, but I am hoping someone will know the answer.
    I have used this Function that I got from a book to retrive values from closed books with VBA

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

    It is basically building the formula for a lined cell, but when getting values in VBA it saves me having to open the book get the value and then close the book again. I would use a procedure like the one below to get it to pass back the value

    sub GetData
    dim dummy
    dummy = getvalue("C:\work\","Accounts.xls","2005","A1")
    end sub

    What I would like to be able to do is pass it the row and column numbers rather than "A1"

    Hence dummy = getvalue("C:\work\","Accounts.xls","2005",1,1)
    would give me the same answer

    Does anyone know what the "& Range(ref).Address(, , xlR1C1)" part of the function would need to be in order to accomplish this? Any help would be very much appreciated.

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    or you could change
    Function GetValue(Path, File, Sheet, ref)

    to Function GetValue(Path, File, Sheet, refRow, RefCol)

    Then Change Range(ref).Address(, , xlR1C1) to
    cells(refRow,RefCol).Address(, , xlR1C1)

    Is that what your after

    Dave

  3. #3
    Join Date
    Dec 2004
    Posts
    37
    Many thanks David.

    This little tip has saved me hours/days of trial and error

    Michael

  4. #4
    Join Date
    Dec 2004
    Posts
    37

    A bit further for ExecuteExcel4Macro(arg)

    Hi, I have used this for a while now and found it very useful.

    Is it possible to take it a step further.

    I have a sheet that is corrupted and this has enabled me to get the data from the sheet, however some of the data is calculated and to be able to also recover the formulas would be even better.

    Is there a method of using this to get the R1C1 formula from a closed book rather than the values?

    Regards
    Mike

Posting Permissions

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