I'm looking to have primary workbook (WorkbookA), open a second workbook (WorkbookB) and test for a value within it. Value to match is in WorkbookA, cell B3 and is the number 1. Range to test in WorkbookB is "A:A".
If the value is not found, WorkbookB is reopened for the user to edit (will optimize to reduce opening/closing, any ideas on getting user input to resume would be appreciated after editing Workbook B), and the code in WorkbookA loops and retests if WorkbookB is still open every 10 seconds.
After the user edits WorkbookB to ensure the value is present, they close it (any better way to signal they are complete is welcomed so I don';t have to close and reopen the files. They are small, so it's not an issue for speed, just seems inefficient).
The assumption I had was that the code would then detect the workbook was closed and then continue code execution, but the VBA is stopping as soon as I select the X in the top right corner of Workbook B.
Would prefer not having separate code in personal.xls file because of multiple users.
Thanks, Aaron
Code in Workbook A:
Global Const strWBb As String = "C:\Users\ashepherd\Desktop\WorkbookB.xlsx"
Global Const strRng As String = "A:A"
Sub Validate()
' ***************************** CHECK WORKBOOKB FOR 1 IN COLUMN A:A *****************************
' Verify presence on item in second workbook
searchItem = ThisWorkbook.Worksheets("Sheet1").Range("B3").Value
Do While Verify(searchItem, False) = False
Call Verify("", True)
Do While IsWorkBookOpen(strWBb) = True
endTime = DateAdd("s", 10, Now())
Do While Now() < endTime
DoEvents
Loop
Loop
Debug.Print "Workbook closed"
Loop
Debug.Print "search item found"
End Sub
Function Verify(item, OpenOnly As Boolean) As Boolean
' ****************************************************************************
' Open workbook B and verify that presence of item
' ****************************************************************************
Dim wbVerify As Workbook
Dim rng1 As Range
' ************************** OPEN FILE ************************************************
If IsFile(strWBb) Then
Set wbVerify = Application.Workbooks.Open(FileName:=strWBb, UpdateLinks:=False, IgnoreReadOnlyRecommended:=True) ' Open WorkbookB
wbVerify.Worksheets("Sheet1").Select
Else
MsgBox " File path incorrect. Unable to open.", vbCritical
Exit Function
End If
' ************************** TEST FOR ITEM ************************************************
If OpenOnly = True Then ' Only opening the file for read/write. Not testing values.
MsgBox "Opening workbook so values can be added. Close when additions completed."
Else
MsgBox ("Workbook B opened. Testing value for " & item & " in column A:A in Workbook B")
Set rng1 = Range(strRng).Find(item, , xlValues, xlWhole)
If Not rng1 Is Nothing Then
MsgBox item & " found !"
Verify = True
wbVerify.Close
GoTo item_found
Else
MsgBox (item & " not found in column A:A. Closing Workbook B. *****User will be promoted at this point to exit, or re-open the file to modify the values so search value is found in column A:A. Code SHOULD resume when Workbook B is closed. Currently VBA code execution in Workbook A is stopping when the 'X' is selected in top right window of Workbook B*****")
Verify = False
wbVerify.Close
End If
End If
Normal_exit:
Exit Function
item_found:
MsgBox "Verify code complete"
GoTo Normal_exit
End Function
Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function
Function IsFile(ByVal fName As String) As Boolean
'Returns TRUE if the provided name points to an existing file.
'Returns FALSE if not existing, or if it's a folder
On Error Resume Next
IsFile = ((GetAttr(fName) And vbDirectory) <> vbDirectory)
End Function
Final code:
Global Const strWBb As String = "C:\Users\ashepherd\Desktop\WorkbookB.xlsx"
Global Const strRng As String = "A:A"
Global Complete As Boolean
Sub Validate()
' ***************************** CHECK WORKBOOK_B FOR 1 IN COLUMN A:A *****************************
' Verify presence on item in second workbook
searchItem = ThisWorkbook.Worksheets("Sheet1").Range("B3").Value
Do While Verify(searchItem) = False
Complete = False
UserForm1.Show vbModeless ' USerform has a single button which changes the global "Complete" variable to true
Do While Complete = False
DoEvents
Loop
UserForm1.Hide
Debug.Print "Manual Edit Complete, retesting"
Loop
End Sub
Function Verify(item) As Boolean
' Modified to close only upon finding search item vs. reopening it.
' ****************************************************************************
' Open workbook B and verify that presence of item
' ****************************************************************************
Dim wbVerify As Workbook
Dim rng1 As Range
' ************************** OPEN FILE ************************************************
If IsFile(strWBb) Then
Set wbVerify = GetWorkbook(strWBb)
If Not wbVerify Is Nothing Then
Debug.Print wbVerify.Name
End If
wbVerify.Worksheets("Sheet1").Select
Else
MsgBox " File path incorrect. Unable to open.", vbCritical
Exit Function
End If
' ************************** TEST FOR ITEM ************************************************
Set rng1 = Range(strRng).Find(item, , xlValues, xlWhole)
If Not rng1 Is Nothing Then
Verify = True
GoTo item_found
Else
MsgBox (item & " not found in column A:A. A pop up form will show. Edit document and then hit RESUME button to continue checking. DO NOT exit via the close icon in the top right window of Excel as the code will stop running.")
Verify = False
End If
Normal_exit:
Exit Function
item_found:
'MsgBox (item & " found in WorkbookB, column A:A. Verify code complete")
wbVerify.Close Savechanges:=True
GoTo Normal_exit
End Function
Function IsFile(ByVal fName As String) As Boolean
'Returns TRUE if the provided name points to an existing file.
'Returns FALSE if not existing, or if it's a folder
On Error Resume Next
IsFile = ((GetAttr(fName) And vbDirectory) <> vbDirectory)
End Function
Public Function GetWorkbook(ByVal sFullName As String) As Workbook
' https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open, modified to add ignorereadonly and update links
Dim sFile As String
Dim wbReturn As Workbook
sFile = Dir(sFullName)
On Error Resume Next
Set wbReturn = Workbooks(sFile)
If wbReturn Is Nothing Then
Set wbReturn = Application.Workbooks.Open(FileName:=sFullName, UpdateLinks:=False, IgnoreReadOnlyRecommended:=True)
End If
On Error GoTo 0
Set GetWorkbook = wbReturn
End Function
