Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with a memory
Hi. I am currently downloading a report every day and updating a
spreadsheet with some numbers from that report. My spreadsheet keeps a history of those numbers for a months time to produce graphs and such. My report comes in an Excel format and has a date/timestamp so identification of what date the report corresponds is possible. Can I make a macro that looks a report dated 10/23/2009 and not delete/ replace the data from 10/01/2009 to 10/22/2009? Hope that is clear enough. Furthermore, if possible, I would like to use this macro to look at a months worth of data at a time. Again, my downloaded report would have a date stamp (something like "09/01/2009 - 09/30/2009"). I believe I could write the code for this task on my own, but creating the logic to do this and the task above is stumping me. Lastly... best VBA book for beginners? I have some programing experience (MATLAB), but I still need to learn the VBA language. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with a memory
the first question I have to ask is the number of rows of data the same every time you download? I suspect you are having problems deleting data because formulas and charts are ending up with wrong references. I think your data would be better organized if the data was in chronological order. If the rows are the same, then moving data rather than copying dat might work OldRow = 10 for NewRow = 1 to Lastrow Range("A" & NewRow) = Range("A" & OldRow) OldRow = OldRow + 1 next Newrow Then add the new data into the last rows. Other solutions are 1) Use Named Ranges in the formulas and charts in you workbook which can be accessed both by the worksheet functions and VBA. Have you macro updated the named ranges so the formulas and charts will get the correct data. 2) Using clearcontents method in VBA will not corrupt formulas or charts if new data is put into the same locations. 3) Have the VBA code create the formulas and charts so yo don't have to preserve the same range of data each time you run the macro. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=146771 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with a memory
Johathan,
The macro you're looking for can almost without doubt be created. You just haven't given enough detail at this point to really begin doing that. If you want, you can contact me via email at (remove spaces) Help From @ JLathamsite .com and I'll see what help I can give you. Eventually I'd need copies of some of the report files and the spreadsheet that gets updated from it. As for VBA book(s) for beginners, take a look at this page over at Contextures: http://www.contextures.com/xlbooks.html one that I've looked at that's listed on that page is Microsoft Excel VBA Programming for the Absolute Beginner, 3E; Duane Birnbaum; 544 pages; ISBN: 1598633945; $29.99 and I would recommend it as one that does as it says: teaches from the ground up. In the meantime, while selecting your book(s), you might get a leg up on it all with a booklet I put together as an introduction to VBA programming in Excel: http://www.jlathamsite.com/Teach/VBA...troduction.pdf There's a link to it and some almost trivial associated "project" workbooks on this page: http://www.jlathamsite.com/LearningPage.htm "Jonathan" wrote: Hi. I am currently downloading a report every day and updating a spreadsheet with some numbers from that report. My spreadsheet keeps a history of those numbers for a months time to produce graphs and such. My report comes in an Excel format and has a date/timestamp so identification of what date the report corresponds is possible. Can I make a macro that looks a report dated 10/23/2009 and not delete/ replace the data from 10/01/2009 to 10/22/2009? Hope that is clear enough. Furthermore, if possible, I would like to use this macro to look at a months worth of data at a time. Again, my downloaded report would have a date stamp (something like "09/01/2009 - 09/30/2009"). I believe I could write the code for this task on my own, but creating the logic to do this and the task above is stumping me. Lastly... best VBA book for beginners? I have some programing experience (MATLAB), but I still need to learn the VBA language. Thanks! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro with memory | Excel Programming | |||
Macro Hogging Memory - what to do? | Excel Programming | |||
Crashing Macro, Not Enough Memory | Excel Programming | |||
XP running out of memory when using macro | Excel Programming | |||
Not enough Memory running macro | Excel Programming |