Archive for the ‘Workbook Object’ Category.
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
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.
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
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
