How to convert text to proper case in an excel sheet

I was doing a form letter mailing here at work and when we run queries against our database, the result set is pulled into an Excel spreadsheet in all upper case. So, a customer's address would look like this:

JOHN SMITH
1313 MOCKINGBIRD LN
TRANSYLVANIA, PA 11111

Obviously, in a professional letter to a customer addressing them by their full name in upper case just clashes with a letter that is properly puncuated and capitalized. What I needed to do was convert it to this:

John Smith
1313 Mockingbird Ln
Transylvania, PA 11111


Each field is laid out separately in the Excel sheet, so the data was a bit easier to work with. To put it simply the customer's first name, last name, address, city, state, and zip code were all in their own column. I couldn't quite find a simple VBA routine on Google that would do this for me so I wrote my own.

The code below will run against the text that is actively selected in Excel.



'--------------------------Begin Code--------------------------------'
Option Explicit
Private Sub ProperCase()
Dim thisCell As Range
Dim strSoFar As String
Dim strTmp As String
Dim x As Long
Dim tmpArr() As String

For Each thisCell In Selection.Cells
tmpArr = Split(thisCell, " ")
For x = 0 To UBound(tmpArr)
If x = 0 Then
thisCell = UCase(Mid(tmpArr(x), 1, 1)) & LCase(Mid(tmpArr(x), 2, Len(tmpArr(x))))
Else
thisCell = thisCell & " " & UCase(Mid(tmpArr(x), 1, 1)) & LCase(Mid(tmpArr(x), 2, Len(tmpArr(x))))
End If
Next x
Next thisCell
End Sub
'--------------------------End Code--------------------------------'


When run the code will take all of the text in each cell and separate it where this a space. It will then capitalize the first letter of each word and lower case the rest of it. Then each word is rejoined with a space. If you have double spaces in your text it will probably add an extra space or two.

So, anything that looks like jOHN sMITH or JOHN SMITH or john smith will end up looking like John Smith after the macro is run.

Is the CIA scanning Craigslist.org?

So, I'm an IS Manager with a rather large corporation and in my spare time to try and earn a couple extra bucks I fix PC's on the side. Most of the time I wind up working for friends and family and don't have the heart to demand money since most of them have very little and I'm not destitute. Anyway on to the meat of the story.

To try and ramp up business I posted an ad on Craigslist.org for PC repair. It's just a basic ad to advertise that I do mobile PC repair and tutoring. To find out how effective my advertising is I inserted a small tracking image into the ad that links back to my own personal webserver. So, by viewing my web logs I can see who looked at the ad, when they looked at, and where they came from. Obviously, the few hits I do get come from Verizon or Comcast users. But one of them looked "different". That IP was 198.81.129.194. I did a reverse DNS lookup and it came out to relay2.cia.gov The CIA? What's going on? I can only speculate. As far as I know I'm not near any of the CIA offices so I can't imagine it would be an employee surfing while at work for someone to fix their PC.

Does the CIA think terrorists and spies are advertising on Craigslist?

SQL INSERT statement with a Microsoft Access Database

I started working on a VB client/server app that uses Winsock to transmit encrypted data to and from my server at home. The server end connects to a simple MS Access database and runs SQL queries against it. The database only has 2 tables. One is named "Users" and the other is named "Accounts". The "Users" table only has two columns. One of them is named "ID" and it is an integer value that auto-increments. The other column is named "User" and just stores a 50 character string value.

One of the more simple queries involves updating the "Users" table by adding a new row to it. This is the code I was using INSERT INTO Users (User) VALUES ('sometext') Every time I ran this SQL query it would crash with an invalid syntax error. I scratched my head and fumbled around for quite a while trying to figure out what in the World was going on. By all accounts my syntax was perfectly fine and sometext did not contain any SQL reserved characters, such as, the apostrophe < ' >.

I even ran the same query against my "Accounts" table after substituting the User column for a column in my Accounts table. The SQL query ran perfectly. Just for kicks I changed the name of the User column in my Users table to "Name", re-ran my query, and it ran PERFECTLY. I guess having a table named "Users" with a column named "User" is unacceptable to Microsoft Access and possibly other database verisons? This is probably documented somewhere I'm sure.

So, the moral of the story is, "Don't give your table coumns names that appear within the name of the table itself."

Rear drum brakes on a 97 Plymouth Neon

I decided I better check the rear brakes on my Mother's 97 Plymouth Neon. It's a four door automatic with front disk and rear drums. It's already had the front brakes done once and has about 90,000 miles on it.

Every other set of drum brakes I've worked on all you have to do is jack the car up, pull the wheel off, and bang on the drum a few times to break the rust bond it forms with the wheel hub over time. Dodge/Chrysler/Plymouth decided to make everyone else's live difficult. The rear bearing is pressed into the drum and then it slides over the rear axle. So, to remove the drum you have to get the steel protective cap off the drum using a thin bladed flat blade screwdriver to pry it away. Once it's off there is a 1 3/16" lock-nut holding the drum to the axle. Once you remove that nut you can probably slide the drum off just by wiggling the drum around with two hands. It worries me that the only thing holding the entire wheel on the car is this nut and it doesn't even have a cotter pin for extra safety.

I knew there was a reason I hated Dodge cars and trucks.

Swollen Uvula

My friend and I just got back from Hawaii a little while ago. The second night there we grabbed some beers and chilled out on our balcony at the Hale Koa Hotel. I had a 12 pack of Red Stripe and he had some cider beers.

Long story short I drank 10 of the 12 beers in about a 4-5 hour time span, which is a hell of a lot of beer for me . I'm not a big drinker. I ended up throwing up before going to bed and fell asleep on my back. That morning I woke up and could feel my uvula hanging in the back of my throat. It was a bizarre feeling and creeped me out. I was afraid I had an allergic reaction to the sushi the night before or had some sort of viral thing going on. My buddy was making fun of me saying I was snoring like a fat person who can barely breathe. I don't usually snore either.

Since Hale Koa doesn't provide free Internet access I went out on the balcony and managed to jump on someone's free wireless network and do some research. Before I lost the connection I found a site where someone else had the same exact thing happen to them. For them it took about a week to return to normal. Relieved about the situation I decided to just enjoy the rest of my vacation and try not to pay attention to it.

To say my swollen uvula was annoying would be an understatement. It was like having something stuck in the back of my throat 24/7. Sometimes, it would flop forward onto the back of my tongue and I'd have to swallow it to get it back in its proper place. I could reach in my throat and touch it with my finger and it didn't tickle or hurt, but it was beat red. I eventually started getting a sore throat from all the irritation. After a few days I finaltly noticed it getting smaller and after about 10 days it was completly back to normal.

Setting up a passive ftp server behind a firewall or NAT router

It's hard to find a decent FTP server application that will allow you to specify whether it runs in ACTIVE or PASSIVE mode and as a Windows system service let alone specify the ports it uses. Even the IIS package that comes with most versions of Windows isn't quite up to my task.

I won't get into the specifics of setting up an FTP server in either Active or Passive mode, that's what Google is for. Here is a good link that breaks it down into simple terms. Active vs. Passive FTP

In my situation I had the following problems and requirements.

1) My FTP server is behind a Westell 327w DSL modem with Verizon Internet service. The modem/router supports IP pass through to a single machine, however, I prefer the extra layer of protection it offers in NAT mode. I will want to use the router's port forwarding abilities in my case.

2) I want to be able to connect to my FTP server from behind another "NAT'd" computer. My company's corporate firewall blocks all unsolicited incoming network traffic. Luckily they don't block outgoing ports.

3) I can't use the standard 21 or 20 port (Verizon blocks it)

4) I want my FTP server running whether or not someone is logged on to the machine. There are a lot of basic freeware FTP servers out there, but the majority of them will only run if a user is logged in to the machine. To preserve system resources I'd rather not have a user session running on the machine. Therefore, it needs to run as a Windows system service.

5) I need to connect to the FTP server from both inside my "Nat'd" network, as well as, from outside of it. Verizon does not allow loopback packets (other than ICMP ping requests AFAIK). So, if I try to connect to my FTP server from within my network using my "Internet IP" the packets will go out to Verizon's servers but never get looped back to my Westell router.

6) I don't want to pay for the FTP server software. I'm a big open source/free kind of guy.



I decided the best course of action would be to use an FTP server setup in PASSIVE mode. I can't use active since my company's firewall will just drop/ignore/laugh at the icoming packets from the FTP server rather than forward them to my FTP client. Since I control the port forwarding on my Westell 327w I can recieve unsolicited requests from an FTP client. So, using ACTIVE mode is my only real course of action.

In my Westell I configured it to forward the ports 20,000 - 20,500 my FTP server. Having 500 incoming ports to the ftp server is probably overkill in my case. From what I've read you only need one port per client connection. So, right now I would assume I can support about 500 clients logged in at the same time. I also decided to run my FTP server's main port on 5000 instead of the standard 21. This gets me around Verizon's restrictive nature of blocking incoming port 21 connections. It also aids slightly in avoiding all the little script kiddies out there scanning for open FTP ports.

When it came to choosing an FTP server it became more difficult. I started out with Microsoft Windows IIS 5.1 since it was built into my OS. After doing some research I learned that you can change the passive ports it uses by adding a string value named "PassivePortRange" to the registry under HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Msftpsvc\ParametersYou can read the details of it here Microsoft KB 555022

For some reason I could NOT get it working from outside my local area network. I would get prompted for a username and password but could not retrieve a directory listing. I've since discovered why. In PASSIVE mode after the client has authenticated and requests a directory listing or file, the FTP server sends a command to the client with what IP address and port it should connect on. However, IIS does not resolve your externally visible IP address. Instead, it sends it the FTP server's local area network IP address. For example, 10.1.1.50 or 192.168.0.50. Neither one of these are accessible to your FTP client so the connection fails right after login. But inside your local area network this would work fine since 10.1.1.50 or 192.168.0.50 would be perfectly normally. What the server needs to do is send the client your "Internet IP" address, such as, 71.245.32.76 along with the port to connect on for data transfer when they are located "outside" your local area network. When a client connects from inside your local area network they need to recieve an ip address, such, as 10.1.1.50.

I was only able to find two FTP clients that had the ability to send different IP addresses to their clients in passive mode depending on where they were connecting from. One of them was edtFTPD which was ported over from the Linux version of proFTPD using the .Net framework. proFTPD is able to perform this tricky task by changing its default config file to use VirtualHost and MasqueradeAddress. I didn't have any luck getting it working and found edtFTPD incredibly difficult to manage users with. You can read more about this program here


Then I tried War Daemon 1.80 by jgga. It took a little while to find the documentation I needed but there is a nat.conf file which can be used to overcome all of my connection problems both from inside the network and outside of it.

Here is the instruction text included in the file. Lines starting with # are ignored by the application.

# Network Adress Translation (NAT) configuration file for
# the War FTP Daemon 1.81
#
# (Lines starting with '#' in this configuration file are treated
# as comments.)
#
# Remote IP range:
# Network address and mask to identify the network
# or interface to set options to.
# The network mask can be in dotted or simple notaion
# i.e. /255.255.255.0 = /24.
#
# Port:
# The port the FTP server binds active (PORT command)
# data connections to. Use 20 for RFC959 compliance,
# -1 for one below the FTP control connection port,
# an exact number or 0 for random numbers.
#
# Host Address:
# The IP address the server reports in the reply to
# the PASV command. If the server is behind a dumb
# NAT router, it can report the IP number to the
# routers external interface, if the router is
# configured to forward TCP/IP traffic on some ports
# to the FTP server.
# The Host address can be one of tree:
#
# 1) An IP number
#
# 2) A host-name. If you specify a host-name, the name
# will be looked up when the server starts, and
# each time nat.conf is reloaded. You can also
# specify that the server should resolve the host-name
# automatically each n minutes, where n can be specified
# by adding a column after the hostname.
#
# Example: ftp.jgaa.com:10
# This will make warftpd resolve ftp.jgaa.com each 10
# minutes in the background, without slowing down the
# server.
#
# 3) A "dynip" declaration. Dynip stands for "Dynamic IP".
# This mode makes warftpd contact a server at
# dynip.jgaa.com each 5 minutes. This sever updates
# your FTP sites IP in DNS (example: yoursite.dynip.jgaa.com)
# and return the actual IP address as it is seen on the
# Internet to warftpd. You can specify another refresh interval,
# by adding a column after the dynip specification.
# The dynip specification consists of an alpha-character,
# a dynip handle ID, a column and a secret password.
# Before you can use this mode, you must register your
# site at http://dynip.jgaa.com. When you register your
# site, it will be assigned a site ID, and you will be
# asked to provide a secret password. The password is
# required to prevent anyone else from "hijacking" your
# site. Please note that the password cannot contain spaces
# or column characters due to the syntax of the nat.conf file.
#
# Example: @1:secret
# This example specifies that you own dynip Site ID 1,
# and that it uses the password "secret".
#
# Example: @1:secret:10
# This example also specify that warftpd should exchange
# updated information with the server at dynip.jgaa.com
# each 10 minutes. The refresh rate should be 60 minutes
# max. If the delay is too long, the DNS information will
# be disabled at dynip.jgaa.com.
#
# Port-range:
# A range (from-to) of port numbers the server can bind
# to during passive (PASV) data transfers. This is
# typically a range the router will forward to the FTP
# server, or a range the firewall is aware of as data-
# ports to the FTP server. A value of 0 means that the
# server will pick random ports.
#
# The rules are matched from top to bottom.
# If a network-number/mask pair match the clients TP address,
# the options are applied. If no rule matches, the default
# options for the server (ftpd_BINDDATA) is applied.
#
# This configuration file is read when the server is started,
# and when changes in the machines IP numbers are detected.
#


# The following sample is meant for a server on the private IP
# address 192.168.0.2, behind a router that forwards IP
# adresses from Internet to the server without changing the
# source address or port number.
#
# Line 1: Use default values for localhost
# Line 2: Use default values for the local network (LAN)
# Line 3: Set options for all other hosts
#
# Remote IP range # Port # Host Address # Port range
#---------------------- ------- --------------- ------------

To the bottom of this file I appended the following

127.0.0.1 2001 0 25000-25500
server/255.255.255.0 2001 server 25000-25500
0/0 -1 ftpserver.no-ip.org 25000-25500


Now I'll explain how this works... to the best of my knowledge.
The line below is used to handle connections made from the FTP server machine itself. I'm not sure if it is needed but someone else added it so I left it alone.
127.0.0.1 2001 0 25000-25500



The line below is used to handle connections that are made from within the local area network or subnet. War FTP Daemon will see the 192.168.1.50/255.255.255.0 part and interpert that it applies to connections coming in on the machine's direct IP.

I'm not sure what the 2001 really does. I believe it specifies the active port, but I don't use it.

The 192.168.1.50 is the IP address that will be sent to the client with the PASV command instructing it to connect back on that IP. The 25000-25500 range is a range of ports the FTP server will use to send along with the IP when issuing the PASV command.
192.168.1.50/255.255.255.0 2001 192.168.1.50 25000-25500


The line below is used for all connections coming in from outside the local area network. When the connection comes in War FTP Daemon will resolve the ftpserver.no-ip.org to it's ip address and send that back to the client. An IP address can also be specified here. However, I'm using the no-ip.org service so this is similiar to what mine looks like.
0/0 -1 ftpserver.no-ip.org 25000-25500


*NOTE* I think these rules are interperted from the top down, if a rule is encountered that matches the criteria it will use that rule and disregard the others. I haven't been able to find any documentation stating this though. If you set it up in the order I have shown above you shouldn't have any problems.

So, there you have it. I hope this helps someone as I know there are a lot of people struggling with this problem.