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)

Hi David,

Try changing:

> Set wbSum = FName

to:

Set wbSum = Workbooks(FName)


---
Regards,
Norman



"David" <(E-Mail Removed)> wrote in message
news:27AA7DEA-EFC6-44AA-8BF7-(E-Mail Removed)...
>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)
>

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

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)

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.