0

I have been playing around with how to remove Excel macros using PowerShell and COM objects and have had success in doing so, however, I noticed that for .XLS files, there is a reference to the name of the macro I remove, even though the VBA Module itself is gone. If I save a .XLS file as .XLSX, it removes the macro as well, but when I save it back to .XLS, I still find a reference (located in the unarchived VBA_VBA_PROJECT file).

Anyone have any insight into how to remove the reference(s)? I am not finding much interacting with the various workbook related objects that could point to this reference.

If I remove the VBA Module from a .xlsb file, for example, no reference is retained.

TIA.

2
  • 1
    why would you still use the .xls format if .xlsx has been the standard for over 15 years. Just don't. Who cares how it behaves. It should not be used anymore and only feature in legacy files. I certainly wouldn't want to spend any time troubleshooting the "why" of a legacy file format that was superseded in 2007
    – teylyn
    Commented Mar 3, 2022 at 22:53
  • @teylyn - you have a valid point there, but unfortunately this is beyond my control in this case.
    – BaliRobin
    Commented Mar 7, 2022 at 14:08

1 Answer 1

0

.XLS is alive and well. Sadly, of course, but it's a fact of life. If only because some small percentage of folks still use it daily and because many software packages that export to Excel do so to an .XLS file, not an .XLSX file.

The following may or may not be of aid, but looks interesting, especially beginning on page 88 (though I was skimming, so it may not be as useful as it looked like it could be):

https://interoperability.blob.core.windows.net/files/MS-OVBA/%5bMS-OVBA%5d.pdf

This is a Microsoft document on a Microsoft site, so it seems likely its link will not break any time soon. That said though...

I found the link on this helpful set of folks' site (credit where credit is due):

https://software-solutions-online.com/vba-project-bin-and-protection/?nowprocket=1

It is really more about .XLSX, I believe, though the document above is suggested as a Microsoft effort to provide documentation for versions prior to the open specifications approach.

Ordinarily I'd suggest opening the file package as a .ZIP package and scour it for mention, then experiment with how to remove the information directly, safely, in order to be able to move forward until a better solution is found. But I don't know that any, much less many, of the older versions (yeah, there were LOTS of .XLS versions, not just one or two), have an openable/editable package. I didn't do that in those days and have never had reason to do so now that I sometimes do do it for .XLSX packages. So I have no experience there.

Lastly, in the spirit of moving forward in some useful way, no matter if it is a distasteful one that is not helpful to the question itself, one CAN of course, open a new .XLS file and copy over all pages, assuming one has knowledge of/access to them all. Should be clean of any VBA material or references. Of course, it'd be diagnostic if it were not clean of such as that would indicate references traveling in sheet information and could point you toward where to remove it (sheet properties).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .