go ahead and email what you have

--


Gary


"Ruckus" wrote in
message ...

Gary & Dave,

Thx so much for your responces, links & ideas.

Sounds like it could work, but its to far over my head. I don't have a
clue how to incorporate this stuff.

Going back to changing hard code, when changing files.
Thx anyway


Dave Peterson Wrote:
I'd put each piece of information (workbook, worksheet, address) in
different
cells.

And then I could use some code from John Walkenbach to retrieve the
value:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

The code would look kind of like:

Option Explicit
Sub testme()

Dim myLocations As Range
Dim myCell As Range
Dim myValue As Variant

With Worksheets("sheetwithfilenames")
'headers in row 1???
Set myLocations = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myLocations.Cells
myValue = GetValue(ThisWorkbook.path, _
myCell.Value, _
myCell.Offset(0, 1).Value, _
myCell.Offset(0, 2).Value)

'do something with myvalue
MsgBox myValue

Next myCell

End Sub

'From John Walkenbach
Private Function GetValue(path, file, sheet, range_ref)

' Retrieves a value from a closed workbook

Dim arg As String

' Make sure the file exists

If Right(path, 1) < "\" Then path = path & "\"

If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(range_ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro

GetValue = ExecuteExcel4Macro(arg)

End Function

=======
Another option, if you know the whole path, workbook name, sheet name,
and cell
address is to just build the link:

='C:\my documents\excel\[book1.xls]Sheet1'!$A$1

If the path may change (you used thisworkbook.path), you could have a
macro
build that kind of formula in column D (say) based on what you typed in
columns
A, B, and C.




Ruckus wrote:

Hi,

I have a master workbook which opens several files and pulls data
out,
this all works fine.

The filenames it pulls from are hard coded into the macro. I need
these
to be flexable cause the filenames change ocasionally. Soo what i am
looking for is to add a sheet on my master workbook with names of
files. The macro will then target a cell for the filename to pull
data
from instead of hard coding.

IE: Instead of hardcoding "TestNumber1.xls" this filename will be in
a
cell on sheet2 of the MasterWorkBook, something like
"MasterWorkBook/Sheet2/A1"

Anyone have any clues on how to do this? Or a better way?
Code:
--------------------
'This is copying workbook # 1
Workbooks.Open ThisWorkbook.Path & "\TestNumber1.xls"
LastRow = Range("A65536").End(xlUp).Row
Range("A" & LastRow).EntireRow.Copy
Windows("MasterWorkBook.xls").Activate
Range("A4").Select
ActiveSheet.Paste
Windows("TestNumber1.xls").Activate
Application.CutCopyMode = False
ActiveWindow.Close
--------------------

--
Ruckus

------------------------------------------------------------------------
Ruckus's Profile:
http://www.excelforum.com/member.php...o&userid=27130
View this thread:
http://www.excelforum.com/showthread...hreadid=466463

--

Dave Peterson


--
Ruckus
------------------------------------------------------------------------
Ruckus's Profile:
http://www.excelforum.com/member.php...o&userid=27130
View this thread: http://www.excelforum.com/showthread...hreadid=466463