Updating master workbook from source that may/may not exist

wrote in message
oups.com...
I don't know if this sheds any light on any of this but the error I
told you about I got while debugging. When I try to run the function
without debugging I get this error returned:

Method 'ExecuteExcel4Macro' of object '_Global' failed

Not sure why the error is different depending on if I'm debugging or
just executing the function. Maybe this helps???


Maybe. I shall try to make a small macro to do something similar. I have
made a macro for a friend where I use the Evaluate function instead. It
works well but the sheets have to be open for it to work. I now feel I have
some more time since I now have a new job. It's so nice not being
unemployed.

/Fredrik

Thanks - I appreciate all your help. As for the "being unemployed" I
understand. Been there, done that and I agree - not as nice as being
employed.

I haven't followed this thread too closely, but that UDF based on John
Walkenbach's code won't work if it's called by a formula in a worksheet.

But you may want to look at how Harlan Grove worked around using =indirect()
with closed workbooks.

http://www.google.com/groups?selm=sH...wsranger. com



Fredrik Wahlgren wrote:

wrote in message
oups.com...
I don't know if this sheds any light on any of this but the error I
told you about I got while debugging. When I try to run the function
without debugging I get this error returned:

Method 'ExecuteExcel4Macro' of object '_Global' failed

Not sure why the error is different depending on if I'm debugging or
just executing the function. Maybe this helps???


Maybe. I shall try to make a small macro to do something similar. I have
made a macro for a friend where I use the Evaluate function instead. It
works well but the sheets have to be open for it to work. I now feel I have
some more time since I now have a new job. It's so nice not being
unemployed.

/Fredrik

--

Dave Peterson

I thought this function you pointed me to would work but I'm still
having problems. I have my function set almost exactly as it is in the
post. I had to change the comparison to ErrRef to ErrValue and it
executed the rest of the code. Unfortunately when it came to the "For
Each c In r" the value being returned by xlapp.ExecuteExcel4Macro is
"empty" and I'm not getting my value.

This is what I have:

Function pull(xref As String) As Variant

Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, c As Range, n As Long, arg As String

pull = Evaluate(xref)

If CStr(pull) = CStr(CVErr(xlErrValue)) Then
On Error GoTo CleanUp

Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add

On Error Resume Next

n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)
Else
For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c
pull = r.Value
End If

CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End If
End Function


And I call the function in my cell with this:

=pull(""&F1&"\["&C1&"]"&D1&"'!D10")

where F1 references the path, c1 references the file and d1 references
the sheet name. Any reason this returns "empty"?

Thanks.

And as a follow-up this new function does work when I call it from a
procedure (as did the other function). Neither function works when
called from a cell in a workbook.

wrote in message
ups.com...
And as a follow-up this new function does work when I call it from a
procedure (as did the other function). Neither function works when
called from a cell in a workbook.

If the same string is evaluated by ExecuteExcel4Macro, that settles it. It
seems as if this function only works when called from a procedure

/Fredrik

That function works ok for me when called from a worksheet cell.

What did your formula look like?

" wrote:

I thought this function you pointed me to would work but I'm still
having problems. I have my function set almost exactly as it is in the
post. I had to change the comparison to ErrRef to ErrValue and it
executed the rest of the code. Unfortunately when it came to the "For
Each c In r" the value being returned by xlapp.ExecuteExcel4Macro is
"empty" and I'm not getting my value.

This is what I have:

Function pull(xref As String) As Variant

Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, c As Range, n As Long, arg As String

pull = Evaluate(xref)

If CStr(pull) = CStr(CVErr(xlErrValue)) Then
On Error GoTo CleanUp

Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add

On Error Resume Next

n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)
Else
For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c
pull = r.Value
End If

CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End If
End Function

And I call the function in my cell with this:

=pull(""&F1&"\["&C1&"]"&D1&"'!D10")

where F1 references the path, c1 references the file and d1 references
the sheet name. Any reason this returns "empty"?

Thanks.

--

Dave Peterson

"Dave Peterson" wrote in message
...
That function works ok for me when called from a worksheet cell.

What did your formula look like?

I have missed something. This has been a very long thread.

7Fredrik

I had suggested that Harlan Grove's =pull() UDF works from a cell on a
worksheet.
http://www.google.com/groups?selm=sH...wsranger. com

The OP said it didn't work for him.

Fredrik Wahlgren wrote:

"Dave Peterson" wrote in message
...
That function works ok for me when called from a worksheet cell.

What did your formula look like?

I have missed something. This has been a very long thread.

7Fredrik

--

Dave Peterson

Okay - I think I finally got it to work. It actually was something
simple - I had double quotation marks around the parameter being passed
to the pull function - which is wrong. It should be a single quotation
mark at the beginning around the path, file name and sheet name, ending
before the ! and cell reference. Once I took out the double quotation
marks it works fine. I misread it on the previous post.

I just have one other question - in Harlan Groves function he is
checking for the cell to have a #REF error in it before pulling the
value from another workbook. Any reason to do this? This was also
giving me some problems at first - sometimes I would have a #REF error
and sometimes I would have a #VALUE error. Any reason to have to check
for these errors at all?

Once agin, thanks to all who have helped me with this.