Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CodeModule utilization problem | Excel Programming | |||
modifying code using addfromfile method | Excel Programming | |||
Excel crashes when using CreateEventProc towards CodeModule | Excel Programming | |||
get linenumber when string found in VBE codemodule | Excel Programming | |||
Reload Typelibrary? .Remove() & .AddFromFile() fails | Excel Programming |