title: Working Around the Workbooks.Open Bug in VBA
body: <p>I'm trying to transfer data from one unique workbook to another unique workbook (i.e., the filenames are static).  I found some sample code which created 2 variables of type workbook (each representing a sample workbook) and then set those variables equal to Workbooks.Open("Filename").  I tried implementing this only to find that my code stopped executing immediately after the first workbook.open("filename") variable definition.  Upon searching for the answer for this, I realized that this is a bug in VBA created by a false detection of a depression of the Shift key.  There is some code on Microsoft's support website that basically won't let your code run if the shift key is depressed.  I implemented this code and, sure enough, the Shift key is being detected as being physically depressed when it in fact isn't.  This seems to be the only available "help" I've found on this topic so far.  I can't seem to figure out an alternative to workbooks.open to set my workbook variables equal to unique filepaths.  First, is there a workaround for this bug so that the Shift key isn't being detected as being depressed?  Or, second, is there another way to set my workbook variables equal to specific filepaths without using the workbooks.open feature?</p>
  
  <p>Thanks in advance for your help.</p>
  
  <pre><code>'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) &lt; 0
  End Function
  
  Public Sub Initialization()
      Do While ShiftPressed()
          Application.ScreenUpdating = False
          DoEvents
          'Public Variable Definition
          'Set Tracking = Workbooks.Open("\\Server2013\NETWORK_SHARED\QC SHARED\P&amp;Q Tracking New Template.xls")
          Set Tracking = Workbooks.Open("\\Qc\shareddocs\P&amp;Q Tracking New Template.xls")
          'Set Data = Workbooks.Open("\\Server2013\NETWORK_SHARED\QC SHARED\Production &amp; Quality Raw Data.xls")
          Set Data = Workbooks.Open("\\Qc\shareddocs\Production &amp; Quality Raw Data.xls")
          DataLastRow = Data.Sheets("P&amp;Q Raw Data").UsedRange.Rows.Count
          WS_Count = Tracking.Worksheets.Count
          Day_Array() = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
          Name_Array() = Array("Angel", "Tony", "Bandy", "Jorge", "Ray")
          'Unprotect Sheets
          For WS_Iter = 1 To WS_Count
              With Tracking.Worksheets(WS_Iter)
                  .Activate
                  .Unprotect
              End With
              'Tracking.Worksheets(WS_Iter).Activate
              'Tracking.Worksheets(WS_Iter).Unprotect
              'ActiveSheet.Unprotect
          Next WS_Iter
          'Clear Contents
          Sheets("P&amp;Q Weekly Summary").Activate
          For WL_Row_Num = 24 To 72 Step 12
              Sheets("P&amp;Q Weekly Summary").Range(Cells(WL_Row_Num, 3), Cells(WL_Row_Num + 4, 6)).ClearContents
              Sheets("P&amp;Q Weekly Summary").Range(Cells(WL_Row_Num, 10), Cells(WL_Row_Num + 4, 10)).ClearContents
          Next WL_Row_Num
          'Reprotect Sheet 1
          Sheets("P&amp;Q Weekly Summary").Protect UserInterfaceOnly:=True
          'Variable Initialization
          WL_Row_Num = 0
          WS_Num = 0
          SBName_Row_Num = 12
          Name_Row_Num = 20
          Weekly_Score_Row = 24
          'Userform Input
          Vacation_Options_Form.Show
      Loop
  End Sub
  </code></pre>
  
  <p>I did some altering of my code and found an interesting observation.  Below is a snippet of my streamlined code:</p>
  
  <pre><code>Public Sub Initialization()
  Application.EnableEvents = True
  Application.ScreenUpdating = False
  Set Data = Workbooks.Open("\\Qc\shareddocs\Production &amp; Quality Raw Data.xls")
  MsgBox "The Data Workbook Is Now Open."
  Set Tracking = Workbooks.Open("\\Qc\shareddocs\P&amp;Q Tracking New Template.xls")
  MsgBox "The Tracking Workbook Is Now Open."
  DataLastRow = Data.Sheets("P&amp;Q Raw Data").UsedRange.Rows.Count
  WS_Count = Tracking.Worksheets.Count
  Day_Array() = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
  Name_Array() = Array("Angel", "Tony", "Bandy", "Jorge", "Ray")
  </code></pre>
  
  <p>After further experimentation I realized that the code does execute beyond beyond the Data workbook declaration if I comment out the Tracking workbook definition but will not execute beyond the Tracking workbook declaration if I do not comment that out.  The Tracking workbook is the one I have initially opened, if that makes a difference.  I have isolated the problem to this.  The Workbooks.Open command doesn't seem to work if the workbook you're trying to open is already open and is the primary workbook from which you're executing code.  Does this seem right?  Is this the bug?  All I'm looking to do is to formally declare my workbooks as unique variables.  Is there another way to do that besides Workbooks.Open?  Please help.  Thanks.</p>