Cell Values from a Closed Workbook

[JK]

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

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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

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

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

 

[JK]

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

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

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

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

 

[paxdak]

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?