CodeModule.AddFromFile problem
 

Hi all,

I don't know if you can help me, but I face a very mysterious problem about
the AddFromFile method

Below is the code, which involves three workbooks:
1. ThisWorkbook: The book containing the code shown below; VBProject
protected
2. CodeTemplate: The book containing the source code to be copied, VBProject
UNPROTECTED
3. ActiveWorkbook: The book where the code will be copied to; VBProject
UNPROTECTED

Sub CopyCode()
Dim TempFileName As String
Dim RemoteVBP As VBProject, LocalVBP As VBProject
Dim GenModule As VBComponent

Set RemoteVBP = ActiveWorkbook.VBProject
Set LocalVBP = CodeTemplate.VBProject

'* Start of Code Block 1
TempFileName = Environ("Temp") & "\ExcelVBA" & Round(Rnd * 1000) & ".bas"
LocalVBP.VBComponents("Module1").Export TempFileName

Set GenModule = RemoteVBP.VBComponents.Import(TempFileName)
GenModule.Name = "GeneralRoutines"
Kill TempFileName

'* Start of Code Block 2
TempFileName = Environ("Temp") & "\ExcelVBA" & Round(Rnd * 1000) & ".bas"
TempFileName = Environ("Temp") & "\12345.bas"
LocalVBP.VBComponents("Code_TFComparison").Export TempFileName
With RemoteVBP.VBComponents(ActiveSheet.Name)
.CodeModule.AddFromFile TempFileName 'PROBLEM LINE
'Rename the document module back to its original name
.Name = ActiveSheet.Name
End With
Kill TempFileName
End Sub

The problem I encountered is:

1. If the machine is rebooted and ThisWorkbook is open and keep on protected
state, then the PROBLEM LINE will never be run with no error message.
2. If ThisWorkbook is unprotected and then run the program again, then no
problem.
3. If ThisWorkbook is closed and then opened again, then even though it is
protected, the code will run normally IN MOST CASE.

I am using Excel 2003 SR-2. Please advise on my problem and any possible
ways to get around it. Thanks in advance.

Frederick Chow
Hong Kong.