Referencing a workbook in the same directory

[Guest]

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.

 

[Guest]

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...

 

[Alan]

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