Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a workbook in the same directory
I'm hoping this is an easy question. Each month I run 5 reports that are
saved as excel workbooks. I have a macro template that combines and analyzes the data from the 5 reports. Each time I do this I copy and paste the macro template into a new folder and save the current 5 reports in this new folder. Each of the 5 reports is saved with the exact same name. What I want to do is change the formulas in my macro so they reference the five reports I run without having to copy and paste them into the macro template workbook. Is it possible to reference worksheets saved in the current directory by not using the full path name (because the folder this is stored in changes every month). I know its possible in html so I'm hoping its as easy in VBA. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a workbook in the same directory
I am not too sure that I completely follow you but you can use
ThisWorkbook.Path to get the path of the workbook executing the code... -- HTH... Jim Thomlinson "Still Learning" wrote: I'm hoping this is an easy question. Each month I run 5 reports that are saved as excel workbooks. I have a macro template that combines and analyzes the data from the 5 reports. Each time I do this I copy and paste the macro template into a new folder and save the current 5 reports in this new folder. Each of the 5 reports is saved with the exact same name. What I want to do is change the formulas in my macro so they reference the five reports I run without having to copy and paste them into the macro template workbook. Is it possible to reference worksheets saved in the current directory by not using the full path name (because the folder this is stored in changes every month). I know its possible in html so I'm hoping its as easy in VBA. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a workbook in the same directory
Since you just want the report data, use workbooks.add and place the report
in a new workbook. Dim Wb1 As Workbook Set Wb1 = ThisWorkbook Workbooks.Add Dim Wb2 As workbook Set Wb2 = ActiveWorkbook Wb1.Sheets("Your Sheet").Range("YourRange").Copy Destination:=Wb2.Sheets1.Range("Your range") or use this if your YourRange contains formulas. Wb1.Sheets("Your Sheet").Range("YourRange")Copy Wb2.Sheet1.Range("YourRange").PasteSpecial xlvalues Wb2.Sheet1.Range("YourRange").PasteSpecial xlformats Then you would close with, Wb1.Close False Leaving the report open to save or you can set up automatic saving by name if you set up the criteria to do so. Regards, Alan "Still Learning" wrote in message ... I'm hoping this is an easy question. Each month I run 5 reports that are saved as excel workbooks. I have a macro template that combines and analyzes the data from the 5 reports. Each time I do this I copy and paste the macro template into a new folder and save the current 5 reports in this new folder. Each of the 5 reports is saved with the exact same name. What I want to do is change the formulas in my macro so they reference the five reports I run without having to copy and paste them into the macro template workbook. Is it possible to reference worksheets saved in the current directory by not using the full path name (because the folder this is stored in changes every month). I know its possible in html so I'm hoping its as easy in VBA. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Links - Copy a Workbook to another Directory | Excel Discussion (Misc queries) | |||
copying worksheets to a new workbook without formulae referencing original workbook | Excel Programming | |||
creating directory for workbook | Excel Discussion (Misc queries) | |||
Get the path to the directory that the active workbook is in | Excel Programming | |||
opening a workbook in the current directory | Excel Programming |