PaulKroon.com

Excel VBA Macro to Execute a Stored Procedure

Sun, 20 Sep 2009 19:48:02 -0400

The other day I came across a problem of running an MS SQL 2005 stored procedure in an Excel VBA macro. I had set this up before with another stored proc, which worked just fine. Copying this code (and later trying several other ways of doing it) always ended up with a closed recordset being returned. My exact error was a run-time error 3704 with the message "Operation is not allowed when the object is closed." The code looked something like this:

' Create new recordset object
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

' Get data to populate spreadsheet
' Assign the Connection object.
rs.ActiveConnection = cn

rs.Open "exec database.dbo.storedProcName", , adOpenDynamic, adLockPessimistic

' The error came up here
Application.Workbooks("Spreadsheet.xls").Worksheets("InvAdj").Range("A2").CopyFromRecordset rs
' Clean up
rs.Close
Set rs = Nothing

There didn't seem to be anything wrong with the proc, which ran fine in the management studio, and the VBA code was identical to what I had used in another macro to do the exact same thing (just return data). A lot of Googling didn't turn up much, but it sort of led me to believe it had something to do with the temp tables used in it. Going off of this, I finally found my problem. The proc was returning multiple recordsets, and the first one I get back after the .Open was empty. I needed to add the following after the open:

Set rs = rs.NextRecordset()
Set rs = rs.NextRecordset()
Set rs = rs.NextRecordset()

Although ugly, this got me through the other recordsets I got back and let me get to the data I needed. My hope is that I can get this p
ost crawled enough to show up sooner in some other poor soul's searches for this same problem.

AJAX and PHP WebMail Clients (and other ramblings about apt-get dist-upgrade)

Sun, 15 Mar 2009 23:15:17 -0400

There was recently an article I saw online (Slashdot or Lifehacker, I can't remember) about 10 AJAX and PHP webmail clients. The article is here. I check them all out one night and I ended up liking RoundCube the best. Since I started trying out IlohaMail for a webmail client with my IMAP server, which was very plain and not very user-friendly at the time, I figured it would be a good opportunity to give it a try.

I installed it on my laptop, just to test it out, and it worked great. I immediately brought it to my server to set up and start using. There is a good installer built-in to help with any initial configuring, but it's only really 2 config files and some permission setting, so I just copied it over from my laptop and edited the configs. Unfortunately, I found that it uses a command (mb_check_encoding) that requires a higher version of PHP than what I was running. Since I update every night, I found out that my installation of Ubuntu Dapper was getting pretty old. I figured this would be as good a time as any to upgrade...

I ended up upgrading from Ubuntu Dapper to Ubuntu Hardy, which led to problems with php5-mysqli (it became a part of php5-mysql, but I hadn't done a complete upgrade yet). After I finally got everything settled, I attempted a reboot to get the new kernel. Of course, this led to more problems with Evms. My /etc/fstab was changed to include those stupid "UUID" entries instead of the actual devices (ie, /dev/hda1). After booting into the old kernel, editing the fstab, and removing evms (I noticed the problem originally because it spit out a kernel message like "dm-linear device lookup failed" every half a second), I was back in business. Plus, as a nice bonus, IVTV is now built in to the kernel, so I don't have to worry about adding it as a module anymore.

So, to get back to my original topic, RoundCube is a very nice webmail client, which I found out when writing this uses IlohaMail code for handling IMAP. It uses AJAX in a way that makes it easier to use the application, but doesn't go overboard. It very simply handles your IMAP mail and folders, and has an additional address book. It's perfect for getting to your e-mail quickly when you're at someone else's computer.

Installing Google Gears on Ubuntu 64-bit

Sun, 15 Mar 2009 22:43:41 -0400

As an avid user of Remember the Milk, I recently noticed that my offline support wasn't installed on my Linux laptop (Ubuntu 8.04, 64-bit). I went to install Google Gears for this, but realized that it was only available for 32-bit at the time. A little bit of Googling and I found this site. This guy put together the .xpi files for a patched version, which I have found to work completely normally.

Just in case it becomes unavailable at that site, here is the latest version I downloaded from there. Note that he says it is current as of February 2, 2009, so it could become outdated pretty soon, but at least it's here until there is an official version out for 64-bit.

Wednesday, April 9, 2008

Wed, 09 Apr 2008 21:53:47 -0400

So, like always, I never have any free time to update much on my site. Over the next few weeks, I'll be doing the bulk of the rest of my school work, so hopefully I'll have some more things coming then. I do have to work on a BGP simulations tutorial for a class, so that will be the next newest addition.

I've been playing with subversion recently, and I hope to get going with Trac next. I definitely plan on putting something up about that because it does not seem to be as straightforward as I would have expected. That will be one of the last "essential" applications that I want to get used to for my goal of being employed as a systems administrator, so hopefully I'll be able to start playing with new stuff after that.

By the way, be sure to check out the bottom of my About Me section. I just set up a procmail filter with the ability to extract images from any email I get from my cell phone. You'll see...

Wednesday, February 6, 2008

Wed, 06 Feb 2008 20:28:03 -0500

In case you hadn't noticed it on The Daily Show, The Colbert Report, or Late Night with Conan O'Brien, Jon Stewart, Stephen Colbert, and Conan O'Brien have been having a fake feud over who "made" Mike Huckabee. It has been hilarious watching the three of them making jokes about it in their own, but similar, ways. This past Monday night (2/4/08), the three of them appeared on each of their respective shows to continue the fight. I just wanted to mention it for anyone who didn't catch the shows, and also put up clips of the shows (also so I can play around with some of what the new company I'm working for does, which I'll talk more about next time). The three skits are related in the order that each of their shows come on. First, The Daily Show and The Colbert Report are the ones I can post clips of. To see the last part (which is the best), you'll have to go NBC's site and watch the episode for Monday, February 4 here.

Here is the clip from The Daily Show:

Here is the clip from The Colbert Report:

And finally, the clip of Stewart and Colbert going to Late Night with Conan O'Brien:

Saturday, December 29, 2007

Sat, 29 Dec 2007 06:23:37 -0500

It's just about 6AM here at the hotel, and I'm just finishing up some work I was doing on my site so I figured I'd finish it up with a final post before I leave for Peru tomorrow. I first wanted to mention something from several days ago. I first heard about this puzzle that a company had posted on Slashdot. The company actually posted the job on Craigslist, and Slashdot picked it up. It was a series of tests and puzzles that led anyone who attempted it through some programming challenges, cryptography problems, and some fun with page source code. It has long since been solved, so you can start at the Slashdot page and follow the solution to the end. I started to work on some of it, but I mainly followed the Google group that they had set up. It was very interesting to watch just how intelligent and resourceful some people are. It ended up being a company called N-BRAIN that was releasing a piece of software in January called UNA, that was designed to assist developers in collaborating with each other on a project.

Stay tuned for the Fluxbox tutorial I was mentioning before because that is what I have planned for next. After that, though, I plan on putting up my pictures from Peru when I get back.

Wednesday, December 26, 2007

Wed, 26 Dec 2007 22:12:28 -0500

Okay, now that I'm on break, I have some time to work on my website. I'm going to work toward adding a lot more content on here, especially with the tutorial stuff. I'll be going to Peru in a few days, so I doubt I'll get a whole lot done before then, but I guess we'll see what happens.

Of some of the things I've been playing with recently, I want to put some stuff up about Fluxbox. I started using it a week or so ago now, and I absolutely love it. I've completely switched from Gnome now. I never liked KDE and Xfce was very similar to Gnome. I'll post my settings files and explain their use, so anyone who wants to try switching like mean doesn't have to spend as much time learning as me, although it was still very easy.

Friday, May 4, 2007

Fri, 04 May 2007 19:44:22 -0400

Ok, so now we've only gone past another couple of months; I'm really good at keeping this updated. Anyway, classes
are over and we're into finals now. I've got two under my belt, and only Geology and Differential Equations left to go. With this free time (for once in a long, long while) I've been able to play around with PHP. Hopefully my pages
still work for everyone else, but they definitely at least still work in Firefox. I've been able to add some site statistics stuff with it, so hopefully I'll add a nice little statistics page sometime for everyone to be able to see.

Also, I've gotten an internship with Ivory Tower Group that I'll be doing over the summer. The only thing is that it is starting right away, so I'll probably have to commute from Jersey for a couple weeks before I am living up at
school. It'll be rough, but I'll be doing some awesome things with Linux and working with a team of students. I'm
not sure of all the details, but it sounds like fun. Hopefully I'll have more free time this summer to keep things up to date, unlike last summer with Summer Fellows where I had no free time.

Well back to the last bits of stuff that I have to do. Hopefully I'll get some more time to play with my site and
PHP. Next is Perl, my favorite programming language that I haven't even learned yet.

Thursday, March 8, 2007

Thu, 08 Mar 2007 19:44:09 -0500

Well it's only been a few months, so obviously I haven't been very busy at all. The rest of the break was pretty
much all the same, just a lot of work. Then school got going again, and I had even more work. I've been doing a
lot of stuff with the ACM and all of the servers. At this point, I've got 5 of them running, and I'm ready to put
up two more if I ever get around to it. I just finished working out the actual implementation of my whole backup
system for all of these servers, so I'm ready to get it going on all of them. Then, once I figure out the tape
drives, I'll have everything done, nice and secured.

I'm currently in my spring break, but I'm doing just as much work as always. I still have to get a lot of research
work done and prepare a live CD for our ACM programming competition. Tons of fun, but at least I've got myself
organized now with my handy-dandy todo list. Hopefully I'll get everything done that I need to get done.

As you can see if you look around, I updated a lot of stuff on my page. I still have to put together some stuff for
that "Projects" section, and my "Linux" section is a little empty. At least I updated that wireless tutorial
though. The most important part is my resume. I added a short version of it and hope to get a more detailed
version up soon too. I need a job/internship for the summer, so any potential employers please take a look and
email me.

That's all for now, gotta get back to doing the things I really need to do.

Tuesday, December 19, 2006

Tue, 19 Dec 2006 19:43:36 -0500

So the past week or so has been pretty boring, I'll I've done is work and sleep. However, I've at least had a little bit of time to
work on whatever I wanted to with my laptop. Unfortunately this has been very necessary because my hard drive is dying and everything
keeps crashing on me. I've had to reinstall a few times trying to get things right, but at least I think I may finally have system
backups down. I also have been able to get better at wireless and I might even be able to update my tutorial and add some new ones
soon. That's all for now, back to work.

Sunday, December 10, 2006

Sun, 10 Dec 2006 19:42:55 -0500

H'okay...so a lot has happened since I got back from Spain. Well, only a lot if you consider the comp sci stuff I've been playing around with
important (like I do, hence this entry). After unpacking and recovering from jet lag, I went up to school to visit everyone. I found out that
my research room had become a mess, so a cleanup the next day was perfect timing. I got to make and drink my amazing Sunset Elegance that I
had missed so much. The next day I was able to play in the research room and I got a lot of stuff up and running. Thanks to the few guys who
showed up, we were able to get some good order going in there, but some sets of rules and guidelines are definitely needed.

Anyway, shortly after this little bit of free time it was back to work at the hotels. This time, though, I've been working at the Country Inn a
lot more. I was nervous at first because I still have not been "trained," but I got by okay and by now I am pretty comfortable as long as it's
slow. I've been putting in some crazy hours, I think I have about 60 from this first full week. With any luck I'll make back at least half of
what I spent in Europe.

On to the more interesting stuff, all of the time I've had during these nights has allowed me to play with my laptop more than I probably
should be. However, I've be able to get a lot of stuff done that I probably wouldn't have gotten around to otherwise. Besides getting some
server work done in the research room, I've come up with a lot of stuff for my laptop. I found a nice flight simulator for linux, fgfs with
apt-get, but that was just a time-waster. My big accomplishment came tonight with Linux Infrared Remote Control (lirc). It basically grabs the
hardware of an infrared port and allows it to be used with whatever remote control. I managed to program a random controller I found behind
the desk to work with my laptop. Basically, I can get any button to run any command on the computer. Once I figured it out, it was all pretty
easy too. I want to try and add a tutorial sometime soon.

Finally, I made a bunch of scripts to use with my webpage to automate this whole "blog" thing. I'm not serious enough to get some Ruby on
Rails or the like going for it, but I will have a script to dynamically update the content every time I add an entry. I love BASH scripting.
Well time to put up the first real post using my system of scripts. Maybe this'll make me get used to writing something here often.

Tuesday, November 28, 2006

Tue, 28 Nov 2006 19:42:10 -0500

Wow, so already I've packed my bags and I'm gettting ready to come home tomorrow. I can't believe three months have already past by. Anyway, let me recap
first. On Monday, a week before yesterday, I stayed at work until 6 so that I could make up some hours and have lunch with my boss, Eugenio. Other than that, I
finished the week with the archiving that I had been doing. It's basically just saving all of the old projects of the company onto DVDs so that they can be
erased from the server and stored. I had finished almost 100 DVDs, something that would have normally taken a month because of all the other stuff that the
regular people have to do at the same time. On Thursday, when I left, it was sad. I really wish I could have stayed, and I could tell that my boss would have
liked it too. He demanded that I write him email regularly and keep in touch. It'll be a great reference and an almost guaranteed job if I ever come back to
Madrid.

After work, a group of about 20 of us went to Tony Roma's (apparently an American restaurant that I've never heard of) for a Thanksgiving dinner. They had a
specific meal for it, and it turned out that they had too much food for the night so they offered additional plates for free. I ate a lot and it was an amazing
time. It felt like we were a huge family with one giant long table. We went around saying what we were thankful for and everything, although I still expect
something when I get back.

Nothing much else has happened since. I've finished some papers and done a lot of relaxing. I've packed up so all that's left now is to make it to the airport.
After this I'm not sure what I'll use my webpage for, I guess I'll just have to keep a "blog" now (I had hoped I would never do that). Later.

This is for testing:

Sunday, November 19, 2006

Sun, 19 Nov 2006 19:41:28 -0500

This past weekend I went on my trip to Paris with Aileen. We left on Thursday in the afternoon so that we could have a little extra time. The first interesting thing occured when we were
waiting for the plane to take off. We ended up being delayed for an hour. It wasn't a big deal and we got into Paris around 6 or 7. Then we got to try and get to our hotel. Unfortunately I
have become accustomed to Madrid's transportation, so we didn't particularly care for how hard it was to get from the airport to the hotel, but we made it. The hotel was really nice, and we
went out to a nearby restaurant for dinner, saving exploration for the next two days.

Friday we walked along the river to go to the far end of the city center to see Notre Dame and pass all the famous stuff, the Louvre, Eiffel Tower, Trocadero, etc. We went inside Notre Dame for
a bit to check it out and grabbed lunch in the area. That evening, we walked over to the Louvre because it was free between 6 and 10 at night. Aileen got to see one of her favorite authors,
Toni Morrison, who just happened to be giving a lecture and signing books that same night. We also, of course, saw the Mona Lisa. On the way back, we passed the Eiffel Tower again, only this
time at night. I have some amazing amazing pictures of it in my photo album. The next day, we didn't accomplish nearly as much, but we did
get to play around with the metro a little and ate some amazing crepes.

When we went to leave, we had a hell of a time trying to get to the airport because the train branches off and there isn't any way to figure out which trains go where unless you speak French.
We got it wrong and had to backtrack when we were already running late. We finally got there and then had to figure out where to go in the airport, which is pretty difficult because it is not
very multi-lingual considering it's an international airpot. Fortunately, there was a mistake and our plane actually left 40 minutes later so we caught it with a little time to spare. Despite
that small problem, Paris was definitely my favorite vacation, although I still like Madrid better. Later.

Monday, November 13, 2006

Mon, 13 Nov 2006 19:40:45 -0500

Not a whole lot has happened since the last time that I wrote. Last week of work went by just fine, we actually
had Thursday off because there is a Madrid holiday for Almudena, the patron saint of Madrid. Before that though,
I was (and still am) doing archiving for Tek. Whenever they finish a project with a client, they move all the
files into a separate folder to be archived and removed from the server to save space. I have been moving these
files off the server and saving them on DVDs for storage. Not very interesting, but I am doing something that
they need and it gives me some free time while waiting for them to burn to do whatever on the internet.

This past weekend, we opted to stay in Madrid for our first of only two more weekends in the city. A large group
of us Ursinus people went to an Irish bar at night. Not very Spanish, but it was cheaper than most places. Also,
during the day, I got a good amount of souvenir and gift shopping done. I've basically gotten everything I want
to get, just a few more things I hope to pick up this week.

Aileen and I are both really looking forward to Paris this weekend! Although I just checked and it's going to
rain the entire time, it will still be amazing to see the Eiffel Tower, another famous monument I can check off
the list. Th's all for now I guess, I'll try to put up an update and new pictures on Sunday after Paris. Later.

Monday, November 6, 2006

Mon, 06 Nov 2006 19:40:20 -0500

Right now I'm at work, and it's a slow day. What I've done is play around with my photo album and look into some Linux
stuff that I want to play with when I get back home. Lot's of stuff has happened, so hopefully can get through as much
as I can while I'm here.

These first three weeks of work have been great. I've been gradually getting more and more to do, and I have gotten used to the
people. I love the Spanish (and probably European) workplace. It is a lot more laid back, and it makes me wish I could live here
even more. In fact, after the first two weeks Eugenio had told me that I should give him a call after I get out of college.
Something more to think about because I would love to work here.

Besides work, we've still had classes. Unfortunately, we've also started a new class called "Spanish Out There." This joke of a
class is 3 hours long, the first of which we are in a classroom doing the same things that we already did with our intensive
language class. The rest of the class is suppossed to be us going out into the Spanish world to talk with real Spaniards to
practice our language. Sounds like a good idea, but it is done in the worst possible way. First of all, I thought the whole living
here for three months and working here for six weeks was pretty "out there." It could work if we did something new, but it's just
places we've been to before with ridiculous assignments. For example, this past Friday we went to El Corte Ingles, the huge
department store that I basically live at. We were suppossed to find the prices of things that we would take on a vacation.
Because asking people for prices and where to find things was what I did the first couple weeks of being here, this was a huge waste
of time. So, the four of us that were walking together decided to go up to the little restaurant on the top floor and have
breakfast. It was one of the best I've had here; fried eggs, bacon, and french fries. Only thing I learned from that class was
where to get a great, inexpensive breakfast.

Along with SOT, our PELA class has also been ridiculous. It's suppossed to teach us about the Spanish workplace, but we only have
been learning about things I already know, just in Spanish. The worst offense though was being assigned a 10-page paper. The work
isn't the problem, it's the fact that this class is only worth 1 credit that is the problem. We've all been complaining to our
professor about it, so I feel sorry for all the stuff she has to deal with. Hopefully she can get something fixed before we finish
here.

It hasn't all been problems besides work though. This past weekend we had two day-trips to Avila and Toledo. I'll hopefully get
some pictures up soon. It was a lot of fun, although I've seen more than enough cathedrals for several lifetimes.

Well it's almost 1 and time for me to head out of here. I'm going to meet up with some friends for lunch at TGIFridays (I don't care
if it's American, I'm hungry and it's good). Later.