Excel “Blue Bar” problem / Excel “blanked window” problem

A really annoying problem that sometimes occurs after running a macro is that the left hand side of the Excel window goes blue (the same colour as the border ?)

I found nothing that would kelp until I came across this:

http://support.microsoft.com/kb/905208, it’s for Excel 2002, but I tried it in Excel 2003 and it works.  Better , I haven’t found a down side yet.  I’ve had it installed since the end of June 2011, and am now preparing to roll it out.  Here are the updated command to make it apply to Excel 2003

1. Exit Excel 2003.
2. Click Start, click Run, type regedit, and then click OK.
3. Locate and then click the following registry subkey:
   HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\11.0\Options
4. After you select the subkey that is specified in step 3, point to New on the Edit menu, and then click DWORD Value.
5. Type DisableToolbarRedrawToggle, and then press ENTER.
6. Right-click DisableToolbarRedrawToggle, and then click Modify.
7. In the Value data box, type 1, and then click OK.
8. On the File menu, click Exit to quit Registry Editor.

Becareful editing the registry, but you know that don’t you.

Windows Server 2008 R2 WindowsUpdate_80072F8F

A brand new build of the server and I cannot get Windows  Update to work.  Everyehere I look the responses talk about setting the time and time zone correctly.  Well the server did have the wrong time after it was installed (but not before!).  So I corrected it, and checked the date and the year in particular.  All OK.  There suggestions in various articles that you should use the Windows Update web site. These are just ridiculous – why do people say that, it just points straight back to the control panel applet.

The I tried to load Microsoft.com – it took ages and didn’t look complete.  That reminded me.

I hadn’t set the MTU value.  As most of the Microsoft sites can’t (or won’t) negotiate, this must be set in the registry.  I don’t know of any tool that will do this for you.

Here’s a quick summary:

Locate HKLM\System\CurrentControlSet\Services\TCPIP\Parameters\Interfaces
Locate the interface with the current IP address
Add a DWROD value, name it MTU, set the value to 1472 (decimal)
from http://www.annoyances.org/exec/show/article04-107, which will also give clues about how to calculate the value.

Windows cannot access the specified device, path, or file. You may not have the appropriate permissions to access the item.

Fisrt check the permissions.  Yes, I have full control, all the boxes are ticked.

I’ve had this problem before, but now I have a hotfix that won’t install, and I need it, so have to run it.

This article http://social.technet.microsoft.com/Forums/en-US/exchangesvrclients/thread/1b903da5-2740-440b-a1f1-de354a5c310e/ suggests that you should uninstall the Internet Explorer Enhance Security component.  Well, its sometime necessary, but is to be avoided.

 

I managed to run the hotfix by copying it to the root of C: and typing the name into the command prompt.

 

Cannot find resource.dll

Locate ‘C:\Program Files\Yahoo!\Messenger’ and double click on each of the files:

default.reg

intl.reg

Yahoo Messenger will now start correctly.

Start the application and sign in.

Removing Quotes from Quoted Strings in Windows batch (or .cmd)

Passing strings between batch files or called routines means they inevitably have to be enclosed in double quote marks (e.g. “String to pass” ) if they are required in one variable.

Once the receiver gets hold of the string and tries to embed it in another string the quotes must be removed.

This simple approach comes from: http://ss64.com/nt/syntax-esc.html.

   :: Remove quotes
   SET _string=###%_string%###
   SET _string=%_string:"###=%
   SET _string=%_string:###"=%
   SET _string=%_string:###=%

The above will only work for non-null strings.  There is a link at the site for a more detailed solution.

Avoid using solutions involving %1 %2 %3 etc as they eliminate double spaces.

Microsoft.Office.Interop.Excel leaves processes in the task list

I’ve migrated some code from vb2005, via vb 2008 to vb 2010.  I’ve changed the program quite a lot but the Excel class has not been changed.  Now when I run the program one or more excel.exe’s are left in the tasklist.  This used to happen rarely in vb 2005, the vb 2008 version was never run in anger, and it happens all the time in vb 2010.  I’ve read lots of articles on the web saying I must dispose of all the objects, well maybe I should, but …
( the logic works, why should I have to worry, all child objects should be cleared by the xl=nothing at the end)

So it’s poor coding, or perhaps a poor compiler take your choice.

I just need it to work properly – to not leave excel.exe behind.

The key parts or the following code are lifted from http://www.craigmurphy.com/blog/?p=82 comment #11 by Mahdi and converted into vb:

Public Class ExcelAccess

Dim xl As Microsoft.Office.Interop.Excel.Application
Public xlHashtable As New Hashtable()      ‘ Instance has table for existing execl
‘ requires rescouce from COM (add reference): Microsoft Office 11.0 Object Library 2.3, Microsoft Excel 11.0 Object Library
Public Sub New()
CheckExcellProcesses()          ‘ Store existing processes in the Hastable

End Sub

Public Sub ExcelNew()

xl = CType(CreateObject(“excel.Application”), Excel.Application)
xl.Workbooks.Add()
xl.Visible = True

End Sub

Public Sub ExcelQuit()
xl.Application.Quit()
xl = Nothing
KillExcel()
‘Unload(Me)
End Sub

‘ Converted from c

Private Sub CheckExcellProcesses()
Dim AllProcesses As Process()
AllProcesses = Process.GetProcessesByName(“excel”)

Dim iCount As Integer = 0

For Each ExcelProcess As Process In AllProcesses
xlHashtable.Add(ExcelProcess.Id, iCount)
iCount = iCount + 1
Next

End Sub
Public Sub KillExcel()

‘ Remove any excel’s started since this process atarted
Dim AllProcesses As Process()
AllProcesses = Process.GetProcessesByName(“excel”)

‘ check to kill the right process
For Each ExcelProcess As Process In AllProcesses

If (xlHashtable.ContainsKey(ExcelProcess.Id) = False) Then
ExcelProcess.Kill()
End If
Next
AllProcesses = Nothing

End Sub

 

Cannot delete internet shortcuts (.url) – Access denied

I deleted a directory on my server, it left 3 files behind.  I cannot access the three files, whatever I do.  Everything responds ‘Access Denied’.  If I try to double click the shortcut I am told that  it is an invalid shortcut.  Properties show the file information, but the Security tab is missing.

Initially I suspected the disk and ran a CHKDSK /F, this caused the volume to be dismounted.  It solved the problem but it’s a lot of effort and some down time.

Then I found this http://vkirshin.blogspot.com/2011/01/cannot-delete-files-from-favorites.html.  I opened explorer to show the offending files and stopped the indexing service.  The offending files immediately disappeared.  I started the service again and they are still gone.

I believe this is a recent issue, but I don’t have time to investigate further.

Changing the Footnote separator width in word 2003

It’s something I do rarely, so can never remember.

The key is to be in ‘Normal’ view (not ‘Print Layout’ view).  Once there choose ‘View Footnotes’, and then select the ‘footnote separator’ option.

You can the replace the separator, but you cannot change the size of the default provided.

For example, Choose ‘Insert … Picture >’, clisk ‘Clip Art’, and type ‘dividers’ in the box.  This will let you choose a graphic which CAN be resized.

Loads more details here: http://sbarnhill.mvps.org/WordFAQs/NoteSeparators.htm

Maximum Number of Recent Documents

15 in the Recent documents list is way too low, I can get through that in an hour.  What’s mor the directory holds hundreds, but the menu only displays a few.  I’ve been trying to change it for a while – on and off.

Now I have tried the Group Policy Editor setting

To get to the group policy editor use Start … Run … , the type gpedit.msc and press return.

User Configuration\Administrative Templates\Windows Components\Windows Explorer

Then click “Maximum Number of Recent Documents”, Click Enabled, then set a number.

Error -2147417848 (&H80010108): The object invoked has disconnected from its clients.

I have a spreadsheet that is used for data entry.  In the Workbook_Open Sub on the ThisWorkbook sheet I set up some validation.

Dim Con_ws As Worksheet
Set Con_ws = Worksheets(“Contract”)
Dim frDt As Date, toDt As Date
frDt = DateAdd(“m”, -6, Now())
Con_ws.Cells(1, 1).Value = frDt
toDt = DateAdd(“m”, 6, Now())

With Worksheets(“Contract”).Cells(1, 1).Validation
.Delete
.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Format(frDt, “mm/dd/yyyy”), Formula2:=Format(toDt, “mm/dd/yyyy”)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = “”
.ErrorTitle = “”
.InputMessage = “”
.ErrorMessage = “”
.ShowInput = True
.ShowError = True
End With

and lots more besides.  On SOME workstations the macro fails with the Error -2147417848, entering Debug the .Add Type= line is high lighted.

I have read http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319832 and it doesn’t seem to apply to the code above.

Trying to work out the cause, I re-recorded the macro that adds the validation:

Range(“A1″).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=”12/31/2009″, Formula2:=”12/31/2010″
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = “”
.ErrorTitle = “”
.InputMessage = “”
.ErrorMessage = “”
.ShowInput = True
.ShowError = True
End With

That runs fine on the affected workstation.  If I put that code in before my own, all of it works everywhere.

Why?

I suppose it must be something to do with cell selection but I don’t understand what.