Values from closed workbook results in Empty

[bradgar]

I am trying to have a macro retrieve information form a closed workbook,
however when i run the function and/or sub it returns #ref! in the cell.

The cell(s) i am calling have information in them, but i still get this error.

any help would be greatly appreciated.

Private Function GetValue(path, file, sheet, ref)

'   Retrieves a value from a closed workbook
    Dim arg As String

'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"

    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If

'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)
 
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function
 
Sub PullInSheet1a()

    p = "correct path" 'this is correct
    f = "correct wrkbk" 'this is correct
    s = "Sheet1" 'this is correct
    a = "G5" 'correct

    ActiveSheet.Range("E20").Value = GetValue(p, f, s, a) 'returns empty?

end sub

 

[p45cal]

Well I got it to work here.
What do you end up with in the cell?
And what is actually in the cell you get the data from?
If you step through the code and watch the value of GetValue in the VBE locals pane (Alt+V,s f you cant see it), what is the value shwing for it just ater
GetValue = ExecuteExcel4Macro(arg)
has been executed?

 

[bradgar]

I end up with #REF! in the cell.
The cell(s) have simple stings in them, one is a date, not function just typed in, another is a text string.

i don't know how to step through...

 

[bradgar]

actually i just stepped through and it is what I thought, it ends up with an empty string, the information before it is all correct, (it is calling the right path, file, sheet, and ref, but it doesn't return the value in the ref cell...)

 

[bradgar]

hmmm,

for some reason when i step through it goes to worksheet change function... this may be the reason, because there has been change in worksheet it leaves sub & function then changes the values of my sub... is that possible?

*actually it gives me error 2023 upon further investigation

 

[Joe4]

Can you post the actual values you are using for p, f, s, and a?
Maybe we will see something that sticks out.
Also, though it shouldn't make any difference, try running it with that other workbook open and see if it makes any difference.

 

[Joe4]

for some reason when i step through it goes to worksheet change function... this may be the reason, because there has been change in worksheet it leaves sub & function then changes the values of my sub... is that possible?

Are you saying that you have other VBA code coming in to play here too, like a Worksheet_Change macro?
What does that code do?

 

[bradgar]

i tried using the sheet tab name instead of the Sheet codename, however no matter what I keep getting the return #ref! in the cell i call the funtion too, this has nothing to do with the worksheet_change event because this is activating because of the #ref! in the cell the getvalue is supposed to put it's value into.

When i follow it in locals pane, getvalue starts as empty, then when i get to end sub line it returns error 2023, any idea why?

 

[Joe4]

Please answer all the questions I asked in my last two posts and I will try to help you out.

 

[p45cal]

I think you've got the sheet name wrong. It worked here using the sheet's tab name. Check it's exactly the same, no leading or trailing spaces, and usually, no spaces within sheet names such as Sheet1.
Best, double click the actual sheet name on the tab, press Ctrl+c to copy the name, click somewhere off the tab so you don't accidentally edit it, then paste to:

s = "" 'this is correct

between the double quote marks.
Then try running again.