Problem with updating sheets

**saitam.alittna** · 05-12-2011, 03:24 AM

Hi!

I made a macro in a workbook (A) that updates sheets from another workbook (B). Is it possible somehow to add an IF-function in the macro that would check if the workbook B is already in use? So that if the workbook B is in use, "Read only", it would skip the update in workbook A, but if the workbook B is not in use it would update the sheets in workbook A.

Thank you already!

**Richard Buttrey** · 05-12-2011, 03:55 AM

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

**saitam.alittna** · 05-12-2011, 04:23 AM

Originally Posted by Richard Buttrey

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

The update code I wrote to workbook A is following:

Workbooks.Open Filename:="X:\Workbook_B.xls"
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Windows("Workbook_A.xls").Activate
Sheets("Sheet1").Select
Cells.Select
ActiveSheet.Paste
Range("A1").Select

How do I add your code in there? On which row?

Sorry, I'm still a rookie with VBA...

**Richard Buttrey** · 05-12-2011, 06:47 AM

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