Re: Cell Values from a Closed Workbook

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <twogilvy@xxxxxxx> wrote in message
news:%23EaZxE2NGHA.3408@xxxxxxxxxxxxxxxxxxxxxxx
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" <twogilvy@xxxxxxx> wrote in message
news:%23bnhh71NGHA.3944@xxxxxxxxxxxxxxxxxxxxxxx
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" <JK@xxxxxxxx> wrote in message news:nTPKf.3576$0z.2076@xxxxxxxxxxx
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








.

Relevant Pages

  • Re: Sumproduct in VBA
    ... Thanks a mil Tom and Bob.... ... Yes I have included the string in double quotes. ... SkillLevel is a string, it needs to be in double quotes in the formula: ... empRange As Range) As Variant ...
    (microsoft.public.excel.programming)
  • Re: VERY simple question about "?"
    ... don't see the equivalence between a string delimiter, or a character that signals the beginning of a symbol, and a symbol that is actually productive of something. ... Part of my difficulty understanding you is probably caused by the fact that you seem to try to tackle problems of computer languages with tools from a complete different domain. ... "Tom" - my name can also be called, but when one does so IT doesn't spring into action at all. ...
    (comp.lang.ruby)
  • RE: VBA and SQL dump changing the cell types in other worksheets
    ... Tom thanks very much your suggestion worked. ... Dim cnAssyst_Dev As ADODB.Connection ... Dim strConn As String ... 'Get the start and end dates from Sheet 1 ...
    (microsoft.public.excel.programming)
  • Re: using VLookUp in a VBA program
    ... > I've tried the following as suggested by Tom> ... I would have thought that I am suppose to> include the reference from VBE so that my program can reference the> Analysis Tool, but there are so many packages i am not sure which to> include for the worksheet function. ... >>> Dim WorkSheet As String ...
    (microsoft.public.excel.programming)
  • Re: Sumproduct in VBA
    ... Yes I have included the string in double quotes. ... Tom, I have made the adjustments as per your suggestions. ... SkillLevel is a string, it needs to be in double quotes in the formula: ... empRange As Range) As Variant ...
    (microsoft.public.excel.programming)