Cell Values from a Closed Workbook

Thanks to JW I was able to implement this procedure in increments but unable
to to make it work in one fluid motion.

I would like my users to select a closed file, then on an active sheet
automatically import cell values from the closed sheet to cells on the
active sheet.

If you have a procedure it would be most appreciated. TIA

James R Kobzeff

I don't know what JW showed you, but the fastest way is to enter a formula
into those cells that link to the closed workbook. Then you can replace the
formulas with the value they retrieve if so inclined.

Sub GetValues()
Range("A1:A100").Formula = "='C:\My Folder\[My File.xls]Sheet1'!A1"
' optional
Range("A1:A100").Formula = Range("A1:A100").Value
End Sub


--
Regards,
Tom Ogilvy


"JK" <[email protected]> wrote in message news:nTPKf.3576$0z.2076@trnddc01...
> Thanks to JW I was able to implement this procedure in increments but
unable
> to to make it work in one fluid motion.
>
> I would like my users to select a closed file, then on an active sheet
> automatically import cell values from the closed sheet to cells on the
> active sheet.
>
> If you have a procedure it would be most appreciated. TIA
>
> James R Kobzeff
>
>

Let's see, you wanted it combined with a file selection dialog.

Sub GetValues()
Dim fName As String, sForm as String
Dim fName1 as String, sPath as String
Dim v as Variant
fName = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.xls),*.xls")
If fName <> "False" Then
v = Split(fName, "\")
fName1 = v(UBound(v))
sPath = Left(fName, Len(fName) - Len(fName1))
sForm = "='" & sPath & "[" & fName1 & "]Sheet1'!A1"
Range("F101:F200").Formula = sForm
End If
End Sub

Requires xl2000 or later. Obviously change F101:F200 and other parts of
the formula to match what you want to do.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> I don't know what JW showed you, but the fastest way is to enter a formula
> into those cells that link to the closed workbook. Then you can replace
the
> formulas with the value they retrieve if so inclined.
>
> Sub GetValues()
> Range("A1:A100").Formula = "='C:\My Folder\[My File.xls]Sheet1'!A1"
> ' optional
> Range("A1:A100").Formula = Range("A1:A100").Value
> End Sub
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "JK" <[email protected]> wrote in message news:nTPKf.3576$0z.2076@trnddc01...
> > Thanks to JW I was able to implement this procedure in increments but
> unable
> > to to make it work in one fluid motion.
> >
> > I would like my users to select a closed file, then on an active sheet
> > automatically import cell values from the closed sheet to cells on the
> > active sheet.
> >
> > If you have a procedure it would be most appreciated. TIA
> >
> > James R Kobzeff
> >
> >
>
>

Thank you, Tom. GetValues is what JW provided. But I need a procedure that
works with xl97 and newer (GetOpenFileName doesn't?). Perhaps you can
suggest a procedure that will work? You are correct about what I'm trying to
do. Have the user select an xls file from a specific folder (c:/Program
Files/Program Folder/), than have my program get values from that file and
write them to the active worksheet. Thank you.

Jim Kobzeff


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> Let's see, you wanted it combined with a file selection dialog.
>
> Sub GetValues()
> Dim fName As String, sForm as String
> Dim fName1 as String, sPath as String
> Dim v as Variant
> fName = Application.GetOpenFilename( _
> FileFilter:="Excel Files (*.xls),*.xls")
> If fName <> "False" Then
> v = Split(fName, "\")
> fName1 = v(UBound(v))
> sPath = Left(fName, Len(fName) - Len(fName1))
> sForm = "='" & sPath & "[" & fName1 & "]Sheet1'!A1"
> Range("F101:F200").Formula = sForm
> End If
> End Sub
>
> Requires xl2000 or later. Obviously change F101:F200 and other parts of
> the formula to match what you want to do.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:%[email protected]...
>> I don't know what JW showed you, but the fastest way is to enter a
>> formula
>> into those cells that link to the closed workbook. Then you can replace
> the
>> formulas with the value they retrieve if so inclined.
>>
>> Sub GetValues()
>> Range("A1:A100").Formula = "='C:\My Folder\[My File.xls]Sheet1'!A1"
>> ' optional
>> Range("A1:A100").Formula = Range("A1:A100").Value
>> End Sub
>>
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "JK" <[email protected]> wrote in message news:nTPKf.3576$0z.2076@trnddc01...
>> > Thanks to JW I was able to implement this procedure in increments but
>> unable
>> > to to make it work in one fluid motion.
>> >
>> > I would like my users to select a closed file, then on an active sheet
>> > automatically import cell values from the closed sheet to cells on the
>> > active sheet.
>> >
>> > If you have a procedure it would be most appreciated. TIA
>> >
>> > James R Kobzeff
>> >
>> >
>>
>>
>
>

I'm looking for a similar solution except with a User Defined function.

I'd like to be able to build a UDF that pulls the value from a closed
workbook
based on user inputs. I can get VBA to determine what data to pull
(i.e build a string that references the data:
"='C:\My Folder\[My File.xls]Sheet1'!A1"),
but I can't figure out how to get the value on a worksheet.

I tried the solution provided on John Walkenbach's site
(' Execute an XLM macro via GetValue = ExecuteExcel4Macro(arg))
http://www.j-walk.com/ss/excel/tips/tip82.htm,
but it does not function in a worksheet formula.

It be nice to be able to enter the following in a cell:
=get_Value(path, file, sheet, cellref)
and get the value returned.

Is there any workaround?

I'd like a User Defined Function solution so that I can call the values
where ever I need them in a worksheet. The macro approach of
creating the string, setting a cell formula to the string, then pasting
the value doesn't allow much flexibility in pulling the data - the
desired data varies to much, plus any updates to the source files
would require the macro to be re-run (not a true link)

Any suggestions?