How to create a bug report?

[ker_01]

I have a replicable problem trying to open XL2007 xlsm files from XL2003,
with the requirement that the macros in the 2007 file must be disabled (the
workbooks are being opened to pull data, but each workbook's "_open" includes
code that resets the target data based on the userID of the person opening
the file).

I have recreated a test case with 12 lines of code (3 in one workbook, 9 in
another-see below. I did some searching, but couldn't find any obvious way to
submit this to MS. Does anyone have a link or information on how to submit
bugs?

To replicate the problem:
-----------------------------
Create a blank 2007 workbook. In the workbook_open event, add msgbox "hello
world" (3 lines of code total). Save the file as XL2007 macro enabled file
type (XLSM).

Open a blank 2003 workbook. Add the following code:

Sub test2007macrowkbk()
Dim secAutomation As MsoAutomationSecurity
secAutomation = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Application.Workbooks.Open Filename:="K:\test2007macro.xlsm",
UpdateLinks:=False, ReadOnly:=True
Msgbox "Process Complete"
Application.AutomationSecurity = secAutomation
End Sub

(replace the filename with the filename of the XL2007 file you saved)

Run the 2003 code. It never makes it to the "process complete" message, the
code just dies after opening the 2007 workbook. If you remove the security
lines of code, it will open the 2007 workbook and trigger the "hello world"
message. If you save the 2003 workbook, open it in 2007 and run the code from
2007, it opens the original 2007 workbook as expected (with macros disabled),
then shows the "process complete" message.

If there is an alternative way to open a 2007 XLSM file from 2003 with
macros disabled, please let me know. If there isn't another way, then I'm
still thinking this would be appropriate for a bug report, and I welcome any
information on how to submit one.

Thank you!
Keith

 

[Don Guillett]

No time to go thru all of that but a thought.
Have you tried saving as .xlsx

 

[ker_01]

I did do some testing earlier today, and 2003 will open 2007 files without
macros (xlsx), but the majority of my source files will be in xlsm format.

The files are populated by a large population of company employees at
various locations (most of whom are on Office 2007). They may open the
workbook and edit their data more than once, so I can't add a saveas xlsx in
the workbook_beforeclose code, because then the workbook would lack the code
to support their subsequent edits (and at least a few users would probably
send back the original workbook, not the one with the new filename).

On our end, I could have an administrative assistant open each file with
macros disabled (because the VBA code updates certain key fields based on the
userID) and resave each workbook as an xlsx, but that seems incredibly
clunky, when 2003 can open 2003 files with macros disabled, and 2007 can open
2007 files with macros disabled... that would be a lot of extra manual
open/save time, as compared to having 2003 be able to open a 2007 file with
macros disabled.

If I can't find any other solutions I'm still inclined to submit it as a
bug, but I'd also do some workaround testing- I don't know enough about the
file structures, but I wonder if there is a way to fool 2003 by having 2003
change the target file extension from xlsm to xlsx before opening the 2007
file(s). I don't have high hopes for that, but I'm up for trying any
workaround.

Thanks,
Keith

 

[Barb Reinhardt]

If you step through your code, I bet it stops at this line

Application.AutomationSecurity = msoAutomationSecurityForceDisable

Change it to this
Application.AutomationSecurity = msoAutomationSecurityLow

HTH,
Barb Reinhardt

 

[ker_01]

Barb-

Thank you for your reply. I tried your suggestion, but it did not disable
the macros in the xlsm workbooks that were being opened.

My real code cycles through multiple files to retrieve data. Prior to this
thread, I did add a debug.print line in between every actual code line in my
real file. Each debug line was triggered (including after every security
line) until it got to the first xlsm file that actually had macros; then each
debug line was still triggered up to the .open line, which successfully opens
the xlsm file (as does the demonstration code in the original post for this
thread). The problem is that it never returns control to the source macro in
the 2003 workbook, so there is no execution of any code after the .open line.

Thank you,
Keith

 

[Barb Reinhardt]

Ok, then you need to add this

Application.EnableEvents = False 'Before you open the workbooks
and
Application.EnableEvents = True 'After you open the workbooks.

The response I provide previously would only disable the message about
enabling macros when you open the workbook.

HTH,
Barb Reinhardt

 

[ker_01]

Unfortunately, even with application.EnableEvents in place, it still fails
when opening the first xlsm file that contains macros. Using
Application.EnableEvents, I tried it both with the lines of security I had in
place, then tried again after commenting them out in case they interfere with
..EnableEvents. No joy either way.

Sub test2007macrowkbk()
Dim secAutomation As MsoAutomationSecurity
secAutomation = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Application.EnableEvents = False
Application.Workbooks.Open Filename:="K:\test2007macro.xlsm",
UpdateLinks:=False, ReadOnly:=True
Application.EnableEvents = True
Msgbox "Process Complete"
Application.AutomationSecurity = secAutomation
End Sub

Thank you for your continued advice,
Keith