Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range used in a macro needs to be variable | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Forcing a macro to pause for filename | Excel Discussion (Misc queries) | |||
open file (as variable) from macro | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |