Altering application properties from vb using automation


Function ExcelApplicationEvents(oExcel As Excel.Application,bEventsStatus As Boolean) As Boolean

Dim xlTempBook As Workbook

On Error GoTo ErrFailed

Set xlTempBook=oExce.WorkBooks.Add

xlTempBook.VBProject.VBComponents.Add 1

With

xlTempBook.VBProject.VBComponents(xlTempBook.VBProject.VBComponents.Count).CodeModule

.InsertLines.CountOfLines+1,”Public Sub SetEventsStatus(bEventsStatus As boolean)”

.InsertLines.CountOfLines+1,Chr$(9) & “Application.EnableEvents=bEventsStatus”

.InsertLines.CountOfLines+1,”End Sub”

End with

oExcel.Run”‘”&xclTempBook.Name & “‘”!SetEventsStatus”,bEventStatus

xlTempBook.Close False

Set xlTempBook=Nothing

Exit function

ErrFailed:

Debug.Print”Error in ExcelApplicationEvents:” & Err.Description

Excel.ApplicationEvents=False

End Function

Private sub Form_Load()

Dim oExcel as Excel.Application

Set oExcel=New Excel.Application

Debug.Print “Application Events are:” & oExcel.EnabledEvents

ExcelApplicationEvents oExcel, False

Debug.Print “Application Events are:” & oExcel.EnabledEvents

ExcelApplicationEvents oExcel,True

Debug.Print “Application Events are:” & oExcel.EnabledEvents

oExcel.Quit

Set oExcel=Nothinh

End Sub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s