Remove Module / Form

[james.billy]

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).Export
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

 

[Peter T]

Unless you have set Thisworkbook as the reference to 'y' your components
will not get removed. Also you won't know your code is wrong as you did not
restore normal erro handling from On error resume next.

Try replacing 'y' with Thisworkbook in both procedures.

BTW, I assume ySubName and xModForm refer to module (component) name and
extension respectively. You may find it helps debugging in future to name
variables with more meaningful names.

In passing -

I have set a reference to
the visual basic extensibility library first.

Not necessary if, as an example, you change
Dim VBComp As VBComponent
to
Dim VBComp As Object ' VBComponent

Regards,
Peter T

 

[james.billy]

Unless you have set Thisworkbook as the reference to 'y' your components
will not get removed. Also you won't know your code is wrong as you did not
restore normal erro handling from On error resume next.

Try replacing 'y' with Thisworkbook in both procedures.

BTW, I assume ySubName and xModForm refer to module (component) name and
extension respectively. You may find it helps debugging in future to name
variables with more meaningful names.

In passing -


Not necessary if, as an example, you change
Dim VBComp As VBComponent
to
Dim VBComp As Object ' VBComponent

Regards,
Peter T














- Show quoted text -

Hi Peter,

Apologies should have been clearer, I have declared y as a public
workbook and then set y to reference WorkBookA so I am able to access
it from all of the different sub routines. Thisworkbook therefore is
WorkbookB. So I am unable to set y to equal thisworkbook otherwise it
will add / remove from itself / incorrect workbook.

Your assumptions regarding ySubName and xModForm are correct. - I have
always had problems around naming variables and using meaningful
words, they always seem to mean something to me at the time,
hindsight!

Yeah I am sure I have used late bindings and dimmed VBComp as an
object in the past but I just wanted to rule that out.

I will put error handling back to Excel after I have checked the error
and see if that gets me anywhere.

Cheers for suggestions, much appreciated.

James