Untested code:

Sub New_Formula(ByVal yr As Integer, mth As Integer, day As Integer)

Dim newDate As Date
Dim newFile As String
Dim newCell As Range
' newCell is the range where the formula should go...

newDate = DateSerial(yr, mth, day)
newFile = "C:\Program Files\Petro Vend\Phoenix\Data\Daily Transaction
Reports\"
newFile = newFile & yr & "\" & Format(newDate, "dd mmmm") & "\["
' Assuming that the "05 May" part of the filepath uses the full month name -
' if this is not the case use the "dd mmm" format instead of "dd mmmm"
newFile = newFile & Format(newDate, "mmddyy") & ".csv]"
' Assumes here you follow the strange American convention of placing the
' month before the day in your date...if not, use "ddmmyy"...

Set newCell = Range("A1")
' You need to change this to the relevant range...
newCell.FormulaR1C1 = "=SUM('" & newFile & "'$J:$J)/2"
' And you're done!

End Sub

--
There are 10 types of people in the world - those who understand binary and
those who don't.


"jjmancini" wrote:

Hey Everyone...

I have a question. I have about 5 years worth of files that I would like to
examine via EXCEL. There are all named in the following format
MMDDYY.csv

Without having to open each file and click on the column that I want to SUM,
how can I dynamically increment the date in my excel equation?

Here is the current equation:
=SUM('C:\Program Files\Petro Vend\Phoenix\Data\Daily Transaction
Reports\2008\05 May\[050508.csv]'!$J:$J)/2

Any information would be GREATLY appreciated!!! Thanks!