Add Header logo / image to blogger

Not sure how I missed this when I created this new blog but adding an image to your blog header is a lot easier than I first thought. I searched for how to do it and a number of people suggested adding a image widget but it turns out Blogger has already provided the functionality to do this.

1) Select Layout

2) On the header click Edit

3) Select your header image and options

GoDaddy PHPMailer Relay problems

This has been very frustrating, if you search for GoDaddy and PHPMailer you'll find loads of posts about how PHPMailer needs to be configured in order to work with GoDaddy's hosting.

I tried using some of the configuration information I found online but I wasn’t having any luck. I kept getting “Error: The following From address failed:” despite trying several different addresses (including valid addresses that I had setup on GoDaddy’s cPanel)

I finally came across Aravind is Online blog which nudged me in the right direction (http://aravindisonline.blogspot.co.uk/2012/01/phpmailer-with-godaddy-smtp-email.html)

For completeness.

You cannot use an external host to relay email messages. You must use the following host: relay-hosting.secureserver.net  

I setup a mail user specifically for PHPMailer (I am hosting a subdomain so all my email services are elsewhere)

Use PORT 25 (numerous guides suggest 465)

Set SMTP Auth to true, Aravind’s guide suggests to set this to false, however this didn’t work for me.

US / UK Date format reverse in Excel

This problem has been bugging me for 3 days now; I am retrieving a date time from SQL. I have verified that the date is in my required format dd-mm-yy, and I have tried a few different ways of selecting the date just to be sure DATEPART(day, fieldname) , DATEPART(month, fieldname), I have even tried dd-MMM-yy.

If I display the date on a form or in a messagebox it displays fine, the moment I put the date value into an Excel cell it flips the day and month around. Originally I was going to work around the issue by prefixing all my dates with a single quote ‘ so that Excel treats them as being strings, however the end user wants to sort on some of the date values and it won’t work quite right as a string.

After banging my head against the wall for 3 days I finally came across the DateValue(date) function! Figured I’d share the solution to this frustrating little problem.

Excel 2010 .xlsm File hangs when opening

I've been writing some VBA macros to improve an Excel workbooks functionality. I was modifying a Sub Routine and just in case I made a mistake and need to revert back to it I made a copy and appended _backup to its name. As it happens I decided I preferred the original way the sub routine worked so I restored it (deleted the original routine) and then renamed the _backup. Turns out I had accidentally copied the routine twice (VBA didn't warn me of this). Thinking everything was fine I saved and closed my workbook, when I came back to it a few hours later I was unable to open it, Excel 2010 just sat at 100% without allow me into the workbook.

I found a few suggestions online to fix this and none worked, so here's what I did to get mine working.

Change the extension of the file from .xlsm to .xls and then open the file. I got prompted that there was a file type mismatch (or something similar) and it also gave me a complication error, something along the lines of problem with sub routine. As soon as I saw the sub routine error I knew where my coding problem was, corrected it and then saved the file back as .xlsm.

I hope this helps someone out, had me in a panic for a few moments.

Excel cannot complete this task with avaliable resources.

 

 

If You've ever come across the above error when trying to open an Excel workbook ("Excel cannot complete this task with available resources. Choose less data or close other applications") then you can resolve it by increasing its priority in Windows.

 

 

With Excel still running, open task manager and locate the Excel.exe process, right click on it and navigate to priority, normally it should be set to normal, if you increase this to "Above Normal" you should be able to open your problematic workbook. I am not sure of the underlying cause of this and I've only seen this problem when running Excel 2010, it is defiantly not a hardware/resource issue.

Outlook 2010 - Finding a misfiled email

Every so often I get a phone about finding lost or misfiled emails. Normally the user has accidentally dragged the email to a folder and misfiled it.

Luckily the search facility in Outlook 2010 is pretty good.

Click on your Inbox and then into the "Search Inbox" field. The toolbar at the top of the screen should now display some search options, the default is to search "Current Folder", change this to "All Subfolders"

99% of the time this will find the email I am looking for. Sometimes the user will want to know what folder the email was misfiled in, simply double click on the email and then click on the File tab. Under the "Move to Folder" option it should display "Current Folder: "

auto increment (auto_increment) in Excel with VBA (auto generate line numbers)

I was asked a question the other day, I'm still not quite sure why this was important to the user (see as Excel has row numbers provided automatically) and its also very easy to type the numbers and then drag down / fill to generate the next number, anyway the user had a large Excel document that they wanted to have automatic line numbers at various points (almost like a list or bullet points in Word)

I knocked up a quick macro that seems to do the job for the user

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

     

    If Target.Column > 1 And Target.Column <= 7 Then

   

        If Target.Row - 1 > 0 Then

            Cells(Target.Row, 1).Value = Target.Worksheet.Cells(Target.Row - 1, 1) + 1

        End If

      

    End If

End Sub