Working Around the Workbooks.Open Bug in VBA

Question

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?

Thanks in advance for your help.

'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

Public Sub Initialization()
    Do While ShiftPressed()
        Application.ScreenUpdating = False
        DoEvents
        'Public Variable Definition
        'Set Tracking = Workbooks.Open("\\Server2013\NETWORK_SHARED\QC SHARED\P&Q Tracking New Template.xls")
        Set Tracking = Workbooks.Open("\\Qc\shareddocs\P&Q Tracking New Template.xls")
        'Set Data = Workbooks.Open("\\Server2013\NETWORK_SHARED\QC SHARED\Production & Quality Raw Data.xls")
        Set Data = Workbooks.Open("\\Qc\shareddocs\Production & Quality Raw Data.xls")
        DataLastRow = Data.Sheets("P&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&Q Weekly Summary").Activate
        For WL_Row_Num = 24 To 72 Step 12
            Sheets("P&Q Weekly Summary").Range(Cells(WL_Row_Num, 3), Cells(WL_Row_Num + 4, 6)).ClearContents
            Sheets("P&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&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

I did some altering of my code and found an interesting observation. Below is a snippet of my streamlined code:

Public Sub Initialization()
Application.EnableEvents = True
Application.ScreenUpdating = False
Set Data = Workbooks.Open("\\Qc\shareddocs\Production & Quality Raw Data.xls")
MsgBox "The Data Workbook Is Now Open."
Set Tracking = Workbooks.Open("\\Qc\shareddocs\P&Q Tracking New Template.xls")
MsgBox "The Tracking Workbook Is Now Open."
DataLastRow = Data.Sheets("P&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")

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.

Answer 1

So in my case the issue is that the open method does not open the file and no errors, plus code does not stop/break. Setting objWB = Workbooks.Open(...) just returns Nothing.

I figured that the open method does not work only if you are calling it from a function. It works as expected when I used it in a method.

My workaround was just to use an external app. Like writing code in VB or C#.

Here is quick c# code that gets row counts from multiple files:

using Microsoft.Office.Interop.Excel;
using System;

namespace ExcelRowCounter
{
    class Program
    {
        static void Main(string[] args)
        {
            foreach (string arg in args)
            {
                Console.WriteLine("{0}|{1}", arg, GetWorkbookRowCount(arg, false));
            }
            Console.WriteLine("Completed");
            Console.ReadKey();
        }

        static int GetWorkbookRowCount(string strWorkbookPath, bool booAllSheets)
        {   
            int retVal = 0;
            try
            {
                Application objExcelApplication = new Application();
                objExcelApplication.Visible = false;
                Workbook objWorkBook = objExcelApplication.Workbooks.Open(strWorkbookPath, ReadOnly: true);

                foreach (Worksheet objSheet in objWorkBook.Sheets)
                {
                    retVal = objSheet.Rows.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
                    if (booAllSheets == false)
                    {
                        break;
                    }
                }

                objWorkBook.Close(false);
                objWorkBook = null;

                objExcelApplication.Quit();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Bah! Find code and debug!");
                Console.WriteLine("Filepath: " + strWorkbookPath);
                Console.WriteLine("Error: " + ex.Message);
            }
            return retVal;
        }
    }
}

Example of issue:

Function GetWorkbookRowCount(ByVal strWorkbookPath As String) As Integer

    Dim retVal As Integer: retVal = -1
    Dim objWorkBook As Workbook

    Set objWorkBook = Excel.Workbooks.Open(Filename:=strWorkbookPath, ReadOnly:=True) ' Fails to open file here

    If (objWorkBook Is Nothing) Then
        'Failed to open file
        Debug.Assert False

    Else
        retVal = objSheet.Rows.SpecialCells(xlCellTypeLastCell).Row

        objWorkBook.Close
        Set objWorkBook = Nothing

    End If

    GetWorkbookRowCount = retVal

End Function

Example of (partial, since it does not work in functions!) solution:

Sub PrintWorkbookRowCount()

    Dim retVal As Integer: retVal = -1
    Dim strWorkbookPath As String: strWorkbookPath = "c:\temp\exportFile.csv"
    Dim objWorkBook As Workbook: Set objWorkBook = Workbooks.Open(Filename:=strWorkbookPath, ReadOnly:=True)

    retVal = objSheet.Rows.SpecialCells(xlCellTypeLastCell).Row

    objWorkBook.Close
    Set objWorkBook = Nothing

    Debug.Print (strWorkbookPath & "|" & retVal)

End Sub

Answer 2

I ended up solving this by not using the Workbooks.Open feature at all. I just referred to my base workbook as ThisWorkbook and avoided the issue entirely. I was trying to avoid using a predefined variable such as ThisWorkbook for the desired workbook but in the end this seemed to be the only available, or at least the best, option.