VBA

LibreOffice is a Faster Tool to Load Excel Data into an SQL Database

We wanted to know how Bernie Sanders did in several electoral districts.

What are our data sources.

There are two public databases that can help us discover these vote totals. One is the election results broken down by voting precinct. The other is a database that maps the precinct numbers to district numbers.

lavote.net's precinct reports

The Los Angeles County Registrar Recorder publshes vote tallies aggregated by precinct. The problem is, these are reports, not raw or cooked data.

Visual Basics: VB, VBA, MS Office

This is a collection of related notes. I will not be updating them as I'm not using the platform at this time.

They're out of order, but there's a lot of potentially useful information in here. Maybe I'll compile it into a short ebook at some point.

An Outlook Script to Save Spam for Training Spamassassin

On the spamassassin box, set up Samba and create two shares spamassassin-spam and spamassassin-ham. I created them in /home/Spamassassin/

MS Access: Showing "Continue..." Conditionally at the bottom of a Section in a Report

Maybe I'm missing something - but it looks like Access doesn't have this feature - to put "Continued..." or "More..." at the bottom of a section if the next section is on the next page. If it exists, please comment or email me at johnk@ the domain name of this site. I seriously hope it exists.

I have this complex report that is a little non-standard - and here's how I did it. The general technique is at this other post:
Printing a Repeated Section Message like "Continued"

MS Outlook: Dumping MIME EML Email Files for Spamassassin Training

Here's a VBA script that I'm using to train Spamassassin from Outlook. It saves out email messages to a file server where messages are used to train the filter. The problem here is that Outlook doesn't save EML (MIME format) files. You can save messages as text, but lately, spammers have been loading messages with a lot of chaff text that looks like regular email. You can't train with that, because it might cause the filter to start mis-identifying legit email as spam.

MS Outlook: Mass Email VBA Script

This is an outline I'm going to use to fix up a mass email script for Outlook that I'm working on. It was kinda elegant and explains how to do some cooperative multitasking with Visual Basic for Applications.

As you might imagine, there are a lot of mass email tools for Outlook. See Mass Mail Tools for Outlook, but this first version had some special needs (it was a huge list), and the second one I'd like to give to others, so a simplified, stripped down tool is best.

MS Access: Comparing Queries Between Two Databases (a query diff)

Often, when you have MS Access in a small office, and have done the right thing and split the database into a backend of tables and frontend of queries, reports, and forms, you end up with changes to the objects in multiple files. The trickiest is comparing queries, because the query object is modified if even a column width is changed. You need to dig deeper and compare queries.

MS Access to KML Data Dump

Here's a script that helps to export KML files for Google Earth from Access tables. The idea is that you create a query with columns named "Latitude" and "Longitude" and any other columns you need. Open that query, and pass the recordset to this dumper. You also specify a file name, and a list of columns to use for the name and definition fields.

Fixing Phone Numbers so they Fit a Common Format

It's common to get a list of names and phone numbers in a spreadsheet or from the web, and the formatting varies. In the US, people don't use a standard formatting consistently. Lately, they have taken to making phone numbers look like domain names or ip addresses, example: 415.555.1212. This function normalizes phone numbers to look like this: 213-555-1212 x1234. The code's structured so multiple regexes are used to perform the matching, allowing for easier modification of the code. (This code was written in Excel, but should work in any VBA application.)

Magic Folder to Convert DOC and OpenOffice to PDF

Most of the information in this post is derived from http://www.tech-faq.com/convert-word-to-pdf.html. It's posted as a service, because the code there needs some editing.

Also, a useful thread about executing OOo macros is at http://www.oooforum.org/forum/viewtopic.phtml?t=2619.

Here's the code to automate the opening and saving of a file as PDF, using Open Office. Paste this into your standard macros.

That 70s Computer: The People's Computer Company

The first computer book I read was "Teach Yourself Basic" by Bob Albrecht. It wasn't a really good book - at least not for a child - but there it was. Mr.

MS Access: Automatically Jumping to the Only Record that Matches

Many years back, just before web pages got popular, I remember that some programs sent you as close as possible to your desired data whenever you searched. If you typed a search term, and only one record matched, you'd be taken to that record.

I have been using an Access db at work that doesn't have this feature. It's kind of a pain, because when you search, you sometimes get results that are one record, or no records at all. Below is code that will take you straight to the record if you type in a search term that's specific enough.

MS Outlook: Remove Duplicate Contacts

This is a pretty good de-duper based on the one posted to a forum.

VBA: Transforming XML Error Messages into VBA Errors (Raising or Throwing Errors)

This is trial code that I used to translate an error from a Yahoo web service into a COM ErrObject.

It's not real XML parsing, but good enough for this purpose. IF an error message is sent, we extract the message and then use Err.Raise to throw an error.

Syndicate content