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
ActiveWorkbook.Close
close the inactive file
I'm using the
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.
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
We have one shared excel workbook and it is used by many people (more than 20 simultaneously), is there any way to auto save & close the workbook (session) if a user is inactive for specified time.
View 2 Replies View RelatedBasically 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 RelatedI 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 RelatedI 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 RelatedHow 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?").......................
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?
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 RelatedI'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:
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?
Variables:
MARM_fileNM
MARC_fileNM
MAKT_fileNM
Temp_fileNM
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))
Workbook.(dFile(j)).Close
Next j
End Sub
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.
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 RelatedI 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.
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 RelatedI 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 RelatedI know this is the code to close a workbook
View 5 Replies View RelatedI can save and create pdf, I just need help deleting the .xls file.
Her is code I have:
ActiveWorkbook.Save
Path = "C:Emailed Proposals"
On Error Resume Next
ActiveWorkbook.SaveAs Filename:= _
Path & Range("C6").Value & Range("O3").Value & ".xls"
On Error GoTo 0
Application.ActivePrinter = "PrimoPDF on Ne00:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"PrimoPDF on Ne00:", Collate:=True
Application.ActivePrinter = "EPSON Stylus C86 Series on Ne02:"
I need to keep the newly created .pdf and delete the created/saved .xls file, I just can't ssem to figure out what I need to write in code.
I have a template where users provide input in specified fields. Upon completion, pressing a macro button emails the file to administrator.
find out a code that once email is sent, perform following actions simultaneously;
Pop up message to appear as “Message Sent – Do you want to process another request?
If reply is Yes, the sheet is refreshed
If reply is No, File is closed without Saving
The code below works perfectly when run from a macro button on a worksheet or when initiated using the VBA run option in the VBA editor but it does not run when run from a command button on a userform. The code is
Sub closer()
Dim bCancel As Boolean
Do Until bCancel = True
bCancel = Application.Dialogs(xlDialogSaveAs).Show
If bCancel = False Then MsgBox "Sorry, cannot Cancel", vbCritical
Loop
ActiveWorkbook.Close SaveChanges:=False
End Sub
When I run the code from a command button on my userform the error message I get is: 'Run-Time error 1004 Document not saved' and the line of code that it continually gets stuck on is: bCancel = Application.Dialogs(xlDialogSaveAs).Show
I have a piece code that allows the user to pick a file from a folder on my C:. It then copies a range of data into the original workbook that the macro was run. I would like to close the other file down automatically which shouldn't be that hard but I can't work out how to do it.
View 9 Replies View RelatedI have a macro that runs and I want it to close excel when it is done. The best I can do is to get it to close the worksheet and workbook.
What is the code to make excel completely close?
I am trying to inch my way forward to writing a macro that will read in an external CSV file into a sheet (my next task will be to spread the date between multiple sheets based on regions). My first attempt at code is the following
Sub read_csv()
Dim strLine As String
Dim i As Integer
Dim filename As String
Dim ans As String
filename = "C:Documents and SettingsMichaelMina dokumentMina datakällorD123905.csv"
Open filename For Input As #1
i = 1
While Not EOF(1) And i < 10
Line Input #1, sLine
ans = MsgBox(sLine, vbOK, "hello there")
i = i + 1
Wend
' Close filename
End Sub
A couple of problems arise. When I debug the code, if I remove the comment on the close filename, the debugger complains with error 13 (incompatible types). If I comment the line and try and run the code twice in a row, I get a complaint about error 55 - file already open. I'm guessing that the close filename is the correct syntax - if it isn't, what should it be??
macro is not exiting from print preview mode. Need the VBA code so it can close the print preview mode (preferably not "private sub" code)
View 2 Replies View RelatedI have a workbook which should only be used between certain dates
In this example between 3rd March 2013 and the 1st March 2014
Would it be possible that if the user opens the workbook outside of this date range an error message could appear then the workbook would then close ?
I have a list of 10 shops as a list box named lstitems i need the user to click one of the 10 shops and when they click the selected shop it tells the name they selected. then i require a Quit button that transfers the selected Shop to cell D3 & then closes the userform, but if they do not pick a shop it will ask them to pick one before it closes. so they must select or it will not close.
View 9 Replies View RelatedI want code to:
Open EstimatingSheet.xls
SaveAs current workbook as Range B13 &".xls"
Open EstimatingSheet.xls workbook from somewhere on C:
Then close the one with B13.xls