Close All Open Workbooks Except......

Feb 25, 2008

I made an Excel based program that uses multiple workbooks with two main workbooks (“Master List” and “Products”) that all the others pull information from. I run into a problem when a user clicks the close X in the upper right hand corner and the entire application closes. I found some code that will let me close all the workbooks but one (the code is put in the “Products” workbook in the BeforeClose event).

For Each wb In Workbooks
If Not wb Is ThisWorkbook Then
wb.Close SaveChanges:=True
End If
Next wb

Is there a way to modify the code so it will close all the workbooks but the two main ones if someone clicks the close X button in the upper right hand corner?

View 3 Replies


Dynamic Open/Close Of Workbooks Crashes

Dec 15, 2009

I'm working on developing a stock option trading system in Excel using a live data feed and VBA. It tends to crash after running flawlessly for a half hour to 2 hours. It seems to crash when loading a support workbook, so I was hoping you could take a peek at my code and see if there's a problem with the way I'm dynamically opening/closing workbooks.

In order to cope with limitations in the amount of live data that I'm accessing (through DDE with Reuters Station), I've split up the data I need into about 600 workbooks, which open when they're needed or close when they're not (through VBA). At any given time, about 35-40 of these workbooks are open.

The main workbook that uses all this live data and support workbooks is set to have the application recalculate every 5 seconds (automatic recalc crashes the program almost immediately). After that refresh, it calls other subroutines, including opening/closing these support workbooks:

View 14 Replies View Related

Open, Update And Close Multiple Workbooks

Sep 5, 2007

I have 30 workbooks closed and i want update a cell (f.e. b4) in all the workbooks.
Need code to open the files, update the data in that cell and close the workbook.

View 5 Replies View Related

VBA To Open External Workbooks With Variable Filenames And Close

Nov 29, 2013

I'm trying to create a VBA macro which will open external workbooks in a specific folder. thes workbooks will have filenames which are identical to the value in cell B1 of my active workbook. Once open, I would use indirect to pull certain results from that external workbook and then close it. I have a separate macro already which runs through the list. I found a few examples online but they don't seem to work.

View 2 Replies View Related

Automatically Open, Copy, And Close Multiple Workbooks

Jul 26, 2006

I have 77 excel workbooks that are created each week, they are all in the same directory. I have to open each one and copy the data into a single workbook. The file names change slightly each week.

example: DIST_91124_GROWTH_PRODUCT XXX _07072006.xls The 07072006 is the week ending date and will change. Each of the 77 files has a different DIST_number.

Is there a way to automate this process, it takes me about 4 hours to do it manually.

View 9 Replies View Related

Excel 2007 :: VBA To Save And Close All Open Workbooks Except Macro Workbook?

Mar 8, 2013

I have a macro that opens all workbooks from one directory and runs a macro for each workbook to clean up the data. I cannot figure out how to take all those open workbooks and save them to another directory and close the workbook. Also, I do not want the macro workbook (xlsm) to save. I only want it to close. I am working in 2007 Excel.

View 8 Replies View Related

Close Workbooks And Move In And Out Of Workbooks In VBA

Dec 27, 2009

Basically the main workbook opens 2 files at a time performs a calculate in the main workbook and then copies and pastes information in 3 ranges. Then closes the two open workbooks and loops and performs same operations until it hits the maximum loops. My macro is as follows and I have 3 question in capital letters.

View 3 Replies View Related

Close All Workbooks In Other Folder

Dec 22, 2009

I have several workbooks in a make your day folder categorized under several sub folders. Each workbook has a macro that calls the following macro:

View 2 Replies View Related

Close Workbooks In Other Instances

Feb 5, 2010

I have one main workbook that, when opened, opens three other workbooks each in its own instance of Excel. I'd like to know how to close all three of these upon closing this main workbook.

View 2 Replies View Related

Close Multiple Workbooks

Feb 16, 2007

How can I close the currentmonthend AND previousmonthend workbooks in the following code, but leave workbook "MoEnd Compare" open? I'd like to do some additional comparisons with the "MoEnd Compare" file. I tried the code below to close those two workbooks, but it is not working.

Sub check_month_end()
Dim currentmonthend
Dim previousmonthend
Dim project_current
Dim ptcurrent
Dim yearcurrent
Dim project_previous
Dim ptprevious
Dim yearprevious
Dim r As Integer
Dim c As Integer
Dim l As Integer
MsgBox "This will check project type and year on current and last period month end report."
currentmonthend = Application. GetOpenFilename("Excel Files (*.xls),*.xls", , "Select CURRENT Month End Report?")
previousmonthend = Application.GetOpenFilename("Excel Files (*.xls),*.xls", , "Select PREVIOUS Month End Report?").......................

View 2 Replies View Related

Close All Workbooks Except For The Main Workbook

Jan 5, 2009

Is there anyway to change the following code so that it will save and close all workbooks except the one that has this code?

View 3 Replies View Related

VBA Code To Close Inactive Workbooks

Sep 7, 2009

I tried to use Workbooks("Inactivebookname.xlsm").Close False
and many variations thereof, but I simply cannot close this workbook when currently another workbook is active.

I have no problem in closing an active workbook with

View 9 Replies View Related

Suppress Open Events When Using Workbooks.Open

Dec 3, 2008

I have built a sub that prompts the user for a folder then opens every workbook in the folder 1 at a time to get stats on the contents of each workbook. Worked like a dam until I ran into an unexpected bug. Some of the users built on open events in their workbooks. ...

Right now my routine inventories workbooks to get formula counts, cell counts, most complex formula, highest value... it does this by looping throught the sheets and the cells. If there is a way of obtaining those stats without opening the workbook I may need to rethink a lot of my work.

way to suppress the code in the target workbook I open through

View 9 Replies View Related

Close Workbooks (with Variable Names) Without Saving

Mar 7, 2014

I have completed a long macro, and at the end of the macro I want to close a few workbooks automatically without saving. These workbooks have been assigned variable names.

Is there a command line I can use, that will not prompt the user to save?


View 3 Replies View Related

Workbooks.Close Function Stops Code

Feb 16, 2009

We're using XP Pro using Excel 07

The background is there are about 40 people who use a Excel based program that contains a pivot and a bunch of other tools that they use on a regular basis, this set of tools has a version number. In this excel workbook, it has a function that looks on the network drive that we have and checks the local version vs the version on our network drive, if its wrong, then the user gets a popup stating your tools are out of date, would you like to update, then they click yes and I have these lines of code

Public Function GetNewTools()
Dim MyFullName As String

'Turn off alerts
Application.DisplayAlerts = False

'Open the new version of tools
Workbooks.Open Filename:= _
"Network DriveUpdate.xlsm"

End Function

The update file has this code that executes on fileopen in the thisworkbook section by calling the following sub

The main issue we're running into is near the very end, the code never makes it to "TEST 2". After the first workbooks close, the code just stops running. No crashes, errors, freezes, anything. It just stops running and never makes it to the second msg box.

View 9 Replies View Related

Close Workbooks Saved As Elements In An Array

Feb 3, 2010

I have an array of data type Variant, who's elements are workbooks opened by a user.

The array size is static, which for now isn't a concern but I can't work out how to close the workbooks in the array via a loop and the usual vba code of Workbooks("file").Close

Code I have that doesn't work is:

Sub Close_Workbooks_In_An_Array ()

Dim dFile (1 to 6) As Variant
Dim i As Integer, j As Integer

' // Some code to open files, set each dFile(i) as a file and then process
' // them. Max value for i is 6

j = 1
For j = 1 To i
MsgBox ("Closing: " & vbNewLine & vbNewLine & dFile(j))
Next j

End Sub

View 9 Replies View Related

Before Close Code Errors When Closing From Other Workbooks

Nov 21, 2006

I have a workbook which includes a simple set of options on closing such as selecting the front sheet, restoring scrollbars and saving the workbook. To avoid problems with subscripts out of range I am using the ThisWorkbook statement to close the workbook.

This works fine and causes the workbook to close when close is clicked on any excel window. The problem is that excel falls over when it tries to resume closing the other workbooks. I am given (ironically) an error saying "excel has encountered a problem and needs to close". Does anyone know how to work around this?

My code is below:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call Toolbars9(True)
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayWorkbookTabs = True
End With

View 8 Replies View Related

Open Close VBA

Apr 14, 2008

Can I take

Workbooks.Open ("N:Data ManagementDashboardLOLTickler Codes.xls")
and do something like this?

Workbooks.Open ("N:Data ManagementDashboardLOLTickler Codes.xls") Not _
Workbooks.Open ("N:Data ManagementDashboardLOLTickler Codes.xls")

I have a userform that I would like with a click of a button to open the workbook and then click the same button to close the workbook after it has been viewed.

View 9 Replies View Related

File Is Getting Close When Open It

Jan 16, 2009

When i open excel it give an error and it close. My computer up to date about office upgrades. And i try to repair my office but it doesnt solve my problem.

View 7 Replies View Related

Vba To Open And Close The File?

Jun 3, 2014

Below is the code to open a csv file and paste it in the workbook "ResultFile". When i click on a button, a csv file will open to copy the values and paste it in "Resultfile". Now, i want to close the csv or excel file to be closed once copying has done.

where to put the close file.

[Code] ....

View 2 Replies View Related

VBA On Open/Close Conumdrum

Jul 27, 2006

I'm creating a template that will be opened by users and saved to a new name based on info provided by the user. I have macros triggered when opening and closing the workbook. The Before Closing sub does some data transfer between workseets. I'm running into a problem with the Open sub, however. I wanted to give the user a chance to abort the opening, giving a dialog that allows canceling. I wanted to just close the workbook.

However, if I include an Active.Workbook.Close line in the Open sub, it of course triggers the Before Close sub, but it leaves the On Open sub hanging. The workbook closes, but the next time I open it, the dialog in the Open sub doesn't come up. Close again, and it works ok or quit Excel between openings and it is ok. If I let the Open macro complete, bypassing the remainder of the macro if the user indcates canceling, how to I trigger closing within VBA? If the Open macro completes without an Activeworkbook.close statement things will just sit there until the user initiates closing.

Is there a way to terminate the Open sub from within the Before Closing sub if I create a flag to indicate closing started during the open sub or some other way around this problem?

View 5 Replies View Related

Open, Print And Close

Apr 10, 2008

When i open an excel file, i need it to print 3 copies and then close itself without saving. This is because i have a file that must have 3 copies printed everyday. I know its lazy =) but i'm sure it will be very useful and i have seen it on a file before however i cannot successfully replicate it.

View 3 Replies View Related

Macro Close Workbooks - Large Amount Of Data On Clipboard Msg

Mar 8, 2005

I am using the close.workbook command in VBA to close a workbook I opened to copy data from. I am getting a message that says "You have a large amount of data on the clipboard. Do you want it available?" What command do I use in VBA say no to this message?

View 5 Replies View Related

Open Userform When You Close A Spreadsheet

Mar 5, 2007

I've got a 'Control Panel' spreadsheet which is all based on UserForms. When a user clicks a particular button, it opens one of many separate 'Regional' sheets which they work in.

When the user closes the 'Regional' sheet, I would like the 'Control Panel' sheets userforms to show again. However I can find no way of doing this as it simply switches back to the 'Control Panel' sheet without loading the forms (as I had to hide the 'Control Panel' useforms to give them access to the 'Regional' sheet).

View 9 Replies View Related

Using VBA To Open And Close Source File

Feb 1, 2010

I am working with a bunch of files to do a dashboard. I have the main file which shows end results and what my users will see "Dashboard". I have about 10-15 files which I export weekly from MS Access. and I have the "Dashboard Data" file. In this file I have a sheet which links all the information for the files exported from MS Access. In here I have named ranges and dynamic ranges plus additional columns created to look up information in other tables. I use this file as my source data for my Dashboard File so that I can reduce the size of the actually dashboard. Dashboard Data file is about 5.5 mb and my Dashboard is 300kb. I have 12 branches that need to view the data over a radius of a hundred miles so the smal file are better.

My problem: I can get the Dashboard Data file to update without opening all the 10-15 other files. I cant however make the Dashboard file update without opening the Dashboard Data file. I have it to auto update without prompting but its still tells me that it cannot update. I was wondering if there is some VBA code I could use that would work like this:

I open the Dashboard File, Dashboard Data file opens so that Dashboard file updates and then close Dashboard Data File so that the user never has to see the Dashboard Data file. I dont want them to see it or have access to it.

View 11 Replies View Related

Close ALL Open Windows Folders

Jul 10, 2012

I use the below code to close ALL open Internet Explorer pages. Im trying to adapt this to close ALL Windows Explorer Folders.

Dim Shell As Object
Dim IE As Object
Dim i As Variant
Set Shell = CreateObject("Shell.Application")
i = Shell.Windows.Count
On Error Resume Next
Do While i > 0
i = i - 1
Set IE = Shell.Windows(i)
If TypeName(IE.Document) = "HTMLDocument" Then IE.Quit


View 3 Replies View Related

Open Worksheet - Refresh And Close

Feb 11, 2013

I have a batch script which runs the following line to open my excel sheet:

start m:exportad.xls

when this sheet opens it automatically refreshes from a csv file.

The problem is that I want this sheet to open, refresh and close. To close Excel I have been using the taskkill command in my batch script, but I have found that using this method doesn't update the sheet properly - the sheet only updates properly if I close Excel in the correct way, but this means manual input and I want this to be automatic.

View 3 Replies View Related

Open And Close File With No Extension

Feb 21, 2013

I opened a file, by file I mean a "file" with no extension that has commas separating it.

So I recorded a macro to open up the file and this works fine.

How do I close it? I tried

wk1 = ThisWorkbook.Name 'Main Worksheet

But the problem is that "wk1" is not set equal to the name, probably because the file is not an excel file, it has no extension.

View 2 Replies View Related

Grouping Open / Close Shortcut?

Apr 30, 2013

I know the shortcut for grouping columns in excel is Alt + D + G + G or Shift + Alt + Right Arrow. Is there a shortcut to actually open and close grouped columns (i.e., the equivalent of clicking on the + sign at the top) without using the mouse?

View 1 Replies View Related

Close Open Instance Of Excel

Aug 6, 2013

I use the following code to open an existing open workbook in a new instance of Excel...

Everything works fine... What I would like to do is close (when I say close I mean, completely exit/quit the application). I'm not able to do that, the original Excel instance stays open with no spreadsheet open. I would just like it force the application to quit so that after running the code I would just have one Excel instance open (with the desired workbook, which works fine now)...

Public Sub Re_open_workbook()
Application.DisplayAlerts = False


View 1 Replies View Related

Copyrights 2005-15, All rights reserved