CODEWITHSUNDEEP
vbaexcel
Lobsterguy
Lobsterguy

Reputation: 93

Checking if workbook is open after making changes in the source book with Excel vba

I made a macro that transfers data from one book to another. If the source book is not opened macro opens it, copies the data and then closes it so that the user does not see what happened. Now for the problem - if the source book is already opened it needs to stay opened after the operation. So, I made an If statement that uses this function I found here:

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

and it works the first time, but if I start the macro again right after the first time it acts like the file is not open and closes it when it finishes the operation...

If IsWorkBookOpen(ActiveSheet.Range("AA1").Value) Then

Set Invoice = Workbooks(ActiveSheet.Range("AA4").Value)
openF = True

Else
openF = False

Set Invoice = Workbooks.Open(ActiveSheet.Range("AA1").Value, True, True)
End If

For Each sheet In Invoice.Worksheets
If sheet.Range("B2").Value = "active" Then
    newD = vbNull
    lRow = sheet.Cells(2000, 7).End(xlUp).Row
    counter = sheet.Cells(2000, 1).End(xlUp).Row

totalR = num

For k = 7 To counter

        If sheet.Cells(k, 6).Value = "n" Then

            sheet.Range("A" & k, "F" & k).Copy
            rep.Range("A" & num, "F" & num).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

            rep.Cells(num, 7) = newD
            If Year(rep.Cells(num, 7).Value) = Year(1) Then rep.Cells(num, 7).Clear
            newD = DateAdd("d", sheet.Cells(3, 2).Value, newD)
            num = num + 1
        End If

Next k

rep.Cells(num, 4) = "Total"
rep.Cells(num, 5) = Application.Sum(rep.Range("E" & totalR, "E" & (num - 1)))
rep.Cells(num, 5).NumberFormat = "$#,###0"

num = num + 3

End If

Next sheet

If openF = False Then
    Invoice.Close False
    Set Invoice = Nothing
End If
Application.ScreenUpdating = True

The question is: does the first round of macro code somehow changes the status or the name of the source book so that the function does not see it as 'open'?

Upvotes: 2

Views: 231

Answers (2)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1652

there are 2 simple ways:

Looping throught the workbooks, or seting it to a variable with an error handling (if err<>0 then not opened).

An other issu could be if you have separate excel application instances, in wich case you apply either previous choice by looping excel applications, using getobject.

I'll add code tomorrow from my computer.

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57743

You can check if the file is opened within Excel by looping through the workbooks and check the names instead.

Public Function IsWorkbookOpen(WorkbookName) As Boolean
    Dim i As Long

    For i = 1 To Workbooks.Count
        If Workbooks(i).Name = WorkbookName Then
            IsWorkbookOpen = True
            Exit Function
        End If
    Next i
End Function

Upvotes: 1

Related Questions