2

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.

Improve this question
8
  • I think I have answered a similar question such as yours... one moment. let me re-confirm... I am growing old you see :D Commented Feb 4, 2014 at 19:18
  • 4
    Found it. Does THIS help? Commented Feb 4, 2014 at 19:19
  • 1
    @jlynn303 - It would help to show your actual code, including the Shift key work-around. Commented Feb 4, 2014 at 19:30
  • I think the OP may prematurely assume he's ran into this bug. The code @jlynn303 is using would really help us to come up with an alternative if Sid's other answer doesn't already answer this. Commented Feb 5, 2014 at 8:28
  • I tried the similar thread and I realized that I didn't put in the 'DoEvents' line of code; however, even after I put that in before my workbooks.open code, nothing changed. Below is my code. I apologize if it isn't in the correct format since I don't see how to add a code block in a comment. Commented Feb 5, 2014 at 12:38

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.