So, i have an .xlam AddIn with several UDFs in it. As a workaround to a well known absolute path problem I am importing my UDF to a current Workbook so the UDFs could be called from the Workbook, not from the AddIn using the following code:
Sub CopyOneModule()
Dim FName As String
On Error GoTo errhandler
With ThisWorkbook
FName = .Path & "\code.txt"
.VBProject.VBComponents("HMFunctions").Export FName
End With
ActiveWorkbook.VBProject.VBComponents.Import FName
MsgBox ("Functions successfully imported")
errhandler:
If Err.Number <> 0 Then
Select Case Err.Number
Case Is = 0:
Case Is = 1004:
MsgBox "Please allow access to Object Model and try again.", vbCritical, "No Access granted"
End Select
End If
It seems to be working fine. So, my (probably dumb) question is: Is there a way to make workbook with imported UDFs "unsee" the same module stored in the AddIn? It's needed to avoid the following situation which may be very confusing:
Thank you in advance.
Removing the Private labels from temporary text file, as suggested by snoopen worked like a charm. Question closed. Here is the final code I am using for import:
Sub CopyOneModule()
Dim FName As String
Dim FileContent As String
Dim TextFile As Integer
Dim ws As Workbook
Set ws = ActiveWorkbook
On Error GoTo errhandler
With ThisWorkbook
FName = .Path & "\code.txt"
.VBProject.VBComponents("HMFunctions").Export FName
End With
TextFile = FreeFile
Open FName For Input As TextFile
FileContent = Input(LOF(TextFile), TextFile)
Close TextFile
FileContent = Replace(FileContent, "Private", "")
TextFile = FreeFile
Open FName For Output As TextFile
Print #TextFile, FileContent
Close TextFile
ws.VBProject.VBComponents.Import FName
MsgBox ("Functions successfully imported")
errhandler:
If Err.Number <> 0 Then
Select Case Err.Number
Case Is = 0:
Case Is = 1004:
MsgBox "Please allow access to Object Model and try again.", vbCritical, "No Access granted"
End Select
End If
End Sub