Excel Queries
I came across some nice libraries to use while programming to query Excel. Right now I’m am using the standard COM interface for my projects, which is fine for small ranges (which is true for most users – I would assume). But for large ranges I would need something more powerful.
LinqToExcel appears to be a great project for using a Linq interface to query Excel (but not write to Excel). It uses ADO under the hood to do the actual queries. This is great if you are using Excel 2007 or newer, but for 2003 and older you run into the problem of memory leaks when you query an open Excel document (which is what I usually do, I haven’t needed to get data from a closed document before) – see also.
Scribble Filter Now Available and Other Muses
I got the sale software all installed so I can sell the Scribble Filter. There’s still a few changes I wanted to make with it. I wasn’t entirely thinking when I added the ability to filter ranges directly when I add (among others). I should have made it more compliant to what Microsoft does. I’ll be working on changing that around now. It will probably be a little while before I incorporate all of that into the filter. If the sales do well then I’ll make it more powerful otherwise I won’t be able to justify the time.
Scribble Filter (For Excel)
Points discussed in video:
The scribble filter is a text wrapper for the Advanced Filter in Excel, making it easy to work with and adding new capabilities.
Scribble Filter Home Scribble Filter Help
Overview Part 1 Overview Part 2 Overview Part 3 Overview Part 4
Scribble Filter: Installation & Opening
The scribble filter is a text wrapper for the Advanced Filter in Excel, making it easy to work with and adding new capabilities.
Points discussed in video:
Scribble Filter Home Scribble Filter Help
Scribble Filter (For Excel)
Points discussed in video:
The scribble filter is a text wrapper for the Advanced Filter in Excel, making it easy to work with and adding new capabilities.
Scribble Filter Home Scribble Filter Help
Overview Part 1 Overview Part 2 Overview Part 3 Overview Part 4
Scribble Filter (For Excel) Overview Part 3
Points discussed in video:
The scribble filter is a text wrapper for the Advanced Filter in Excel, making it easy to work with and adding new capabilities.
- Special Functions Defined @00:04
- Top or bottom (also purpose for chaining) @00:30
- Top or bottom by percent @02:10
- Above or below average @02:52
Scribble Filter Home
Scribble Filter Help
Overview Part 1
Overview Part 2
Overview Part 3
Overview Part 4
Scribble Filter Overview Part 1
Points discussed in video:
The scribble filter is a wrapper for the Advanced Filter in Excel, making it easy to work with and adding new capabilities.
- To get the date spans right click on the left calendar button.
- Use tab or enter to get the first line from the drop down menu.
- Right click on buttons to get more options (e.g., right click on report button to set type of report to create: On separate sheet, preview, in place, chain).
- Order of precedence is CHAIN, then AND, then OR. Use parentheses if you want something ORed first, otherwise it will be ANDed first.
Sheet Exists
I was looking at Siddharth Rout’s new blog (Adding/Deleting Sheets to the Excel File) and realized that I haven’t posted my version of sheet exists. Mine probably does too much per Code Complete standards, but it is nice not to have to think of multiple.
If I were to refactor this code I would take out the code for the charts and probably just make it work only for checking if the sheet exists and adding a new sheet if it doesn’t. I would create a separate function for deleting sheets. But once you have lot’s of code written it is difficult to refactor all your code just to change one function (and I use this function quite a bit).
List of Excel Functions
For the advanced filter I need a list of Excel functions. There are lists, unfortunately I needed the list with the function parameters (syntax). I also wanted it by date. I have lists of just the functions for Excel 2003, 2007, and 2010 without the syntax. So I did some screen scraping of Microsoft’s list of Excel functions where I was able to get the function name, category, description, syntax, and website URL for that function.
XML Comments
Comment headers above procedures can be convenient when you want to know what a function does and what the parameters are for – especially if they weren’t named with enough description. Luckily Visual Studio provides a way to do these comments easily and it gives you intellisense for your own functions! They are called XML comments. All you need to do is put in three comment markers (‘) and then it will fill in everything for you (if you already have the procedure/parameters filled in).