How to pull data from another workbook automatically?

[sburns]

I am trying to figure out how to make a workbook pull data from different workbooks in a shared folder under a particular year. The workbooks are saved based on a date name (eg UP08-0107 that is Jan 07, 08) - this would be in the 2008 folder of the shared file.

So, I am trying to make this excel sheet where I can type in different dates, in the format mentioned above, and have excel go to this shared folder, to this years folder, and look for a saved file with that specific date name and pull cell $F$51.

Is this possible?

 

[Smitty]

Welcome to the Board!

Here are references two methods from Debra Dalgliesh's site:

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip

• Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/
  It includes =indirect.ext() that may help you.

You can also see: http://www.erlandsendata.no/english/index.php?d=envbawbreadfromclosedwb

Hope that helps,

 

[sburns]

The links didn't really help, two of them didn't work and the third was a little confusing. I should probably explain a bit more... this question is probably out of my skill set. But it would be huge if I could do this as I have just started a new job and this was asked of me do try and do.

This is an example of the drive I want to pull from:
='S:\Workgroups\ICAP United OTC sheets\2008\[up08-0107.xls]UPI'!F51

1. I wanted the 2008 to be a variable.
2. I wanted the spreadsheet name to correlate with a date I enter. So, if I wanted July 2,2008; I would put that date in a cell and it would pull up08-0702.xls (using 08 from the 2008 cell mentioned above in #1.
3. I also wanted to have the cell reference as a variable.

I know this can be done and I want to learn how to do it. But I need a good teacher.

Thanks everyone.

 

[Smitty]

If the workbooks will be open you can use INDIRECT, but if there's closed you'll need to use one of the methods I referred you to. The easiest is probably INDIRECT.EXT from the Morefunc add-in.

You'll find Debra's tutorial on INDIRECT here:

http://www.contextures.com/xlFunctions05.html

 

[jefferyjames21]

Hello All,

I am basically trying to do the exact same thing that sburns was. I have tried to a function I found on a website someone wrote. It was called GetValue.

The GetValue Function<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The GetValue function, listed below takes four arguments:<o></o>
· path: The drive and path to the closed file (e.g., "d:\files")<o></o>
· file: The workbook name (e.g., "budget.xls")<o></o>
· sheet: The worksheet name (e.g., "Sheet1")<o></o>
· ref: The cell reference (e.g., "C4")<o></o>
Private Function GetValue(path, file, sheet, ref)<o></o>
' Retrieves a value from a closed workbook<o></o>
Dim arg As String<o></o>
' Make sure the file exists<o></o>
If Right(path, 1) <> "\" Then path = path & "\"<o></o>
If Dir(path & file) = "" Then<o></o>
GetValue = "File Not Found"<o></o>
Exit Function<o></o>
End If<o></o>
' Create the argument<o></o>
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _<o></o>
Range(ref).Range("A1").Address(, , xlR1C1)<o></o>
' Execute an XLM macro<o></o>
GetValue = ExecuteExcel4Macro(arg)<o></o>
End Function<o></o>
This function is not working for me for what every reason it always outputes file not found in the cell which i try to deploy it in. I tried the links in this thead and they both are to sites that are no longer availible. Would anybody be able to lend me some advice on this issue.

Thanks in advance.