I am facing a very weird problem with VBA code. I have an excel file which uses UDFs from an Addin. This file runs on the server so it is really impossible for me to see what actually is going on in the code. Now, whenever I make any changes in the file (on my local machine), I use the local version of the addin. So when replacing the file on the server, I need it to automatically change the links to the addin copy that is there on the server. For this purpose, I have this piece of code in the file:
Private Sub changeLinks(addinName As String, wb As Workbook)
Dim check As Long
Dim i As Long
Dim existingPath As String
Dim correctPath As String
Dim temp As Variant
Dim tempA As Variant
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
Application.ScreenUpdating = False
temp = getLinks(addinName, wb)
On Error Resume Next
correctPath = Application.AddIns(addinName).fullName
For i = 1 To 100
If temp(i) = "" Then Exit For
If UCase(temp(i)) <> UCase(correctPath) Then
wb.ChangeLink temp(i), correctPath, xlLinkTypeExcelLinks
End If
Next
On Error GoTo 0
End Sub
Private Function getLinks(addinName As String, wb As Workbook)
Dim allLinks As Variant
Dim check As Long
Dim i As Long
Dim temp(1 To 100) As Variant
Dim linkCounts As Long
check = 0
allLinks = wb.LinkSources
linkCounts = 0
On Error Resume Next
For i = LBound(allLinks) To UBound(allLinks)
check = InStr(1, UCase(allLinks(i)), UCase(addinName))
If check <> 0 Then
linkCounts = linkCounts + 1
temp(linkCounts) = allLinks(i)
End If
Next
On Error GoTo 0
getLinks = temp
End Function
Instead of all the statements like Application.DisplayAlerts=False and On Error Resume Next, the code hangs on the line where I use .ChangeLink. Curiously, the code works ok if I login to the server and run this manually. I believe it is due to some pop-up which appears when the code tries to run .ChangeLink statement. I can't see what this pop-up is, because it runs on the server with a different login. And so now I'm just banging my head to the wall.
Appreciate any help. Thanks.
Bookmarks