Selecting only visible cells in Excel

I found a neat solution to a problem I have manually been working around for quite sometime.

If you have hidden any rows or columns in Excel and want to copy the remaining data only, or if you have made use of the outlining tool and have created subtotals in your document and you only want to copy those subtotals then you can do so by following these steps;-

1. Select the data you want to copy
2. Press Ctrl+G to bring up the "Go To" window
3. In the bottom left corner click the Special button which will bring up the "Go To Special" window
4. From this window select "Visible cells only" 
5. Click OK

Now copy and paste the selection as you normally would.

Unable to create offline files in Windows 7

After clicking "Available offline" several times and enabling and disabling offline files I finally stumbled across a solution to make this work.

1. Disable offline files and reboot
2. Navigate to C:\Windows\ 
3. Take ownership of the CSC folder (you may need to also do this to any sub folders)
4. Delete the CSC folder
5. Enable offline files and reboot

Creating a sent item in Outlook without sending it

I was asked if it was possible to create an email within Outlook and make it look like it had been sent, without actually ever sending it. This question is very much in the grey area of "Don't ask why?"

After rummaging around on the Internet I couldn't actually find a reliable answer, but I remembered doing something similar with the nickname (.nk2 file) 

1. Open Outlook
2. Click File - Manage Rules & Alters
3. Click Next
4. At the bottom of the window under Start from a blank rule, select 'Apply rule on messages I send'
5. Click Next
6. Tick the box that says 'sent to people or public group' then in the bottom window click on the underlined text 'people or public group'
7. In the To box enter the email address you are sending to then click OK
8. Click Next
9. Tick the box that says 'move a copy to the specified folder' then in the bottom window click on the underlined text 'specified'
10. Select Sent Items and click OK
11. Click Next (twice)
12. Click Finish
13. You will be warned that this rule will only run when Outlook is running, click OK (twice)
14. Either Disable your wireless / Internet connection OR put make Outlook Work Offline.
15. Create a new email and send it.
16. As Outlook is offline the message should stay in your Outbox. Scroll through your folders until you find the Outbox, it should show that 1 message is unread. Click into the Outbox and then delete the email contained within.
17. Navigate to your Sent Items, this should also show 1 unread item which is the email you have just 'sent'
18. If you want to modify the date on a sent item then you can do so by modifying the date / time on your computer. 


I am currently still searching for a simpler solution, and am also looking out for some sort of add-in that may just create the sent object automatically without having to go thorough the steps outlined above

Connect Excel to Microsoft SQL Server and query Database

Open Microsoft Visual Basic for Applications (Alt+F11)

Click Tools – References

Add “Microsoft ActiveX Data Objects 2.7 Library”

Create a new module (I tend to keep all my SQL code in a module of its own e.g. mod_SQL)

Then create a new function or sub routine

Function myFunctionName(ByVal s_myString As String) As Integer

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim sConnString As String

'Create the connection string.

sConnString = "Provider=SQLOLEDB;Data Source=Address;" & _ 

"Initial Catalog=DBName;" & _

"User Id=Username; Password=Password"

'Create the Connection and Recordset objects.

Set conn = New ADODB.Connection

Set rs = New ADODB.Recordset

'Open the connection and execute. 

conn.Open sConnString

Set rs = conn.Execute("SQL Statement")

'Check we have data.

If Not rs.EOF Then

myFunctionName = rs("Return Value")

' Close the recordset

rs.Close

Else

myFunctionName= -1

End If

'Clean up

If CBool(conn.State And adStateOpen) Then conn.Close

Set conn = Nothing

Set rs = Nothing

End Function

Word / Office 2013 has stopped working

Annoyingly if you look around the web for Word / Office crashing on start up or "has stopped working" you will find thousands of posts all recommending the same thing; start word in safe mode and then disable COM Addins. 

After trouble shooting the issue for 3 hours and trying a variety of things I finally managed to resolve my problem. It seems my users issue was to do with their USB docking station (and the version of the Display link Adapter software they had installed)

I guess I could try upgrading the version of the software, but the user works in a different country so I don't want start uninstalling anything that may cause me to be disconnected. To get them up and running I was able to disable hardware acceleration for office, to do this

Run Regedit
Go to HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common
Create a new Key called Graphics
Under the new key create a new DWORD called DisableHardwareAcceleration
Set the DWORD value to 1

Office 2010 Activation error 0x80070190

An unspecified error has occurred. Your request cannot be processed at this time. Please try again later. (0x80070190)

I’ve had the above error message a bunch of times while trying to activate Office 2010, I’ve been able to work around the problem by using the telephone activation option provided by Microsoft but it’s a pain and takes about 5 minutes. I honestly can’t believe I’ve put up with problem for so long when the solution to my problem was fairly obvious and straight forward.

If you are dealing with this error try running the activation as Administrator (right click Word or Excel and select Run as Administrator)

Worked first time for me.

SQL Backup And FTP

Since moving to SQL Server 2005/2008 (including SQL Express at home) I've been using SQL Server Management Studio to create a daily database backup. In my home environment my databases are very small (mostly development projects) so I place these backups into a folder than is configured to synchronize with the cloud. I'm not too worried about full server backups as the database server has minimal configuration done to it. In my business environments I have daily backups that backup the full system (including my SQL backup files). 

Despite using the Management Studio for nearly 9 years now I know very little about it, so perhaps it has the functionality built within it and I just don't process the knowledge (nor did my search results) on how to backup SQL databases to a network location, I also wasn't really in the mood to go fiddling with scripts etc.

I came across a great bit of software called SQL Backup And FTP (http://sqlbackupandftp.com/) 

The most basic (free) version of the software has a lot of functionality and setting up a job is easy.  

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.

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.

You should only open attachments from a trustworthy source

I have dealt with an issue today that took me a little while to figure out.

A user using Outlook 2007 was getting prompted every time they opened an attachment to either save or open it.

"You should only open attachments from a trustworthy source"

On my computer this seemed like a simple fix, unchecked the box that says "Always ask before opening this type of file" However on my end users PC this option was grayed out. Turns out that Outlook needs to be running in Administrator mode. This lead me to my next problem, all their Outlook shortcuts lacked the "Right Click - Run As Administrator" option. So I created a new shortcut for the user on their desktop pointing it to C:\Program Files\Microsoft Office\Office12\Outlook.exe they now have the option to Run Outlook as an administrator.

Corrupt profiles Windows 7 (Domain users)

A client has been having some issues with their laptop, installers have been complaining that their ‘My Documents’ folder (redirected to H:\) doesn’t exist. After pushing and poking some settings I decided the quickest and easiest thing to do would be to recreate their user profile.

Reboot the system and login as another use (preferably Administrator) then browse to C:\Users and rename the user’s folder.

Normally when the user logs in next Windows will create a profile automatically, however under Windows 7 it decided to create a temporary profile.

It turns out Windows 7 stores the path that a user should be using for their profile under the following registry folder;-

HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList

Look through the various folders listed until you find the one with your pesky profile, delete it and then login as normal.