Using VBA to remove a module

mikechambers

Active Member
Joined
Apr 27, 2006
Messages
397
I have the following code in Workbook C to perform the following steps:

1. Export Module1 to a BAS file from Workbook A
2. Delete Module1 from Workbook B
3. Import BAS file into Workbook B as Module1

Code:

Dim FName As String, vbCom As Object
With Workbooks("Workbook A.xls")
FName = "C:\code.bas"
.VBProject.VBComponents("Module1").Export FName
End With
Set vbCom = Workbooks("Workbook B.xls").VBProject.VBComponents
vbCom.Remove VBComponent:=vbCom.Item("Module1")
vbCom.Import FName
Kill FName

The problem is after the Remove line, Module1 still shows up in Workbook B, so that when it imports FName, it does so as Module11. BUT, if I end the macro after the remove line, Module1 disappears. So it seems like a refresh issue or something. It is not actually removed until the macro is finished running? Does that make sense? I tried Application.Screenupdating=True after the Remove line, but didn't fix it. Any ideas???
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Have you tried a DoEvents line after the Remove?
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
well i tested your code
works fine for me - no such problem here
i had a similar problem with the formula bar - delete a formula from the cell but it stays in the formula bar
the solution was to hide and then show the formula bar again.
maybe try the same with the project explorer window in VBE (?is this stupid?) or try saving the file (Workbook B) before import or DoEvents - not really sure what can help
 

mikechambers

Active Member
Joined
Apr 27, 2006
Messages
397
DoEvents isn't working for me. I also tried saving the workbook after the Remove line, and nothing. I can't figure this out.
 

mikechambers

Active Member
Joined
Apr 27, 2006
Messages
397

ADVERTISEMENT

bobsan42: What version of Excel are you running?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I take it there's no chance there is any code actually being triggered in that module (UDFs for example)?
Have you tried moving the import to a separate routine and calling that using OnTime from the first sub?
 

mikechambers

Active Member
Joined
Apr 27, 2006
Messages
397

ADVERTISEMENT

I'm not familiar with the OnTime...
 

mikechambers

Active Member
Joined
Apr 27, 2006
Messages
397
I did realize however that it has something to do with me opening the Workbooks A & B with the preceding code. If I already have them open, and I step over the open lines, it works.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Code:
Dim FName As String, vbCom As Object
With Workbooks("Workbook A.xls")
FName = "C:\code.bas"
.VBProject.VBComponents("Module1").Export FName
End With
Set vbCom = Workbooks("Workbook B.xls").VBProject.VBComponents
vbCom.Remove VBComponent:=vbCom.Item("Module1")
application.ontime now() + timeserial(0,0,1), "DoImport"

then additional sub:
Code:
Sub DoImport()
Dim FName as string
FName = "C:\code.bas"
Workbooks("Workbook B.xls").VBProject.VBComponents.Import FName
Kill FName
End Sub
 

mikechambers

Active Member
Joined
Apr 27, 2006
Messages
397
I will give that a shot, but I have narrowed it down even more.

When I open the other two workbooks, I have Workbook Open procedures running in those. If I comment out those, everything works fine. So there is something going on in the Open procedures that is conflicting with the rest of it.

So in each of the other 2 files that are opening, I have the exact same code.

In ThisWorkbook I have:

Private Sub Workbook_Open()
Call OpenProcess
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call CloseProcess
End Sub


And then in Module1 I have:

Const FileURL = "\\scotts002us\lawnsrv\Reporting\Daily Flash Report\Log Files\"

Sub OpenProcess()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
SecurityCheck
LogUser
ChangeUserName
End Sub

Sub SecurityCheck()
Dim ws As Worksheet
ThisWorkbook.Unprotect "summit"
Warning.Visible = True
For Each ws In ThisWorkbook.Sheets
If ws.Name <> "StartSUM" And ws.Name <> "EndSUM" Then ws.Visible = xlSheetVisible
Next
Warning.Visible = xlSheetHidden
ThisWorkbook.Protect "summit"
End Sub

Sub LogUser()
If ActiveWorkbook.ReadOnly Then Exit Sub
If Dir(FileURL & (Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_log.xls")) = "" Then
NewBook = FileURL & (Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_log.xls")
Workbooks.Add
ActiveWorkbook.SaveAs (NewBook)
Else
Workbooks.Open FileURL & (Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_log.xls")
End If
Columns("A:B").ColumnWidth = 20
Range("A1:B29").Cut Destination:=Range("A2:B30")
Range("A1") = Environ("USERNAME")
Range("B1") = Now()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Sub ChangeUserName()
If Application.UserName = "The Scotts Company" Then Application.UserName = Environ("USERNAME")
End Sub

Sub CloseProcess()
Dim ws As Worksheet
If ActiveWorkbook.ReadOnly Then Exit Sub
Application.ScreenUpdating = False
ThisWorkbook.Unprotect "summit"
Warning.Visible = True
For Each ws In ThisWorkbook.Sheets
If ws.Name = "Warning" Then
ws.Visible = xlSheetVisible
Else
ws.Visible = xlSheetHidden
End If
Next
ThisWorkbook.Protect "summit"
ThisWorkbook.Save
End Sub

Sub DesignMode()
Dim ws As Worksheet
ActiveWorkbook.Unprotect "summit"
For Each ws In ThisWorkbook.Sheets
ws.Visible = True
ws.Unprotect "summit"
Next
End Sub

Sub UserMode()
ActiveSheet.Select
For Each ws In ThisWorkbook.Sheets
ws.Protect "summit"
Next
Sheets("Warning").Visible = False
Sheets("StartSUM").Visible = False
Sheets("EndSUM").Visible = False
ActiveWorkbook.Protect "summit"
End Sub


I know it's a lot to ask to look through it, but can you see anything in there that could be causing it? I'm beating my head against a wall trying to figure this out and I KNOW it's something stupid!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top