General discussion

Locked

Removing macros from spreadsheets

By TheLanLady ·
We are using an interface (ADI) between Oracle and Excel. The spreadsheets that it generates have a lot of macros from Oracle in them as well as up to 20 worksheets. It can take up to 15 minutes for them to open over the WAN assuming it doesn't crash the users system in the process (even when they choose DISABLE MACROS when they open the file). Since I just need the users to view the data over the WAN is there a way that I can strip the macros out when the workbooks are saved?

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Removing macros from spreadsheets

by Charles Byrne In reply to Removing macros from spre ...

Sub RemoveModules()
?**********
?Program to Remove Code & Modules Without Saving
?Charles Byrne 10/11/2000
?Office Version Compat:
?Office 97 - Tested and Works, Office 2000 - Not Tested
?Add Reference to File that contains code:
?
?In Office 97
?Microsoft Visual Basic for Applications Extensibility
?C:\Program Files\Common Files\Microsoft Shared\VBA\Vbeext1.olb
?
?In Office 2000
?Microsoft Visual Basic for Applications Extensibility 5.3
?C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\Vbe6ext.olb
?**********
Dim x As Integer, intVBProjCnt As Integer, intAns As Integer
Dim lngLineCnt As Long
Dim strProj As String, strWkbk As String
Dim vbProj As VBProject
Dim vbc As VBComponent
Dim Wkbk As Workbook

?Set Object Variables
Set Wkbk = ActiveWorkbook
Set vbProj = Wkbk.VBProject

strWkbk = Wkbk.Name
strProj = Wkbk.VBProject.Name

intAns = MsgBox("Remove Modules and Forms from " & _
strWkbk & "'s project (" & strProj & ")?", _ vbYesNo + vbQuestion, "Remove Without Saving")
If intAns = vbYes Then
?Remove Modules, Class Modules, Forms
For Each vbc In vbProj.VBComponents
If vbc.Type = vbext_ct_StdModule Then
vbProj.VBComponents.Remove vbc
ElseIf vbc.Type = vbext_ct_ClassModule Then
vbProj.VBComponents.Remove vbc
ElseIf vbc.Type = vbext_ct_MSForm Then
vbProj.VBComponents.Remove vbc
Else ?remove code lines from other objects with code
lngLineCnt = vbc.CodeModule.CountOfLines
If lngLineCnt > 0 Then
vbc.CodeModule.DeleteLines 1, lngLineCnt
End If
End If
Next vbc
End If
Set Wkbk = Nothing
Set vbProj = Nothing
End Sub

Collapse -

Removing macros from spreadsheets

by TheLanLady In reply to Removing macros from spre ...

Poster rated this answer

Collapse -

Removing macros from spreadsheets

by TheLanLady In reply to Removing macros from spre ...

This question was closed by the author

Back to Desktop Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums