Prevent an on open macro from running when retrieving data

[Phanmore]

Good Morning All,

I was wondering if anyone could help me.

I have a main spreadsheet that opens another spreadsheet, copies some information and pastes it in to my main sheet.

The problem is, the workbook I am retrieving the data from has quite lenghtly code under the on open event.

Is there any vba code that will allow me to open the 2nd spreadsheet and disble the on open event?

If that's not possible, is there any other simple way to retrieve the data without opening the workbook?


This is the code my main workbook currently runs.

Sub ComplianceImport()
Application.ScreenUpdating = False
 
Dim ReadOnlyWB As Workbook
Dim ActiveWB As Workbook
Dim Sht As Worksheet

Set ActiveWB = ActiveWorkbook
Sheets("Database").Visible = True
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Database" Then
Sht.Visible = False
End If
Next Sht

ActiveWB.Sheets("Import Data").Visible = True
ActiveWB.Sheets("Import Data").Cells.ClearContents
 

Set ReadOnlyWB = Workbooks.Open(Filename:="\\0788 Group\Availability & Compliance\Compliance Update.xls", ReadOnly:=True)
ReadOnlyWB.Sheets("Database").Visible = True
ReadOnlyWB.Sheets("Database").Select
ReadOnlyWB.Sheets("Database").Cells.Copy
ActiveWB.Activate
ActiveWB.Sheets("Import Data").Select
Range("A1").Select
ActiveSheet.Paste
Cells.Copy
ActiveCell.PasteSpecial Paste:=xlPasteValues
ReadOnlyWB.Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Sheets("Database").Visible = True
Range("F2").Select
ActiveCell.Formula = "=IF(ISERROR(VLOOKUP(A2,'Import Data'!A:FE,161,FALSE)),""Not Imported"",VLOOKUP(A2,'Import Data'!A:FE,161,FALSE))"
ActiveCell.Copy
Range("J65536").End(xlUp).Offset(0, -4).Select
Range(ActiveCell, ActiveCell.End(xlUp)).Select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Import Data").Visible = False
Sheets("Checklist").Visible = False
Application.ScreenUpdating = True

End Sub

 

[VoG]

Try like this

Application.EnableEvents = False
Set ReadOnlyWB = Workbooks.Open(Filename:="\\0788 Group\Availability & Compliance\Compliance Update.xls", ReadOnly:=True)
Application.EnableEvents = True

 

[Phanmore]

Perfect Thanks.

The code doesn't run anymore, but it didn't speed it up as much as I'd thought it would. It may just be my netowrk