Dynamically increment date for data import from another spreadshee

[jjmancini]

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!

 

[Geoff]

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

 

[jjmancini]

Thanks Geoff!

I tried to implement this code, but frankly I am having trouble setting up a
macro, I have never done it before.

Would you be interested in helping me out?
It seems you know excel pretty darn good.

Thanks so much!

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.

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!

 

[jjmancini]

I ran into another problem, I am using Excel for Mac 2008, I have a windows
machine, but prefer not to use it.

Is there any way to do this without using macros?
Thanks!

 

[Geoff]

VBA is definitely the simplest way to do this - it may be possible to do
something using the INDIRECT function without using macros, but this requires
that the source workbooks be open, so probably not much help. I found a link
that may help you decide the best route to follow (someone basically with the
same problem):
http://www.eggheadcafe.com/software/aspnet/30689973/using-a-cell-reference-is.aspx

Otherwise, the easiest way I can think of to approach this is to copy the
formula across the range of cells that you want it in (so they are all
looking at the same column in the same file), and then to alter the formulae
manually, changing the names of the files referenced one by one. If you're
looking at a lot of workbooks then that's going to get old pretty quick, but
still (slightly) easier than opening the files manually.
So, for instance, change
=SUM('C:\Program Files\Petro Vend\Phoenix\Data\Daily Transaction
Reports\2008\05 May\[050508.csv]'!$J:$J)/2

to
=SUM('C:\Program Files\Petro Vend\Phoenix\Data\Daily Transaction
Reports\2007\04 Dec\[120407.csv]'!$J:$J)/2

for each of the references you need.

HTH