Calling variable files
 

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

Calling variable files
 

i have done exactly that, not sure if there was a better way, but it worked
for me.

on a hidden sheet called tellers, i have the teller's first name in a1 thru
a20
in b1 thru b20, i built the complete file name with this formula
="["&A1&".xls]"

then in my code , i access the filename to bring in whichever cell i need to
fill in the report sheet

Dim FileName$(1) ==========some of these dims are for other
areas===========
Dim fPath As String
Dim i As Integer
Dim Fname As String
Dim LastRow As Long
Dim rNum As Long
Sub March_Summary()

'====here i use the lastrow of teller names on the teller sheet in
b2:b20=========
For i = 2 To LastRow
Fname = Worksheets("tellers").Cells(i, "B").Value
'==it cycles through all the teller names (filenames) from the last row to
row 2, i use an explicit reference for row 1 elsewhere in my code
'Main
rNum = 4 ' this is the row number that the data i want is in

'====this is what goes out and brings the values from the trans and hours
sheets into the monthly sheet
Range("B4") = Range("B4") & "+'" & fPath & Fname & "Trans'!$M" & rNum
Range("C4") = Range("C4") & "+'" & fPath & Fname & "Hours'!$M" & rNum

this gives me 20 formulas in each of 2 cells, so i can add up the
transactions for the 20 tellers in b4 and the hours for the 20 tellers in


if it's hard to follow, just post back and i'll try to explain
--


Gary


"Ruckus" wrote in
message ...

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

Calling variable files
 

Hi Gary,
Thx a lot for responding. I'm trying to figure this out. I will ge
back to you.


Gary Keramidas Wrote:
i have done exactly that, not sure if there was a better way, but i
worked
for me.

on a hidden sheet called tellers, i have the teller's first name in a
thru
a20
in b1 thru b20, i built the complete file name with this formula
="["&A1&".xls]"

then in my code , i access the filename to bring in whichever cell
need to
fill in the report sheet

Dim FileName$(1) ==========some of these dims are for other
areas===========
Dim fPath As String
Dim i As Integer
Dim Fname As String
Dim LastRow As Long
Dim rNum As Long
Sub March_Summary()

'====here i use the lastrow of teller names on the teller sheet in
b2:b20=========
For i = 2 To LastRow
Fname = Worksheets("tellers").Cells(i, "B").Value
'==it cycles through all the teller names (filenames) from the last ro
to
row 2, i use an explicit reference for row 1 elsewhere in my code
'Main
rNum = 4 ' this is the row number that the data i want is in

'====this is what goes out and brings the values from the trans an
hours
sheets into the monthly sheet
Range("B4") = Range("B4") & "+'" & fPath & Fname & "Trans'!$M" & rNum
Range("C4") = Range("C4") & "+'" & fPath & Fname & "Hours'!$M" & rNum

this gives me 20 formulas in each of 2 cells, so i can add up the
transactions for the 20 tellers in b4 and the hours for the 20 teller
in


if it's hard to follow, just post back and i'll try to explain
--


Gary


"Ruckus" wrot
in
message ...

Hi,

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

The filenames it pulls from are hard coded into the macro. I nee
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 pul
data
from instead of hard coding.

IE: Instead of hardcoding "TestNumber1.xls" this filename will be i
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


--
Rucku
-----------------------------------------------------------------------
Ruckus's Profile: http://www.excelforum.com/member.php...fo&userid=2713
View this thread: http://www.excelforum.com/showthread.php?threadid=46646

Calling variable files
 

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

Calling variable files
 

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
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) i
different
cells.

And then I could use some code from John Walkenbach to retrieve th
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
macro
build that kind of formula in column D (say) based on what you typed i
columns
A, B, and C.




Ruckus wrote:

Hi,

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

The filenames it pulls from are hard coded into the macro. I nee
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 pul
data
from instead of hard coding.

IE: Instead of hardcoding "TestNumber1.xls" this filename will be i
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 Peterso

--
Rucku
-----------------------------------------------------------------------
Ruckus's Profile: http://www.excelforum.com/member.php...fo&userid=2713
View this thread: http://www.excelforum.com/showthread.php?threadid=46646

Calling variable files
 

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