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 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
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
I have a bunch of workbooks that are used to capture information about implants used in surgical cases. I use a small VBA process each morning to update a querytable which contains all of the core patient information for future cases, however it is not working properly. The code goes through the process OK, or so it appears, opens and closes each sheet, and saves them appropriately, but the query table doesn't seem to refresh no matter what I do.
Sub RevisedRefresh() Dim lCount As Long Dim wbResults As Workbook Dim wbCodeBook As Workbook Application. ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next Set wbCodeBook = ThisWorkbook With Application.FileSearch .NewSearch .LookIn = "G:SHSTheatreCommonReceptionImplants" .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then For lCount = 1 To .FoundFiles.Count Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)...............................
In the last week the following code has ceased to work, and I do not know why:
If TradeShow = True Then ReportFile = " ANALYSIS PRINT (CS+T) MASTER.xls" Else ReportFile = "ANALYSIS PRINT (CS ONLY) MASTER.xls" End If Workbooks.Open Filename:=ReportFile, ReadOnly:=True Worksheets("TRANSFER DATA").Select PriceFile = "PRICE COMPARISON.xls"
The Workbooks.Open command executes, and the screen shows the cells of the new spreadsheet. Nothing else happens, and the cursor is sensitive to operator control, showing that Macro control has been lost. I have inserted a test 'MsgBox' after the .Open command, and not even that is executed - so I am certain that the .Open is in some way wiping out the Macro.
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.
My code below works great all the way to the end. When the last message box comes up, If the user chooses Yes. I want them to be directed to the sheet "C123". But currently it just stays on the sheet that I named "Home".
What am I missing here?
Private Sub CommandButton1_Click()
Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Log")
'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row
'check for a Reason Code If Trim(Me.ComboBox2.Value) = "" Then Me.ComboBox2.SetFocus MsgBox "Please enter a reason code and ensure that date is correct!" Exit Sub End If
I have 3 Work books - one (WB1) checks its version number against an online record and if they dont match it opens WB2 which then proceeds to download the updated version copy information from WB1 to its self which it then pastes into WB3
WB1 then gets deleted and WB3 Renamed to the old name of WB1
the issue i have is when i open WB2 using the button that also checks for updates on WB1.
WB2's code seems to just stop when it gets to the point of updating WB3 no error messages or nothing it just stops!
I am using Excel 2003 work PC, and when i run this simple code it stops during the loop, I have had this problem a bit its like something is hitting the esc key or ctrl - break. But no keys are being hit or are sticking.
I have closed Excel and created new work book pasted the code in but it still stops at r = r - 1, haven't done a restart yet.
Sub Macro2() Dim r As Integer r = 10 Do Until r = 0 ActiveCell.Value = ("Shut down in " & r) Application.Wait Now + TimeValue("0:00:01") r = r - 1 Loop Application.Quit End Sub
I'm using Excel 2007 and my s/s is 360000 rows deep. To cut+paste formulas+formats from one column to another I'm using the following Sub move_formula_and_formats_from_I_to_L()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Range("I1", Cells(Rows.Count, "I").End(xlUp)) With cell If .HasFormula And Not .Offset(1).HasFormula Then .Cut Destination:=.Offset(1, 3) End If End With Next cell
Application.ScreenUpdating = True
End Sub The code stops working after 159000 rows and highlights (in yellow) the line: .Cut Destination:=.Offset(1, 3)
I have an Access program that acts as a dashboard to open Excel reports. One of the Excel workbooks opens a form in the Auto_Open routine. My problem is that control is never passed back to access after the form opens. It appears that the code stops executing until the userform is closed. How can I open the excel workbook, show the Excel userform, and continue processing my Access code?
Set XL = Excel.Application With XL .Workbooks.Open FileName:=FileName, ReadOnly:=True . ActiveWorkbook.RunAutoMacros xlAutoOpen End With XL.Visible = True
' Excel Code Sub Auto_Open() Unload UserForm1 UserForm1.Show ' The code get "stuck" right here End Sub
I am attempting to show a User Form during the forms Initialize code. I Load the form after extracting data from a SQL Server database. The code that Loads the form is returned to after the form closes. The problem is that when I run through the Initialize code where criteria decides whether the form is actually shown to the user. If the criteria is not met there are no issue, but if the criteria is met, then the form is opened and the rest of the Initialize code is not stepped through. As there are many If Then and a Select Case and a couple With...'s I need the rest of the Initialized code to be stepped through so after the form is closed by the user and the original code which Loaded the form is returned to I do not get an error.
Oh and the big problem is that I am not getting an error message during this, the Load code is actually repeated because when the original code is actually returned to is returned on the Load UserForm code.
Private Sub UserForm_Initialize() Dim i As Integer, Endofdata As Integer, wsheet3 As Worksheet, wbBook As Workbook Dim wsSheet As Worksheet, c As Variant, gTotal As Long, Score As String, g3000 As Long Dim gTotalAdd As Integer
On Error Goto ErrorHandler: Application. ScreenUpdating = False 'cmbIDCountry.ColumnCount = 2 Set wbBook = ThisWorkbook Set wsheet3 = wbBook.Worksheets("UpdateDetails") Set wsSheet = wbBook.Worksheets("Data") ....................
I usually just use macros to clean up Data from non-excel sources. So I wrote a macro to do this, but the process requires a date to be added, so attempted to do this via an input box. The input box works, but the code doesn't, clicking the button to which the macro is bound spits out an error 91 (object out of bounds?) when it hits the search function right after the inputbox code. Both bits work as intended separately, so I guess it's just some moronic formatting error on my behalf.
I have built a small userform with 3 fields. The macro ran OK first few times. Now, when user enters data in the form and clicks OK nothing happens. I found that repeated clicking on OK or Cancel button on form had no effect. I then observed that VBA editor was open and the yellow cursor was displayed on the first code line under cmdOK_click procedure. The code line was also highlighted in yellow. There are no errors to debug and no break points etc. When I clicked the Run (or Continue) icon from the VBA toolbar, the macro completed OK. Question: Why is the macro pausing on the first line and how can I make it run without pausing for no apparent reason.
I have a Userform that allows a user to select a year eg (2007) from a listbox called yearbox where Yearbox.Value = AddYear. When the user Presses 'OK' the following code runs: ...
I have a textbox from the drawing toolbar. When someone changes a cell then clicks in the textbox, Worksheet_Change does not run. If they double click in the cell, that's OK I can capture that event and protect the sheet, stopping them clicking in it. But if they just start typing in the cell, I can't capture that. I have seen some API code which captures keypresses, but it is not practical to use as it loops repeatedly. I could lock the textbox and have the user do something to unlock it, but this is a last resort.
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.
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?").......................
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?
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:
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?
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
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.
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.
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?
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.
