Getting Excel to work in Scheduled Tasks

It's been a while since I've had to post any voodoo, but this one I thought was worth mentioning.

Sometimes, when you're trying to turn data into information in a BI environment, you have to suck it up and access spreadsheet data (*shudder*). If that data is not clean, you may need to run a macro to export clean data to another file, massage it, and then load that.

Since we want to automate this process, we need to:

a) Make sure we run the code in the Workbook_Open() event of ThisWorkbook
b) Turn off all the security alarms that stop it running automatically
c) Set up a scheduled task.

The problem is that even when you have done that, Excel really doesn't want to run in a non-interactive environment. More than likely your scheduled task will fail silently.

To get around that, create these two folders on the server that is running the task.

C:\Windows\System32\config\systemprofile\Desktop  

C:\Windows\SysWOW64\config\systemprofile\Desktop

For some reason, Excel wants these folders to exist when it is being run non-interactively.

Try again, and all things being equal, your code should work.

Remember to close Excel as the last line in your VBA code to avoid blowing up the servers memory over time.

Truncated Memo Fields

This one drove me crazy for three hours and I thought I would share it with you, as it demonstrates the dangers of falling for the red herring!

Observe this simple piece of code:

Dim dvStory As DataView = _ DirectCast(dsStory.Select(DataSourceSelectArguments.Empty), DataView)


For Each drvStory As DataRowView In dvStory ' Should only be one
    lblHeading.Text = drvStory("Heading").ToString()
    lblSubHeading.Text = drvStory("SubHeading").ToString()
    storyBlock.Text = drvStory("Block").ToString()
Next

Essentially I am pulling values from an Access database and populating a webform. Everything is fine, except for the storyBlock.Text which is pulling from a memo field called Block.

When presented, the field is truncated at 255 Characters. 

"Damn .toString()!" I yelled in frustration. Obviously the toString was truncating the field and try as I might I could not find away around it. Except of course, it wasn't truncating the field. The real culprit lay in the call to Access via SQL:

<asp:AccessDataSource ID="dsStory" runat="server" DataFile="~/CompanyNews.mdb"
  SelectCommand="SELECT DISTINCT [Group], [Heading], [SubHeading], [Block], [PublishFrom] FROM [Blocks]">
</asp:AccessDataSource>

There was a WHERE clause in there too which I removed for simplicity's sake.
Turns out that the 'DISTINCT' was the cause of the problems. When I asked the query to return only distinct values, Access was forced to compare the memo field against all other records, and that comparison causes truncation. By forcing uniqueness at entry level I was able to throw away that clause and now the .toString() (which was doing its job correctly all along) returns the full memo field value.

Fun way to end the year! 

RPC Server is Unavailable or Acquiring Network Address

This is one of those annoying ones for which there are 20 solutions, one of which may work, and all of which require an internet connection, which you do not have.

What follows is a way around the problem, and is not intended to be permanent, although if its for a desktop or a laptop that never leaves the house then use it by all means.

If you get the Acquiring Network Address message and it never goes away, you may be inclined to dig around and discover that the RPC Server is unavailable. What an RPC Server is or why its missing is beyond the scope of this little blog, suffice it to say that if you want to solve the problem you will need to google around and download a fix, which you can't do, because you can't connect to the internet!! (I'm assuming you're accessing this on your phone)

The workaround is really quite simple. Go to Control Panel-> Network Connections->TCP/IP Protocol (V4) and select Properties, then select specify an IP address.

Give your machine an IP address , subnet mask and default gateway. The info on the default gateway and subnet mask should be written on your router or the book that your cable guy left you, or if you do have another computer you can do Start->Run->cmd then type ipconfig and find it there.

The ip address should be in the same domain, so if your default gateway is 192.168.1.1 and your subject mask is 255.255.255.0, then picking, for example 192.168.1.42 should be a pretty safe bet, unless you're on a corporate network, in which case it may take some trial and error to find a free address.

Once you have made the changes and hit okay, restart your machine and you should be good to go. Now you can download any patches, drivers etc to resolve the problem, or leave the machine asis if you are not planning to take it out of the house. Remember to change the config though if you change service providers.

That's all for now, over and out.

Review of the Kindle Fire: Update

Given the price, and the developer buzz about this product, I couldn't resist getting my hands on the new Amazon Kindle Fire.

$199 gets you a 7" tablet , a power cord, and a one page with basic instructions, which are basically, charge it up and turn it on.

You'll need an Amazon account if you don't already have one, and I recommend forking out the $79 annual fee for Amazon Prime, since that really does pay for itself pretty quick, and gives you access to all kinds of additional free stuff.

What you are basically buying is a magical store window from within which you can instantly purchase (with one click and no 'Are you sure?') anything in Amazon's extensive product line. This is why Amazon is able to price at $10 below cost, because they know they will make up the difference quickly, and in spades.

So the basics then. It's black, with the usual ubiquitous touch screen, and a simple user interface. Weight close to a pound (14.6 oz) it feels quite heavy which is both comforting and annoying after a while. A stand for watching movies is definitely going to be needed. The 7" screen is bright, but you will likely want a screen guard since the fingerprints mount up quickly.

In terms of functionality, it's important to remember what it is not, namely a smartphone. You cannot use this version as a phone since there is no microphone. The USB port is not a solution either, since it only serves to allow you to connect to your PC as if it were a thumb drive, and to side load apps if you Root it (currently only possible on Mac and Linux, although that changes by the minute. There are instructions for Windows, but I cannot confirm they work. Yet...).

There is no camera in the Kindle, and support for photos is surprisingly poor. The quality is fine, don't get me wrong, but there is no easy way to access any photos you have stored online, unlike Music and Video, for which there are instant links to Amazon's Cloud.

There is not a lot of disk space either - about 5 gigs available (not the advertised 8, unless you want to split hairs) for users to play with, nor is it readily upgrade-able (no SD card). The reason for this is that Amazon wants you to use their online cloud, where you can store all of your documents, photos, music, videos etc, and then download them as you need them. The cloud is free up to 20 gigs.

Think of it as going on a trip: You don't carry your whole house with you (unless you're a tortoise). You pack what you need for the trip and unpack when you return. So if you don't have Wi-Fi handy, you have enough room to store a couple of movies and several hundred songs and books. When you return, you can store zap what you don't want and replace with new stuff from your cloud. This is extremely useful if your Kindle goes walkabout, swimming or encounters some other existence sapping adventure.

There is no 3G, however if you have a droid you can set it up as a hotspot, and as long as the signal is good, you're covered.

Now the good stuff. Movies load fast, and you have as many choices as you have subscriptions. Netflix, Hulu, Amazon, all are available to you easily. The quality is crisp, and the sound very respectable even without speakers. Youtube is a breeze, directly from the browser.

You can order subscriptions to your favorite magazines and newspapers have them delivered to your Kindle as they come out. Unfortunately, you are still going to be subjected to an increasingly annoying number of advertisements, but at least they don't fall out in your lap.

Web surfing is a breeze, although I would like to see the kindle make use of favicon.ico or some other mechanism to produce prettier web links for storing in your favorites.

Books, the whole point of the Kindle in the first place, are delivered cleanly and easily, and without any fanfare. There is no cutesy page turning, which frankly I would like the option to have, to help me ease into the idea of reading a book online without feeling like I'm working.

Music can be loaded from your Cloud Drive, or if you have a USB cable (which if you have a droid, trust me, you do) you can load directly into the Music folder on your kindle from your PC. Oddly, I have not yet found a shuffle function for the music. You can download a free Droid Music Player, which has that functionality, so there is a workaround.

Speaking of workarounds, if you are used to the simple integration of Droid with Google, then you may be in for a little frustration, most, if not all of which can be overcome.

Gmail works fairly well with the built in email program, but gmail chat/googletalk does not. You will need to download Trillian or some other clone to get that functionality.

Google docs is cludgy unless you integrate it with Quickoffice.

As for Google Voice, well remember : This is not a phone. You can't fake it, like you can with ipod Touch, because there is no microphone. However you can still send SMS messages and listen to voicemail if you have a burning urge to, as long as you have a Google number.

The software keyboard is what it is. It doesn't support Swype (at least not yet), but it is pretty accurate and doesn't take much getting used to.

There is a Kindle Development Kit, which I have not yet played with but which I hope integrates nicely with Eclipse, and offers promise for side-loading.

Finally, Battery life seems to be as advertised. I turned it on yesterday morning, played with it intermittently throughout the day, and by midnight it was ready for a nice nap and an overnight charge. 2 hours seems to be sufficient if you're not playing with it whilst its charging.

Conclusions? If you like toys, this is fun and cheap. If you want to surf the web, read a book, listen to music, watch a video, this will definitely work for you.

The water starts getting choppy when you use the keyboard, but that is like any new tablet/smartphone style product, and time will tell if its a real issue. There is not likely to be an external keyboard option.

This is not a serious work tool. Its fine for taking simple notes (I recommend ever note) and for sending short to medium length emails.

Mostly this is an entertainment/consumption device which can certainly stave off boredom, but if you need to do some serious work, stick with a laptop.

*UPDATE*

Its now 4 months on, and I am still happy with my Kindle, especially after rooting and Jailbreaking it, allowing me access to the Android marketplace and all that implies.

For a 7" tablet, its hard to beat at this price point, and has become a staple part of my technology choices, when the smartphone isn't cutting it and I don't want to pull out the laptop. And its great to play with over lunch too!

Cannot open an anonymous level security token

I'd been avoiding Internet Explorer 8 for as long as possible, but finally had to bite the bullet when I ended up with a new laptop with Windows 7.

One particularly unpleasant problem I encountered was with Business Objects when attempting to create a new Web Intelligence (webi) Document. I would get a Javascript error saying "Cannot open an anonymous level security token"

After a bit of cursing and some digging, I found out that its tied to DCOM, which meant running the DCOM Config tool, as follows:

1) Start, cmd, dcomcnfg

2) Select Component Services, then drill down to My Computer and right-click on it.

3) Select properties

4) Choose Default Properties

5) Change the Default Authentication Level to 'Connect'

6) Make sure the Impersonation level is set to 'Identify' (by default, it should be)

7) Hit ok, exit IE then reload.

That should take care of it. :)

Got a bad Android Marketplace? Here's a Solution!

Many of you who bought Android Tablets or phones over christmas in the hopes of spiking the ball in the face of iPad/iPhone users, may have been disappointed to discover that the app store on your phone is 'a bit dodgy' . If you got a grey market phone, or perhaps got a generic tablet from China, the appstore is most likely less than stellar.

On investigating ways around this problem, you may have been told to talk to the manufacturer, the provider, etc.

These solutions will most likely, not help. Here's one that will: Amazon app-store!

Click here from your Android: https://www.amazon.com/app-email and you will be able to access angry birds, words for free and all the other important apps you have been missing. You will need an amazon account for one click ordering, but then you're good to go.

Enjoy!

Fixing Android Music Player music order

Howdy!!

Its been a looong time since I've updated here, but I'm going to see if I can add things as they come up.

I recently got an Android phone, which is frakking awesome btw, however I discovered an annoyance with the Music player, in that it doesn't play the album tracks in the right order, but rather alphabetically!!

This is not a bug, but a feature (for real), and is designed to make searching easier for particular tracks. If you do want to play a particular album with the tracks in the right order, simply press and hold the album cover (in the album lists screen), and then select play (or play in order, depending on your android version - I have 2.2).

Awesomeness restored!!