Thread: Problem with updating sheets

Hi,

Perhaps something like the following Function and its calling procedure.

Sub IsWorkbookOpen()
    If WorkbookIsOpen("your file name") Then 'change name to suit or read from variable
        MsgBox ("File: " & "name" & " is already open"), vbInformation
    Else
        MsgBox ("File: " & "name" & " is not open"), vbInformation
    End If
End Sub

Function WorkbookIsOpen(wbName) As Boolean
'Returns TRUE if the workbook is open
    Dim x As Workbook
    On Error Resume Next
    Set x = Workbooks(wbName)
    If Err = 0 Then WorkbookIsOpen = True Else: WorkbookIsOpen = False
End Function

Hi,

Try the code below in place of your code. The earlier version was testing for an open workbook. I realise you want to test for the read only status

    Dim stFname As String, stSheet As String, wbMyBook As Workbook
    stFname = "C:\Users\Richard\Downloads\VLOOKUP.xlsx"
    stSheet = "Sheet1"
    Set wbMyBook = ThisWorkbook
    If GetAttr(stFname) And vbReadOnly Then
        Exit Sub
    Else
        Workbooks.Open Filename:=stFname
        Range(Sheets(stSheet).Range("A1"), Sheets(stSheet).Range("A1").SpecialCells(xlCellTypeLastCell)).Copy
        wbMyBook.Sheets(stSheet).Range("A1").PasteSpecial (xlPasteAll)
        Range("A1").Select
    End If

Note that you don't need to use .Select and .Activate. In fact rarely do you ever need these instructions which only slow down code. The macro recorder will always include these so you should generally delete them and just address the objects directly.

Regards