Remove Excel VBA Modules and/or External Links Via Access VBA?

[kjv1611]

I'm using Excel 2007, and the workbooks sent to me are also apparently in 2007, as the code references the ribbon.

I don't know that this will be a recurring issue, but it was definitely a first that I had experienced.

One of the processes I'm currently responsible for, we get data in Excel spreadsheet form sent to us from outside sources. I'm taking this data, loading into an Access database for sorting, combining, further processing. Well, as part of my automation process, I've been opening the Excel documents programmatically to make sure of no import errors, and if we do have any, to just go ahead and fix them without having to waste time and effort there.

Well, out of the blue, one of the senders left some VBA code modules as well as a User Form, and what appears to be an attached Excel workbook in the last email. It's all looking perfectly valid for THEIR use, but for whatever reason, they didn't clean it all out before sending.

So, now I want to find an idea, at least a start towards a solution for checking a workbook before opening it for any VBA code and/or links to other tables/spreadsheets, and then remove all of that if found. I ONLY want the data in this case, I could care less about anyone else's code - in this instance.

The code basically is supposed to add a button to the ribbon, open a user form for exporting data lists to various sources, and of course includes a little error handling - it's pretty short and to the point. The problem is that they apparenlty put some of this in the local workbook - which is why I got it, and then the rest is missing, b/c it's in their PERSONAL.XLS file.

So regardless of the whos, hows, whats, and all, I'd rather just skip all that and clear out anything I don't need automagically.

Can anyone give me any starting points of reference, a web article, a keyword I need to look at, etc... I'm imagining (can't remember for sure) there is a module object that I can reference, but I want to be sure. I'll continue researching as I wait for any responses.

Thanks in advance for anything.

 

[kjv1611]

Figures. I didn't find anything when searching before posting here. Then right after I post here, I find at least one starting point:

http://www.ozgrid.com/VBA/delete-module.htm

I'm thinking I can adapt that to my needs, but of course any suggestions greatly appreciated. Whatever I end up with, I'll post here, so others can benefit as well.

 

[kjv1611]

Another reference:

http://www.cpearson.com/excel/vbe.aspx

Still working on it..

 

[kjv1611]

Okay, I've gotten somewhere, I think, however I may be getting stuck..

So, I'm trying to do this from Access VBA.

And I can at least so far loop through the VBA Components - those would be "This Workbook", "Sheet1", "Sheet2", etc.. or whatever the sheet names are.. that's no problem.

But how can I delete any code attached to those Components? That's the main point of where I'm getting stuck.

Also, I need to find a way to disable the start-up of Macros before checking and running this code, so I don't get the annoying "Run-time" errors which are from the code that shouldn't be there in the first place.

This is what I'm trying to use so far:

Private Sub SaveExcelToText()
    Dim fso As FileSystemObject
    Dim fldr As Folder
    Dim f As file
    Dim appXl As Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fldr = fso.GetFolder("\\NetworkFolderLocation")
    Set appXl = New Excel.Application
    
    For Each f In fldr.Files
        If InStr(f.Name, "xls") Then
            appXl.DisplayAlerts = False
            Set wb = appXl.Workbooks.Open(f.Path)
            wb.Activate
            Set VBProj = appXl.ActiveWorkbook.VBProject
            For Each VBComp In VBProj.VBComponents
                MsgBox VBComp.Name
            Next VBComp
            appXl.DisplayAlerts = True
            appXl.Workbooks.Open (f.Path)
        End If
    Next f
    
    Set VBProj = Nothing
    Set VBComp = Nothing
    Set CodeMod = Nothing
    Set ws = Nothing
    Set wb = Nothing
    appXl.Quit
    Set appXl = Nothing
    Set f = Nothing
    Set fldr = Nothing
    Set fso = Nothing
    
End Sub

From what I'm finding/guessing, is that I have to somehow tell what kind of component the item is (worksheet, module, etc), and then based on that determine the action. If it's a module, delete it, or if it's a worksheet, just clear/delete the code. I haven't the foggiest as to how to find and delete the code within one of the objects so far... and the Remove option didn't work out so well, probably b/c I was running it on any it found, thus "Sheet 1" would throw an error, b/c it can't just be "removed".

I'll keep hammering away at it. If anybody has any advice, references, info in the mean time, I'd be most thankful.

 

[kjv1611]

Alright, for stopping the macros/modules from running, I've tried these so far (just a shot in the dark, really), and no go:

appXl.DisplayAlerts = False
appXl.EnableEvents = False

The commands run, but they don't stop anything with the macros. I suppose if I could change the applications macro security level, that would handle it.

Also, I wonder if running this from within Access, if perhaps I need to somehow Dim the VBA variables differently... such as Excel.VB... instead of just VB... Well, I may take a stab there later.

Still, open for anyone else's advice/info on the subject.

 

[kjv1611]

Well, the title to this tutorial/document sounds promising, if I can get it to load... slow loading at the moment:

http://www.exceltip.com/st/Delete_a...ocument_using_VBA_in_Microsoft_Excel/505.html

Finally loaded... so I'll review this and see how this approach works for me.

 

[kjv1611]

Just as a final update (forgot to post back last week), the reference from exceltip.com seemed to do the trick. It's very efficient code, it seems.