Is there a similar decompile process for Excel 2007 files? (1 Viewer)

[mdlueck]

I have developed the following process to prepare Access 2007 databases before deployment:

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk/forums/showthread.php?t=219948

Is there a similar capability in Excel 2007? I came across this site:

"VBA Code Decompiler and Compactor"
http://orlando.mvps.org/VBADecompilerMore.asp

Which makes it sound like it is possible. But is it possible to do so without requiring that tool? I see references that there is no /decompile command line switch for Excel.

 

[mdlueck]

I have one mysterious breakpoint that I am unable to get cleared:

Sub Auto_Open()
On Error GoTo Err_Auto_Open

  If ObjAppSettings.IsflgInitOK() = False Then
    GoTo Err_Auto_Open
  End If

  'Update this filename for the target Fandango Costed BOM Export file
  Workbooks.Open FileName:="G:\New Product Development\Fandango2.1data\DevTestProject.xlsx"

Exit_Auto_Open:
[COLOR=Yellow] [B][COLOR=Red] Exit Sub[/COLOR][/B][/COLOR]

Err_Auto_Open:
  Call errorhandler_MsgBox("Module: modmain_AutoOpen, Function: Auto_Open()")
  Resume Exit_Auto_Open

End Sub

Each time I exit / reopen this spreadsheet file, the Autpen execution is stopped at this LOC.

I came across this tool this morning:
"VBA Code Cleaner"
http://www.appspro.com/Utilities/CodeCleaner.htm

I have tried that tool, using both the Clean and also Export / I manually deleted the modules and then reimported them... the cranky breakpoint will not go away. Each time I close / reopen the file, VBA stops at this LOC.

I tried copying the module to Notepad, inserting a new mod, changing the VBA name of the module, and then pasted back from Notepad. Finally I deleted the original module. The problem persists even through that.

In my mind, Excel 2007 is worse to get a file cleaned up than Access 2007! :banghead:

Suggestions?

 

[mdlueck]

I just stumbled across a band aid work-around to this breakpoint problem: Holding down the left shift key after I double-click the .xlsm file successfully runs the Autpen macro. I can see that it has run as that macro opens another spreadsheet file.

I was anticipating like Access, to disable the autoexec macro. However, like I said, Excel with the shift key opens the file properly.

I think I shall rebuild this spreadsheet file by hand, into a brand new .xlsm file. I can not think what else to do to try to remove that breakpoint.

 

[JANR]

Just to check, have your tried this: http://http://www.access-programmers.co.uk/forums/showthread.php?t=213807&highlight=phantom+breakpoint

JR

 

[mdlueck]

Thanks for the suggestion, JANR. I have done much more than that and can not get rid of the break point. I have rebuild the .xlsm file via exporting the VBA code and inserting new modules / classes, etc...

But yes, phantom break points in Access tell me it is time to run my Decompile / Compact / Compile process again... and 99% of the time that clears up the nonsense with Access / VBA.

Just now I started with a brand new .xlsm and created this VBA code in a new Module:

Sub Auto_Open()

  'Update this filename for the target Fandango Costed BOM Export file
  Workbooks.Open Filename:="G:\New Product Development\Fandango2.1data\DevTestProject.xlsx"

[COLOR=Red][B]End Sub[/B][/COLOR]

The code stops where indicated as if there were a breakpoint there.

The .xlsx file format, I understand, CAN NOT contain VBA code. I refreshed the .xlsx file via rerunning the export query, so that file is brand new.

I can not see what could be so wrong with that 1 LOC... :banghead: as that is the least I need to recreate the issue. And the DevTestProject.xlsx opened without error... so the VBA did what it was suppose to.

 

[mdlueck]

Duct tape workaround found here:

"Code enters break mode on form open"
http://answers.microsoft.com/en-us/...orm-open/ba69e1b3-fb1f-4d96-9ab2-e2272ce84b45

Implemented as follows:

Sub Auto_Open()
On Error GoTo Err_Auto_Open

  If ObjAppSettings.IsflgInitOK() = False Then
    GoTo Err_Auto_Open
  End If

[B][COLOR=Blue]  'This is duct-tape to prevent a mysterious trap/error caused by Workbooks.Open
  Application.EnableCancelKey = xlDisabled[/COLOR][/B]

  'Update this filename for the target Fandango Costed BOM Export file
  Workbooks.Open FileName:="G:\New Product Development\Fandango2.1data\DevTestProject.xlsx"

Exit_Auto_Open:
  Exit Sub

Err_Auto_Open:
  Call errorhandler_MsgBox("Module: modmain_AutoOpen, Function: Auto_Open()")
  Resume Exit_Auto_Open

End Sub

Apparently it is something related to the Open event. (shrug)

I will leave this duct tape fix in place for now.

So, does anyone know how long this adjusted setting stays active? By using this in Autpen, have I disabled all error handling for the entire application? Or only until Autpen ends?