Archive for the ‘Workbook Object’ Category.

VBA Bug: Macro in Excel Stops After A Workbook.Open Command

Hi Everyone.

Although I have already reported this as a KB article sometime ago, I decided it to be useful to post this here too:

Sometimes Excel VBA behaves unexpectedly. Here is an example:

You are running code that is meant to open one or more files using the Workbooks.Open command. As soon as you hold down the shift key when this command is being processed, macro execution stops and your program is terminated entirely. This will even happen if you are e.g. editing an email message in Outlook whilst Excel VBA is processing the code in the background.

Read more on this page.

Regards,

Jan Karel Pieterse

Control When Events Are Handled

Events are a powerful aspect of Excel programming. They enable you to make your application respond to user actions such as entering data into cells or clicking the print button.

If your application uses events, you will probably also need to be able to control whether or not an event executes its code or not (e.g. to avoid event looping or to enable your code to do things you are preventing your user to do through the user interface).

This article shows a method to gain fine control over which event fires and which does not.

Alerts When Sharing and Unsharing

Sharing workbooks in Excel seems to be a disaster, at least to me. Yet the fact that so many people have problems with it must mean that they use it. Attempting to change a shared workbook in code will often cause the code to fail. So, like with protection, you have to unshare, do your thing, then re-share. This causes Excel to prompt the heck out of you about workbooks that already exist and losing change history. Fortunately, the DisplayAlerts property of the Application object handles these alerts.

'unshare
Application.DisplayAlerts = False
    ThisWorkbook.ExclusiveAccess
Application.DisplayAlerts = True
 
'Do stuff here
 
're-share
Application.DisplayAlerts = False
    ThisWorkbook.SaveAs , , , , , , xlShared
Application.DisplayAlerts = True

Opening Different File Types

To open files that belong to an application other than Excel, you can automate the other application. If you just want to open them, however, the FollowHyperlink method of the Workbook object is a better choice. Way less overhead. FollowHyperlink isn't just for HTML files, it works with any file. It has the added advantage of figuring out which application to open. I assume it uses the Windows settings to determine that.

Run a sub like this

Sub OpenFiles()

    Dim sPath As String
    
    sPath = "C:Documents and SettingsDickMy Documents"
    
    With ThisWorkbook
        .FollowHyperlink sPath & "db1.mdb"
        .FollowHyperlink sPath & "Mod.xls"
        .FollowHyperlink sPath & "WordsList.txt"
        .FollowHyperlink sPath & "Doc1.doc"
    End With
    
End Sub

and you can have a taskbar that looks like this

Followhlink