Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically increment date for data import from another spreadshee
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically increment date for data import from another spreadshee
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically increment date for data import from another spread
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! "Geoff" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically increment date for data import from another spread
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically increment date for data import from another spread
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/...erence-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 -- There are 10 types of people in the world - those who understand binary and those who don't. "jjmancini" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a calendar in excel with the data from another spreadshee | Excel Discussion (Misc queries) | |||
Import External Data by Date | Excel Worksheet Functions | |||
Referencing tombstone text data to populate a cell in a spreadshee | Excel Programming | |||
how to dynamically import a text file | Excel Programming | |||
Import/Export Excel spreadshee data without MS Excel install | Excel Programming |