Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Home > English site > Articles > Workbook Open Bug

Macro in Excel Stops After A Workbook.Open Command

Introduction

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.

Cause

Excel is designed not to run Auto_Open and Workbook_Open code when a workbook is opened from the User interface whilst holding down the shift key. Unfortunately, this (desired) behaviour also applies when opening workbooks through VBA code.

Microsoft has confirmed this is a bug.

Resolution

Before opening a workbook through code, detect if the shift key is pressed and wait until it is released. The code sample below shows you how to do this:

 Option Explicit

'Declare API
Declare Function GetKeyState Lib "User32" _
(ByVal vKey As Integer) As Integer
Const SHIFT_KEY = 16

Function ShiftPressed() As Boolean
'Returns True if shift key is pressed
    ShiftPressed = GetKeyState(SHIFT_KEY) < 0
End Function

Sub Demo()
    Do While ShiftPressed()
        DoEvents
    Loop
    Workbooks.Open Filename:="C:\My Documents\ShiftKeyDemo.xls"
End Sub

This problem has also been listed in the Microsoft knowledge base:

Macro in Excel Stops After A Workbook.Open Command, February 26, 2005


Comments

Showing last 8 comments of 39 in total (Show All Comments):

 


Comment by: Jan Karel Pieterse (6/11/2013 9:13:49 PM)

Hi Sew,

First thing to check is whether that path and workbook name is really correct and the file is actually there.

 


Comment by: Sew (6/11/2013 9:19:57 PM)

Thank you for the response. I was able to figure it out. When I last wrote the code, I was using .xls files. Excel 2007 saves files in .xslx. This gave the run-time error 1004 because I was referring to workbook.xls instead of workbook.xlsx.

 


Comment by: mohd hamdan (10/13/2013 7:37:19 AM)

Hi

actually i have some excel sheets which they are protected, i found that other people are able to break the password with the “password breaker” well known code.

is there any way i can prevent them from breaking my sheets.

appreciate your help

thanks

 


Comment by: Jan Karel Pieterse (10/13/2013 4:56:43 PM)

Hi Mohd,

Setting a password that is needed to be able to open the file is relatively secure compared to the worksheet passwords. But nothing is so secure that it cannot be cracked.

 


Comment by: Brian K (10/26/2013 8:25:50 AM)

Hi,

I have a code will read a xxx.txt from my web server and oepn the workbook by "Workbooks.Open(FileName:=path & File)", it is fine in XP + Office 2003, but now in WIN7 + Office 2010, it is a long time waiting to download that text file (xxx.txt), would you please help, how can I fix it ?

 


Comment by: Jan Karel Pieterse (10/27/2013 5:43:06 PM)

Hi Brian,

Excel 2010 has stronger security regarding getting data from Web locations. I'm afraid I can't really help to improve the performance here.

Perhaps using a bit of VBA code that first downloads the file and then opens it from the hard disk?

 


Comment by: Akshat Bhatt (11/26/2013 7:43:41 AM)

Hi,

There is a macro been developed in an excel, problem is when I run that macro all other excel files gets open from where data is getting populated. I know this is happening due to the use of Workbook.Open command. But thing is I have to stop other files fom getting opened while I execute the code. How do I disable Workbook.Open command.


Thanks & Regards
Akshat

 


Comment by: Jan Karel Pieterse (11/26/2013 7:55:09 AM)

Hi Akshat,

Without seeing (part of) your code it is impossible to answer your question.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].