Is there a way I can convert all udf calls to use my add-in module?

[JenniferMurphy]

When I moved from my old Win 10 laptop to my new Win 11 laptop, I neglected to move my personal add-in module (My Add-Ins.xlam) over. So any calls to any code in that add-in, gets changed to some long string. For example, I wrote a Col() UDF that would return the column letter. Now instead of =col(), I see something like this:

=@'C:\Documents and Settings\Administrator\Application Data\Microsoft\AddIns\My Add-Ins.xlam'!col()

If I manually delete everything but CODE=vba]=col()[/CODE], it works.

I can use Replace to fix all calls in an entire workbook, but is there a way I can fix all calls in all workbooks? Or do I need to do each workbook individually as punishment for doing the migration wrong?

 

[offthelip]

I have had a similar problem, so what I did was write a macro that searched for the c:\documnts..... etc string and deleted it throughout a workbook , I kept the macro in my personal.xlb file so that I could run it whenever I opened a workbook I hadn't previously converted. I think you could probably add something to search a file directory and do all the xl files in that directory quite easily.

 

[JenniferMurphy]

I have had a similar problem, so what I did was write a macro that searched for the c:\documnts..... etc string and deleted it throughout a workbook , I kept the macro in my personal.xlb file so that I could run it whenever I opened a workbook I hadn't previously converted. I think you could probably add something to search a file directory and do all the xl files in that directory quite easily.

Do you have a copy of that macro you could post?

 

[offthelip]

Here is the macro I used, I haven't needed to use it for while but it should it give you the idea.

Sub chngeaddin()
klen = 77
' change this string to your string
strk = "='C:\Documents and Settings\User Name1234\Application Data\Microsoft\AddIns\Tech_trading_functions.xlam'!"
strk1 = "Document"
    Set Sheet = ActiveSheet
    row_min = Sheet.UsedRange.Row
    row_max = row_min + Sheet.UsedRange.Rows.Count - 1
    col_min = Sheet.UsedRange.Column
    col_max = col_min + Sheet.UsedRange.Columns.Count - 1

inarr = Range(Cells(row_min, col_min), Cells(row_max, col_max)).Formula
'inarr = ActiveSheet.UsedRange.Formula
ilim = UBound(inarr, 1)
jlim = UBound(inarr, 2)

For i = 1 To ilim
For j = 1 To jlim
   forstr = CStr(inarr(i, j))
   
     If forstr <> "" Then
     MMn = InStr(forstr, "Tech_trading_function")
     If MMn > 0 Then
      If MMn = klen Then
      bang = InStr(MMn, forstr, "!")
      newfor = Mid(forstr, bang + 1, 1000)
      inarr(i, j) = "=" & newfor
      Else
       lftstr = Left(forstr, MMn - klen)
       bang = InStr(MMn, forstr, "!")
       midfor = Mid(forstr, bang + 1, 1000)
       newfor = lftstr & midfor
'      inArr(i, j) = newfor
      End If
       
      Cells(i, j) = "=" & newfor
     End If
     End If
 Next j
 Next i
 tt = 23
Range(Cells(row_min, col_min), Cells(row_max, col_max)).Formula = inarr

 

[JenniferMurphy]

Here is the macro I used, I haven't needed to use it for while but it should it give you the idea.

. . .

Thanks. I'll see if I can follow that.