CODEWITHSUNDEEP
vbaexcel
NorwegianLatte
NorwegianLatte

Reputation: 89

continue if the workbook is already open

Hello I am working on "Packing list Form (PL)" have a set of codes which opens a "Tracing Form", and copy whatever that is on there to "Packing list Form (PL)"

but when I run the code the second time, "Tracing Form" is already open, so it prompts me saying

""Tracing Form" is already open. reopening will cause any changes you made to be discarded. do you want to reopen "Tracing Form"?"

I want it to say No and continue with the rest of the code !!

please help... here's part of the code that's relevant

Set PL = Workbooks("PACKING LIST FORM").ActiveSheet    
userprofile = Environ$("userprofile")

Workbooks.Open userprofile & "\Dropbox\Tissue Tracing Form"

Set tracingform = Workbooks("Tissue Tracing Form.xlsx").Worksheets("2018_1")
ActiveWindow.WindowState = xlMinimized 

'execution
from_lastrow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).row
.
.
.
.

Upvotes: 0

Views: 938

Answers (1)

TisButaScratch
TisButaScratch

Reputation: 173

The function below allows you to check if a workbook is already open and assign xRet a boolean value, you can then use that within a case to run the rest of your code or open the workbook and then run the rest of your code. This will avoid you trying to open the workbook when it is already open and so you wont get that pop up message.

Sub CheckExcelOpen()

Dim myPath As String
Dim xRet As Boolean

    xRet = IsWorkBookOpen(Tissue Tracing Form.xlsx)
    If xRet Then
        'YOUR CODE WHEN ALREADY OPEN
    Else
        Workbooks.Open (TissueTracingFullFilePath) 'action needs the full file path to open
       'YOUR CODE ONCE OPEN
    End If

End Sub

The function-

Function IsWorkBookOpen(Name As String) As Boolean
    Dim xWb As Workbook
    On Error Resume Next
    Set xWb = Application.Workbooks.Item(Name)
    IsWorkBookOpen = (Not xWb Is Nothing)
End Function

I hope this helps

Upvotes: 1

Related Questions