Daily posts of Excel tips…and other stuff
Archive for the ‘File Operations’ Category.
Hi Everyone!
Today I finally found time to actually post a brand new article on my website.
Wouldn’t it be useful to be able to edit the contents of an Office 2007 OpenXML file from within VBA? Well, now you can, using the sample code and explanation in this article.
Enjoy!!
Regards,
Jan Karel Pieterse
www.jkp-ads.com
When I test formulas on a spreadsheet, it s a very manual process. I'm trying to figure out a way to automate this. Here's what I have so far.
I want to identify some input cells and output cells. Then record some known good values for the inputs and outputs and store them. [...]
When rolling your own csv files, you have to account for commas in the text. Extra commas will create extra columns and you don't want that. Let's start with this data:
Two of the names have commas and two don't. By ignoring those commas, extra columns are created because a comma delimits a [...]
I have to save a file that may have the same name as an existing file. If it does, I append a number to the end of it to make it unique. The problem is that the file will live in three different folders in its life; Working, Review, and Archive. I [...]
Do you know what's worse than having a bunch of data in an Excel workbook that really should be in a relational database? Having a bunch of data in multiple Excel workbooks that really should be in a relational database. It's the Excel-workbook-as-a-record model and it's how we currently store our quotes.
Each quote [...]
Most of you will know that Excel 2007 (well, Office 2007) comes with a brand new file format, based on what MSFT calls Open XML.
This suddenly enables us to write code that can easily generate/change Office 2007 files without the need for an Office installation. For instance on a server.
Whilst there is proper documentation on [...]
Here is one for the Code Library.
Somehow I end up misplacing this bit of code. So every time I need to do it, I end up re-writing it.
Perhaps I'll save someone the same frustration along the way.
This code snippet will loop through each file in your folder (and subfolders).
For each workbook opened, it will unprotect [...]
Scott wants to create folders based on the information in certain cells. I suggest the MkDir function.
Check out the line below "Make sure base folder exits". Is that the best way to do that. For some reason I thought there was a problem with that method, but I can't think of what [...]
