Answer My Searches

The newer versions of Office seem to have broken from the standard help/about layout that all other programs in the universe use. Here’s how you get to the new about menu.

For Excel 2007:

Click on the Office-logo at the top-left, then click on the “Excel options” button, then choose “Resources” in the left menu and then click on the “About” button.

I assume other Office products will work the same way. Post a comment if you find they don’t.

Source

Have you ever wanted to sort or filter by the background color of cells in Excel? There are no functions built into Excel to do such a thing, but you can still do it with this simple user defined function:

Function Color(MyRef As Range) As Long
Color = MyRef.Interior.ColorIndex
End Function


I found installing it a bit convoluted (at least in Excel 2003). First you want to make sure you get it into your personal workbook so it will always be available to you. But when I went to the Visual Basic editor in Excel, it wouldn’t show me my personal workbook.

What I did was record a simple macro in Excel, being sure to select to store it in the “personal macro workbook” option. Do something simple like type in a cell, and hit stop recording. Then go into the Visual Basic editor again, and you can see your personal workbook (PERSONAL.XLS). Place the function above in the same module as the macro you just recorded. You can delete the macro you recorded now.

To use the function you have to call it in a funny way. Thus to get the color of cell A3 you would type in a cell:

=PERSONAL.XLS!Color(A3)

And now you can get numeric values for the background colors in your cells! Yay!

It turns out that this nifty pyExcelerator program will let you write an Excel formatted document directly from Python.

While you could always use COM automation with the Python Win32 extensions to make Excel documents, I think this library is the way to go. It lets you write an Excel file without even having to have Excel installed, and you don’t even need to be running on Windows. That’s pretty neat. It sounds like an easy way to offer an Excel output format option from your application or website, without requiring any messing with Windows or Excel.

Sadly there wasn’t any friendly documentation for this project online, so here I offer my first code snippet using this package. I mostly figured it out from the example files included with the package. It writes a few rows of data and styles the data depending on content.
(more…)

Here’s the syntax to write to a CSV file:

import csv
w=csv.writer(file(r'C:\greg.csv','wb'),dialect='excel')
some_values=[[1,2,3],['A','B','C'],[4,'"5"','ab,c']]
w.writerows(some_values)

Make sure you use ‘wb’ when you open the file. Otherwise I’ve found that it will put a blank line between each row at least when Excel opens it.

Here’s the syntax for reading a csv file (from the documentation):

import csv
reader = csv.reader(open("some.csv", "rb"))
for row in reader:
    print row[0], row[-1]

And here’s the link to the full documentation:
http://docs.python.org/lib/module-csv.html
And this takes you straight to the examples in the documentation.

Update:
Another useful writeup of the CSV module.

[tags]Python, csv, csv file, comma delimited, comma seperated values, comma seperated, Python documentation[/tags]

Here is the VBA code to do this: http://visualbasic.ittoolbox.com/code/archives.asp?d=1466&a=s&i=7

So download and extract that code. Then within your VBA project add DOSOutpus.cls from the zip file to your project.

Then here’s a function I wrote which serves as an example of how to use this code, and it is also just a handy function you may want to use. You just feed it a commandline string (e.g. “ping”) and get the result back.

Public Function popen_and_read(commandline As String) As String
    Dim objDos As DOSOutput
    Set objDos = New DOSOutput
    objDos.commandline = commandline
    popen_and_read = objDos.ExecuteCommand
    Set objDos = Nothing
End Function

This was a bit tricky to find and big thanks to the author of this module for making this thing. I would have never been able to do this on my own.

The terms I searched for were:

1. VBA Spawn
2. VBA System Call
3. VBA Open Pipe