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

Cell Values from a Closed Workbook
 

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" 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

Cell Values from a Closed Workbook
 

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" wrote in message
...
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" 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

Cell Values from a Closed Workbook
 

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" wrote in message
...
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" wrote in message
...
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" 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

Cell Values from a Closed Workbook
 

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?