Macro does not run if file is open

[hrayani]

Hello,


I am using the below code to update links

Sub update()    
    ActiveWorkbook.UpdateLink Name:="\\192.168.0.100\itex share\ITEX\DATA ENTRY.xlsm" _
        , Type:=xlExcelLinks
    
    End Sub

The code works fine when the source file is closed. but gives a debug msg when the source file is open.

I would like the code to run whether source file is open or closed.

Any help would be appreciated

Regards,

Humayun

 

[MARK858]

The code works fine when the source file is closed. but gives a debug msg when the source file is open.

What is the debug msg?

 

[hrayani]

Hi Mark,

Sorry for replying late. I was on holidays.

This is the debug msg i am getting when the source file is open

Run-time error '1004':
Method 'UpdateLink' of object'_Workbook' failed

 

[MARK858]

Try putting both codes below in the same regular module and running the code.

Sub update()
    Dim myTest
    myTest = IsWorkBookOpen("\\192.168.0.100\itex share\ITEX\DATA ENTRY.xlsm") 'CHANGE TO FULL WORKBOOK PATH IF INCORRECT
    If myTest = True Then
        Application.Calculate
    Else
        ActiveWorkbook.UpdateLink Name:="\\192.168.0.100\itex share\ITEX\DATA ENTRY.xlsm" _
                                        , Type:=xlExcelLinks
    End If
End Sub

Function IsWorkBookOpen(FileName As String)
    ' Function curtesy of Siddharth Rout
    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

 

[hrayani]

Hello Mark,

Thanks a lot. Working just perfect now.

I just added these two lines in the code

Start of Code
ActiveSheet.Unprotect Password:="xxx"

End Of Code
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True _
, Password:="xxx"




Thanks once again

Regards,

Humayun