Put Filename Variable in Macro
 

I have a macro that stores the full path/filename of a file that is needed in
another macro to cell B8 on a sheet named Index. This sets the file location
which can vary by location.
Another macro uses this file to post data to from the current workbook. I'm
having trouble getting the macro to use the filename variable stored in cell
B8.
Can you help? Here is the code that's in question:

Dim wbSum As Workbook, wbData As Workbook
Dim sht
Dim FName As Variant
FName = Worksheets("Index").Range("B8")
Set wbSum = FName <==Here's the problem line!
Set wbData = ActiveWorkbook
Dim iDate As Date, iValue
Dim lastcol As Long, xV As Long, xR As Long, xC As Long
Workbooks.Open (Filename)

Put Filename Variable in Macro
 

Hi David,

Try changing:

Set wbSum = FName

to:

Set wbSum = Workbooks(FName)


---
Regards,
Norman



"David" wrote in message
...
I have a macro that stores the full path/filename of a file that is needed
in
another macro to cell B8 on a sheet named Index. This sets the file
location
which can vary by location.
Another macro uses this file to post data to from the current workbook.
I'm
having trouble getting the macro to use the filename variable stored in
cell
B8.
Can you help? Here is the code that's in question:

Dim wbSum As Workbook, wbData As Workbook
Dim sht
Dim FName As Variant
FName = Worksheets("Index").Range("B8")
Set wbSum = FName <==Here's the problem line!
Set wbData = ActiveWorkbook
Dim iDate As Date, iValue
Dim lastcol As Long, xV As Long, xR As Long, xC As Long
Workbooks.Open (Filename)

Put Filename Variable in Macro
 

David,
The Set statement makes assignments to objects. Youf FName variable is
not an object. The method you are seeking is the Open method of the
Workbooks Collection object. Replace with:
Workbooks.Open(FName)

When opened it becomes the ActiveWorkbook so the following line should
be fine if you mean to work with the recently opened file.

HTH
Kostis Vezerides

Put Filename Variable in Macro
 

Instead of
Set wbSum = FName
you need
Set wbSum = Workbooks.Open (FName)

Also, the line
Workbooks.Open (Filename)
seems wrong, as you haven't declared or initialised Filename
(typo for FName?). You _are_ using Option Explicit, aren't you...?

Andrew Taylor


David wrote:
I have a macro that stores the full path/filename of a file that is needed in
another macro to cell B8 on a sheet named Index. This sets the file location
which can vary by location.
Another macro uses this file to post data to from the current workbook. I'm
having trouble getting the macro to use the filename variable stored in cell
B8.
Can you help? Here is the code that's in question:

Dim wbSum As Workbook, wbData As Workbook
Dim sht
Dim FName As Variant
FName = Worksheets("Index").Range("B8")
Set wbSum = FName <==Here's the problem line!
Set wbData = ActiveWorkbook
Dim iDate As Date, iValue
Dim lastcol As Long, xV As Long, xR As Long, xC As Long
Workbooks.Open (Filename)

Put Filename Variable in Macro
 

Try changing this line
FName = Worksheets("Index").Range("B8")

to this:
FName = Worksheets("Index").Range("B8").Value

As a test you can add this line and run the code before and after the
suggested change:
FName = Worksheets("Index").Range("B8")
Msgbox FName

You should see the expected results.