Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Module / Form
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Module / Form
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 wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Module / Form
On 14 Apr, 15:30, "Peter T" <peter_t@discussions wrote:
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 wrote in message ... 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- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Module still there even after remove and save | Excel Programming | |||
Remove Module not working | Excel Programming | |||
Remove the module | Excel Programming | |||
Remove and Import Module | Excel Programming | |||
Remove VB Module from workbook | Excel Programming |