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

PowerPoint 2010 video problems "Microsoft Office PowerPoint Has Stopped Working"

I encountered a frustrating problem today, I’ve still not really worked out a “final” solution but here’s what I discovered.

A user has a number of power point presentations with videos linked (possibly embedded within) the videos are of mixed format.

The videos and presentations run perfectly on their laptop but on their assistants they do not. In fact on their assistance laptop Microsoft PowerPoint crashes when you try and open the presentation with the following error message “Microsoft Office PowerPoint Has Stopped Working”

I was able to open the presentations with PowerPoint 2003 (using Microsoft Compatibility pack), the base files are in 2007 format (.pptx), if PowerPoint 2003 won’t open the pptx file and you have the following error “No text converter is installed for this file type” simply download service pack 3 for Office 2003.

So people have suggested that once open in 2003 that you could try saving the file as a ppt, this works however I am concerned about what video functionality we may lose by doing this.

I found that by uninstalling VLC media player that I could now open the presentations in PowerPoint 2010 however I am unable to view any of the videos nor am I able to view any of the video properties. I managed to fix this by installing an earlier version of VLC media player (I opted for version 1.0.0 available from VLC’s ftp site http://download.videolan.org/pub/videolan/vlc/)

It seems that the videos in this presentation are “linked” a new feature in 2010 is embedded videos, I think I will investigate this and see if embedding the media resolves this issue.

Follow Up: Exchange / Outlook Nickname (NK2) contacts syncing to iPhone

OK, so in my last blog posted I talked about an annoying problem with setting up contacts with the iPhone and Exchange 2010 and while the work around I posted hid a lot of unnecessary contacts they are still actually on the device. This causes a problem for a lot of my mobile users as a number of them sync their contacts via blue tooth with their in car systems.

The first time I had one of them do this they reported that they had 1000+ contacts listed on their in car system.

A previous work around I had used for the BlackBerry was to move all the contacts to a secondary address book and then have them store only telephone numbers in their main 'Contacts' address book within Outlook. On the iPhone this doesn't work.

So here's how I solved this problem (or at least this is my work around)

Within Outlook export all the contacts from the address book to a CSV file.

Go to GMail and import the CSV

From GMail export the contacts to a vCard format.

Go to iCloud and import the vCard

Now setup your iPhone to only sync contacts from iCloud and not Exchange.

Seems a little messy really, Apple should really come up with a fix for this, as when my users add new contacts they will need to do it via the device/iCloud and then also in Outlook.

Exchange / Outlook Nickname (NK2) contacts syncing to iPhone

So I'm fairly new to iPhone (having had a BlackBerry for 4 years). Since setting my iPhone 5 to sync contacts, email etc with Exchange 2010 I've had a rather annoying issue, it seems that the iPhone picks up the "Suggested Contacts" folder, this means that my iPhone now has a contact for every single person that I've ever send an email to.

I read a couple of articles which suggest the way to fix the issue is to disable the suggest contacts, for me this would work fine, however the majority of my users use the suggested contacts. The solution is actually rather simple.

On the iPhone go to Contacts
Go to Groups
Check all the address books that you don't want to sync.