Opening workbooks via VBA

Nyanko

Active Member
Joined
Sep 1, 2005
Messages
437
When I use a macro to open a worksheet, how can I bypass any pop up messages ?

The sheet(s) I open have reminders that pop up due to a Workbook_Open event that I need to remain. However if a run a macro to open these files then the macro aborts as soon as I manually it the OK button ...

... grrrrrrrr something so easy I thought !
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Quick Partial solution: Some messages are bypassed by the line...

Application.DisplayAlerts = False
 
Upvote 0
Well I can't see a reason why the macro "aborts" perhaps you should clarify what you mean and provide an error. But here is one way to suppress your popups.
Code:
Option Explicit
Sub test()
OpenWB "C:\Documents and Settings\ME\Desktop\popup.xls"
End Sub
Sub OpenWB(WorkbookPath As String)
    Excel.Application.EnableEvents = False
    Excel.Workbooks.Open WorkbookPath
    Excel.Application.EnableEvents = True
End Sub
 
Upvote 0
Hi,

Thanks for the very quick replies !!! This is my code :

Code:
Option Explicit
Sub MonthSummary()

Dim NewPath As Workbook, OldPath As Workbook
Dim NewTab As Worksheet, OldTab As Worksheet
Dim a As Long, b As Long

Excel.Application.EnableEvents = False

'Sheets("Control").Select
NewPath = "R:\Rosters\Goods In\Roster 2007 Goods In.xls"
NewTab = "Chart"
OldPath = "R:\Rosters\ED Report Control.xls"
OldTab = "Sickdata"

Workbooks.Open Filename:=NewPath, updatelinks:=0, password:="abc123"
Sheets("Chart").Select

For a = 3 To 12
    For b = 3 To 12
    OldPath.OldTab.Cells(b, 2) = NewPath.NewTab.Cells(b, 1) 'Month
    OldPath.OldTab.Cells(b, 3) = NewPath.NewTab.Cells(b, 2) 'Total Shifts
    OldPath.OldTab.Cells(b, 4) = NewPath.NewTab.Cells(b, 3) 'Total Sck
    OldPath.OldTab.Cells(b, 5) = NewPath.NewTab.Cells(b, 4) 'LTLA
    OldPath.OldTab.Cells(b, 6) = NewPath.NewTab.Cells(b, 5) 'Total Sck + LTLA
    OldPath.OldTab.Cells(b, 7) = NewPath.NewTab.Cells(b, 6) 'Sick %
    OldPath.OldTab.Cells(b, 8) = NewPath.NewTab.Cells(b, 7) 'LTLA %
    OldPath.OldTab.Cells(b, 9) = NewPath.NewTab.Cells(b, 8) 'Total Sck + LTLA %
    OldPath.OldTab.Cells(b, 8) = "department"
    Next b
Next a

Excel.Application.EnableEvents = True
End Sub

It's a work in progress ... but at least now it's not because of the popups. I think I have other issues now !!!!

btw... what's the difference between Excel.Application.EnableEvents and Application.DisplayAlerts ??

Thanks again !!!
 
Upvote 0
"EnableEvents =False" will prevent any events (such as workbook open, etc.) from being triggered when said event occurs. Wheras "DisplayAlerts = False" simply supresses an iherent warnings from Excel. (Such as "You are about to delete this sheet are you sure you really want to do that?")
 
Upvote 0
Hi, thanks again for reading this ... I'm running into a few issues that I need some assistance with !

Premise
To update one summary workbook from various closed(& passworded) files for use in a pivot. All the child workbooks contain a tab called "Chart" where the data is held in table form in the same place and format.
e.g
MONTH....SHIFTS....SICK....PERCENT
January....1275......38........2.98%
February..1187......58........4.89%

I need to run a macro to: open each child workbook in turn; unpassword; override any pop up messages; copy the table from the chart tab and paste into the parent sheet so that each child data table is overwritten one after the other.

I'm probably over complicating matters in order to condense code in otherways I've learned !! Please can someone help me understand where I've gone wrong with the following code :

Code:
Option Explicit
Sub MonthSummary()

Dim a As Long, b As Long
Dim NewPath As Worksheet, OldPath As Workbook
Dim NewTab As Worksheet, OldTab As Worksheet

Application.ScreenUpdating = False

'Sheets("Control").Select
NewPath = "R:\Rosters\Goods In\Roster 2007 Goods In.xls" 'to be linked to a cell reference later
NewTab = "Chart"
OldPath = "R:\Rosters\ED Report Control.xls" 'Parent file
OldTab = "Sickdata" 'Destination Tab

Workbooks.Open Filename:=NewPath, updatelinks:=0, password:="abc123"
Sheets("Chart").Select

For b = 3 To 12
    OldPath.OldTab.Cells(b, 2) = NewPath.NewTab.Cells(b, 1) 'Month
    OldPath.OldTab.Cells(b, 3) = NewPath.NewTab.Cells(b, 2) 'Total Shifts
    OldPath.OldTab.Cells(b, 4) = NewPath.NewTab.Cells(b, 3) 'Total Sck
    OldPath.OldTab.Cells(b, 5) = NewPath.NewTab.Cells(b, 4) 'LTLA
    OldPath.OldTab.Cells(b, 6) = NewPath.NewTab.Cells(b, 5) 'Total Sck + LTLA
    OldPath.OldTab.Cells(b, 7) = NewPath.NewTab.Cells(b, 6) 'Sick %
    OldPath.OldTab.Cells(b, 8) = NewPath.NewTab.Cells(b, 7) 'LTLA %
    OldPath.OldTab.Cells(b, 9) = NewPath.NewTab.Cells(b, 8) 'Total Sck + LTLA %
    OldPath.OldTab.Cells(b, 8) = "department"
Next b
End Sub

Or am I better off sticking to old school set by step for this procedure :biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top