Hi All,

I am having problems importing new modules and forms, I first delete
the old ones and then import the new ones. I have set a reference to
the visual basic extensibility library first. The problem is occurring
as the modules / forms aren't being removed until right at the end of
the code. I have tried adding a loop to wait for a couple of seconds
and put DoEvents in the loop to yeild to Excel but this still isn't
rectifying the problem.

Other references on the web suggest using an add-in, which I am going
to look at but I am unable to do this until I re-relase the
spreadsheet next year (every year the user starts with a new
spreadsheet so I can make these changes then).

The process starts with WorkbookA, when this opens it runs code to
first open WorkBookB and then using an application run statement it
calls a macro from within WorkBookB. The code below is contained in
workbookB and is modifying (looping through) the code modules and
forms from WorkbookA (It is not changing the module that the original
run statement was executed from). The code below is only part of what
the overall macro does but xExport_And_Delete is called early on in
the macro and I have left the import code right until the end but its
still not working.

I either end up with missing forms / modules, or it errors when
importing the forms as Excel still thinks that the forms exist where
as it will tack a one onto the end of the modules (which to me
suggests at the time of import the module still exists). - The code
does work but not realiably as it seems if there isn't much CPU
avaialble (i.e. the user is using an old machine or they have a lot of
other applications open) Excel cant keep up, where as running on my
machine (Pentium 2.8gz and 2gb of ram, Excel 2000 and win xp) it seems
to work more often than not.

I need this to work with many users, so could really do with something
that is reliable, can anyone help? Make any further suggestions?

Sub xExport_And_Delete()
Dim ySubName As String, xSubName As Integer
Dim xModForm As String
Dim testV As Variant
Dim VBComp As VBComponent
For xSubName = 3 To ThisWorkbook.Sheets("Index").Cells(65536,
6).End(xlUp).Row
ySubName = ThisWorkbook.Sheets("Index").Range("E" & xSubName)
xModForm = ThisWorkbook.Sheets("Index").Range("F" & xSubName)
ThisWorkbook.VBProject.VBComponents(ySubName).Expo rt
Environ("Temp") & "\" & ySubName & "." & ModForm
On Error Resume Next
testV = CBool(Len(y.VBProject.VBComponents(ySubName).Name) )
If Err.Number < 0 Then testV = False
If testV Then
Set VBComp = y.VBProject.VBComponents(ySubName)
y.VBProject.VBComponents.Remove VBComp
Set VBComp = Nothing
End If
Next xSubName
End Sub

Sub xImportModules()
Dim ySubName As String, xSubName As Integer, xModForm As String
For xSubName = 3 To ThisWorkbook.Sheets("Index").Cells(65536,
6).End(xlUp).Row
ySubName = ThisWorkbook.Sheets("Index").Range("E" & xSubName)
xModForm = ThisWorkbook.Sheets("Index").Range("F" & xSubName)
y.VBProject.VBComponents.Import Environ("Temp") & "\" &
ySubName & "." & xModForm
Kill Environ("Temp") & "\" & ySubName & "." & xModForm
Next xSubName
End Sub

Many Thanks In Advance for any help.

Cheers,

James