midget1500

hi everyone

i'm trying to open a spreadsheet (to obtain values from) from another spreadsheet. this works fine as long as their is no VBA code in the other spreadsheet else execution of the code stops upon the open command (even though macros have been disabled). even if i just put a msgbox("hi") in the other spreadsheet that is enough to stop it. this is driving me mad :-(

any thoughts on this are greatly appreciated.

Sub OpenAllModels()

Dim secAutomation As MsoAutomationSecurity

secAutomation = Application.AutomationSecurity

Application.AutomationSecurity = msoAutomationSecurityForceDisable

Dim wb As Workbook

Application.ScreenUpdating = False

Set wb = Workbooks.Open("C:\book1.xls", False, True)

MsgBox ("this doesnt get executed")

wb.Close

Set wb = Nothing

Application.ScreenUpdating = True

Application.AutomationSecurity = secAutomation

end sub

OllyK

Works just fine for me

midget1500

olly

what, book1 opens, then MsgBox ("this doesnt get executed") is run?

do you have

Private Sub Workbook_Open()

MsgBox ("book1")

End Sub

in thisworkbook of book1? this is what stops it for me, even though macros are disabled

midget1500

anyone?

DJ Dunk

iTrader: (5)

Try the forums at MrExcel.com, they are incredibly helpful and solved my complicated Excel problem in seconds Saved me days & days worth of cutting,pasting & transposing

Michael Kuss

Hi,

I got a similar problem. I had to open several hundreds of different workbooks which contain macros (which were even buggy).
In order to use .AutomationSecurity=msoAutomationSecurityForceDisa ble
I had to add the "Office 10.0 Object Library" to the used librarys of my VBA code. But it did not give the desired effect. On my Excel XP all macros (including the one running) stopped immediately after opening the other workbook. (I found other users reporting the same problem in the web)

The following solution is simple (and works fine for me): Simply create another instance of Excel:

Dim wb As Workbook
Dim oExcel As Variant

Const conFileName = "Z:\HitchhikersGuideToTheGalaxy.xls"

' Create a new Excel-Object
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True

' disable macros
oExcel.AutomationSecurity = msoAutomationSecurityForceDisable

' open Read-Only without Updating Links
Set wb = oExcel.Workbooks.Open(conFileName, 0, True)

' ...

wb.Close

' close the Excel
oExcel.Quit
Set oExcel = Nothing


This works fine. The .AutomationSecurity stuff only works in newer versions of Excel. You could also disable macros by disabling events (.EnableEvents) and disabling automatic calculation (.Calculation) which should work fine in medium old versions of Excel ...

Hope, this is helpful to you.

Michael

dchodges

I cut your applet out and ran it in VB. I get an error (438:Object Doesn't support this property or method) at this line:

oExcel.AutomationSecurity = msoAutomationSecurityForceDisable

Any thoughts? I have the MS Office 10.0 and Excel 9.0 Object Libraries enabled.

Thanks.

Michael Kuss

Hi,

sorry for the late reply. I think the method

.AutomationSecurity=msoAutomationSecurityForceDisa ble

only works for Office >= Office XP (try to google for "msoAutomationSecurityForceDisable"). Do you have an older version of Excel running? If you have an older version of Excel running, you could try to disable macro execution by:
- disabling events (Application.EnableEvents)
- forbid automatic recalculation (Application.Calculation)

Otherwise the code below should work fine (at least it does so for me).
But I encountered another problem: In some cases the Excel process was still alive even after the corresponding object has been set to nothing. I solved this by killing the Excel process after the Excel object has been destroyed (this should be done in a small wrapper class which kills the Excel process when the class object is gone).

Michael

Fosters

stick this in your Worksheet module
when you dbl click on a cell which contains a filename, it'll open it with it's windows associated application

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Private Declare Function GetDesktopWindow Lib "user32" () As Long

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim HwndDT As Long
HwndDT = GetDesktopWindow()
ShellExecute HwndDT, vbNullString, Target, vbNullString, 0&, 1
End Sub

if it's a hardcoded file, change 'Target' to the filename to open (in the shellexecute line).