i've an stand out spreadsheet that's about 300,000 rows contributing to 100 posts
i have to perform various functions about this spreadsheet and using this spreadsheet i have to create about 3000 other excel spreadsheets that are Considerably more compact
for each produced spreadsheet i have to have another ms powerpoint file which will come with an instantly produced graph
i have done plenty of VBA programming, but i'm a little lost with this particular project
- basically dump the information right into a mysql file will it be simpler that i can handle my task?
- could it be achievable to get this done all in VBA stand out?
- can you really easily add graphs from stand out into ms powerpoint programmatically? or possibly must i make use of a different solution for graphs?
This is really achievable in most respects, but VBA might be an excessive amount of overhead with this due to it's heavy-handed character in frequent lowering and raising the Stand out and Ms powerpoint instances for 3000 excel spreadsheets and presentations. Whether it's a 1-time solution and you will only ever have to do it this once though, VBA is unquestionably fast to build up for, so you could lay aside a great deal upfront just using the object model. Another choice is to get this done from an Interop application in C# or VB.Internet where you might have with additional control over your atmosphere, like garbage collection.
However, if you are dealing with Stand out 2007/2010 (I suppose you're due to the 300k rows), I'd make a move different. I'd perform the calc programs around the primary XLSX in VBA after which use Open XML to process and make the 3000 excel spreadsheets and presentations with charts. (Note: I would not use Open XML around the primary XLSX since it does not really render built-in information - you'd still have to open the XLSX to "hydrate" the spreadsheet - so VBA could be better in cases like this).
If you are a new comer to Open XML, there is lots to understand upfront, therefore the juice might not be well worth the squeeze. But articles like this are extremely useful should you choose need to know or already Open XML, the industry great beginning point (because it handles charts too). But you might make use of a wrapper on Open XML SDK like Simple OOXML that's quite great for beginning out.
It is dependent strongly how you intend to process the information. If you are planning to create code in Stand out, it can make a lot more sense to depart it in Stand out. With that said, I'd dump the information to CSV (comma-delimited) for more processing having a different tool, like Python.
Things are always achievable given enough money and time. If you are like the majority of other developers, you do not have an excessive amount of either, so you'd like the best solution, or near to it. Whether it were me, I'd write code in Python to see the information from the CSV file, perform all needed procedures, and save the 3000 separate output sets as individual CSV files which may be imported back to Stand out.
Charts could be tricky to produce and manipulate from VBA. I'd make use of a Python library like Matplotlib to create all graphical output, which may be saved to disk as PNG images, which may be placed in to the Ms powerpoint presentation(s).
Python is pointed out here only for example. You need to use something that you simply feel quite acquainted with however, the concepts of processing the information programmatically (not via interconnected cell references and formulas after some VBA tossed directly into copy sheets and so forth) should still apply, and will also be your very best answer here; I did a lot of the type of work you describe. Obtain the data into CSV and process the information with code.
Have a look in the open-source record system known as "R". The correct answer is proficient at programatically producing graphs and charts from real-world datasets.