Check If Workbook Open & Close It
Sep 22, 2007Code to check if a workbook is open, and close it, if it is.
View 4 Replies
ADVERTISEMENT
Refresh All Queries On Workbook Open When Finished Print To PDF Then Close Workbook
Jan 23, 2014I have data that daily needs to be refreshed and printed to pdf.
I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.
I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).
Close Active Workbook And Open Another
Jan 30, 2008Two workbooks named"test1.xls"test2.xls
I have a userform in "text1.xls" workbook when I click on commandButton it validates Staff name and Staff number stored in sheet1 of "test1.xls" workbook and opens test2.xls file which is in different folder. Problem is how do I code to close or unload "test1.xls" after opening test2.xls
Private Sub goButton_Click()
F$ = "D: estFiles est2.xls"
' can I use Path so I don't need to type the full directory path
Workbooks.Open Filename:=F$
End Sub
Macro To Open / Close / Save Workbook?
Mar 14, 2014I need to have a workbook (all of them in a folder, ideally) refresh in the middle of the night. The file is large so the refresh takes a full minute. I know I can't auto refresh a closed workbook (pretty sure anyway), and I can't leave the workbook open for various reasons.
So I'm thinking I could have a macro in an open excel (it kicks off on open) that will open a particular workbook elsewhere (the one I need refreshed), open it, refresh it and close/save it. Ideally it would do this for the entire group of workbooks in the folder but I'd be happy to start with just one. I would like to set it to occur every twelve hours (so at midnight, say, this would occur - I would just have to leave the workbook with the macro in it open on a computer so that it runs and completes this task every night).
Script To Run Macro On Workbook Open And Close
Nov 13, 2011I am relatively new to writing script in excel. I have a 34 sheet workbook that includes inputs, intermediate calculations and then reporting outputs.
I am trying to write macros to do the following
1. On opening - all sheets except a contents page (with macro buttons on it) are hidden
2. One of the macros on this page will unhide all sheets (i.e. a 'developer mode')
3. One of the macros will unhide all relevant input and reporting sheets (i.e. a 'user mode')
I originally set up macros to run on workbook opening that hid all sheets except the contents page and this was working but ran into trouble when I opened up the 'User Mode' saved my changes and exited - the macro didnt work when I went to open again (I think because some of the sheets were visible and some werent and it was trying to hide sheets that were already hidden).
To counter this I set up a macro that made all worksheets visible on workbook closing - this works except I keep getting a 'Save' prompt from excel because the location of selected cells (or the sheet I am viewing) has changed from last opening (not to mention the inputs may change).
I am thinking the best way to fix this as follows:
1. Keep the opening macro the same
2. Keep the user/developer macros the same
3. On closing - develop a macro that unhides all sheets after the user has selected whether or not they have made changes to the sheet in a pop up message box - if they choose yes then they are prompted to save as and then the macro to hide all sheets runs on the new version - if they choose no then they are not prompted to save the document (i.e. next time they open they will be opening back at the contents page with the same inputs they started with in the previous session).
Auto Close Workbook And Open Another After Inactivity
Nov 26, 2012I have a menu workbook with macro buttons that call different workbooks. Call the menu workbook (switchgear.xlsm). Switchgear contains 2 buttons. Button 1 closes switchgear and opens Book1. Button 2 closes switchgear and opens book 2.
If book1 is open and nothing has changed for 3 minutes I would like it to close and open switchgear.xlsm. I do not want it to just close in 3 minutes... I want it to close after 3 minutes of inactivity.
I have search all over and tried and tried to achieve this without success...
I have been able to get the timer to work but it gets caught in a loop. If I open book1 and close it in less than a min then 3 minutes later out of nowhere the code tries to run???
Secondly when this happens I see several book1 vba projects loaded in the editor window???
Auto Close Workbook If Kept Open For More Than 5 Minutes
Dec 21, 2006I have an excel file stored on a network drive for the purpose of information sharing. (File protected with a password)
But some the guys leave the file open for quiet long time and hence I cannot open the file for updating the data.
-I need to have a macro that runs every 5 minutes and displays an alert message saying "Please close the File" as long as the file is kept open.
-A second macro with a modified version of the above to close the file automatically after 5 minutes from file opening time after showing an alert message "You cannot leave the File Open, File is Closed Automatically!"
Code To: Open Another Workbook And Close Current
Jul 31, 2007I 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
Open & Close Workbook Without Screen Flicker
Jan 10, 2008I have a userform that opens a woorkbook and fills some textbox from that woorkbook. Although that it goes very fast it is still visible (sometime just in the toolbar) and flickers the screen. Is it possible to open it "hidden"?
Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
On Error Resume Next
Workbooks("Kontrollsystemet.xls").Close SaveChanges:=False
Workbooks.Open "V:allaBeredningKontrollsystemetKontrollsystemet.xls", ReadOnly:=True
Sheets("Calc").Activate
Label4 = Range("K7")
Label5 = Range("L7")
Workbooks("Kontrollsystemet.xls").Close SaveChanges:=False
Caption = Now
TextBox1.SetFocus
Application.ScreenUpdating = True
End Sub
MacRO: Check If WorkBook Open. If Not, Open It.
Sep 27, 2006I did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I´m using for tranfering the sheet
Sub Transfer_Sluttet()
If ActiveSheet.Index <> Sheets.Count Then
Application.DisplayAlerts = False
Set ws = ActiveSheet
Sheets(ws.Index + 1).Delete
ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2")
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
Application.DisplayAlerts = True
End If
End Sub
This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it
If IsWorkbookOpened("Filename.xls", "C:Documents and ..................
Open Workbook / Copy / Close Without Offer To Save?
Mar 12, 2014I've got a kind of "menu" sheet that will pull up various reports from Excel spreadsheets. Rather than having the user work with the live data, the menu opens the file and saves it as a copy, closing the original. This is working fine.
The tweak I want to add is, since the user is looking at a copy rather than the live data, I don't want them to save when closing. Is it possible to programatically add a bit of code to the new copy to handle this?
Here's a sample of one of the menu button's code:
Code:
Private Sub CommandButton18_Click()
Dim wb As Workbook
Set wb = Workbooks.Open(Filename:="J:Z PII OvensRack Repair TrackingRack Repair List.xlsx")
With wb.Worksheets("Sheet1")
.Unprotect Password:="pass"
ActiveWorkbook.Worksheets("Sheet1").Copy
End With
[Code].....
Open/close Workbook Depending On A Part Of String
Jul 26, 2007I have create a menu with buttons that are attached to the different projects of vba. This menu will be put in the server of my department so all my colleagues can take profit of the macros. Is a menu in an excel file that only contains the vba projects, not the sheets where the macros are supposed to run. My problem start when you want to open the file. It will always be in the same disk "P:COAP", and then there are more subdirectories (year, month)... so the total path, for example, this month is:"P:COAP2007200706". But every month, it changes, logically.
The problem is that i need to open a file with the name "COAP-200706 Concentració Empresa Emissora" in this case... the only thing that changes is month, and year...
I want to close the booke i choose, to avoid errors, if the name of the workbook chosen is different to a part of this string. In this case, i'm trying to close the book if the name of the file or the book don't contains the string "Concentració Empresa Emissora", without saving changes, of course... because sometimes people have chosen badly the files and they've made a disaster. If the file chosen contains this string in the name i want to continue with all the code that is written after.
ChDrive "P:"
ChDir "P:COAP2007"
NouFitxer = Application. GetOpenFilename
If NouFitxer = "" Then Exit Sub
Workbooks.Open Filename:=NouFitxer
If ActiveWorkbook.Name <> "*" & "Concentració Empresa Emissora" & ".xls" Then
Msg = MsgBox("Error in the file opened. Look at the name of the file next time." _
& vbCrLf & "The program will close without finishing the process. Start again. Thanks", _
vbOKOnly + vbCritical, ("END OF PROCESS"))
ActiveWorkbook.Close Savechanges:=False
Exit Sub
End If
Open Workbook, Find Last Row & Paste, Save And Close
Jun 13, 2008What i need to do is Open a Closed workbook (which i have done in the code below)
1 . When this book opens there is a msgbox that needs to have "template" pasted in to then ok automatically clicked ( i have no clue if this can even be done)
2. Then go to Sheet("contactor info") find last row then paste certain info. Below is a peice of code i have from the open workbook but of corse it doesnt work. The red is where it hangs up.
openpath = ActiveWorkbook.Path & ""
Set wb = Workbooks.Open(openpath & "Template.xls")
With wb.Sheets("Contractor Info")
. Cells(Sheets("Contractor info").Rows.Count, "B").End(xlUp)(2, 1).Select
ActiveCell.Value = Msg1
ActiveCell.Offset(0, 4).Value = Msg2
ActiveCell.Offset(0, 5).Value = Msg3
ActiveCell.Offset(0, 3).Value = Msg5
ActiveCell.Offset(0, 2).Value = Msg4
ActiveCell.Offset(0, 1).Value = Msg6
ActiveCell.Offset(0, 6).Value = Msg7
Application.Calculation = xlCalculationManual..................
Excel 2007 :: VBA To Save And Close All Open Workbooks Except Macro Workbook?
Mar 8, 2013I 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 RelatedCheck If Workbook Is Open & Open If Not Open
Jul 1, 2007I would like to write a Sub that will see if a workbook is open and if it is not then open it. I know how to have a macro automatically open a workbook, but I run into problems when the macro runs and tries to open an already opened workbook.
View 5 Replies View RelatedCan I Change A Setting Somewhere To Make Close Its Default Blank Workbook When I Open An Existing One
Feb 9, 2007I have Excel 2002. If I start the program it opens with a blank workbook, called "Book 1".
If I then open an existing workbook, I have two Excel buttons in the Windows taskbar: the one I just opened, and the blank one titled Book 1.
At work I have Excel 2003. The blank "Book 1" is also there when I start the program, but if I then open an existing workbook, the Book 1 goes away. I like this way better.
My question is: Is this just the way versions 2002 vs. 2003 work, or can I change a setting somewhere to make Excel 2002 close its default blank workbook when I open an existing one?
Check If Workbook Open If Not Open It
Aug 15, 2007Need some VB Code to check if a workbook is open or not. Either way, it needs to be opened and made active.
I have tried using some of the answers already on here but not getting anywhere.
ActiveX Combobox - Linked Cell Reference Lost After Save / Close And Re-open Workbook
Feb 11, 2013Using the developer tab I inserted an Active X combo box. Under properties I referenced a 'linked cell' that I want the data to appear in. This box works great as long as I keep the sheet open. Once I save and close the workbook and re-open, that reference is lost. It shows #REF! in that field. Why does it lose the reference? I have tried to reference a cell on the same sheet as well as a cell in a different sheet and it keeps the sheet name but not the individual cell.
View 13 Replies View RelatedHow To Check If CSV Workbook Is Open Or Not
Feb 27, 2013I have a code that close a csv workbook after use. But sometimes the book is not opened from the start of, and then I get an error trying to close it.
How can I easily determine if the workbook wbOBX is open or not?
Code:
If Not wbOBX Is Nothing Then
wbOBX.Close False
End If
Check If Workbook Already Open
Aug 19, 2006I have to files and a macro which copies data from one file to the other. The code goes as follows:
Sub test()
Range("A2").Activate
ActiveCell.Copy
ChDir "D:"
Workbooks.Open Filename:="D: est2.xls"
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Everything is ok as long as the second file (test2) - the one data is copied to, is closed the moment the macro starts acting. The problem is: when test2.xls was opened before the macro was activated a window pops up saying that all unsaved data in test2.xls would be lost.
The best thing to do is to write a piece of code stating that : if the target file is closed then the macro should open it and do its stuff, but if the target file is already opened than the macro should simply switch to the already opened workbook without trying to reopen it. All I need is a piece of code regulating that
Check .xla Workbook Is Open
Sep 23, 2006Y have files that are stored as .xla files because they are standalone applications that just contain the code.
Y need to test if one .xla file is open and if so close it, so i'am able to open another one. Otherwise the APP withevents from both APP get all mixed up.
I'am actually doing it from vb.net 2005 and i 'am using latebinding.
But the code would be similar y guess.
Check If Workbook Is Open
Jan 3, 2007I have a small group of excel files that I've tried to code to update a master file. I have borrowed code snippets and adapted the rest (still trying to learn). My appologies to those I borrowed from! I would give you full credit if I could remember who did what. Anyway, The files are on a network share folder and everything works perfectly for me, but when one of the people who this was disigned for tries to update it always says "File already open, please try again later." This message is built in to the code.
All the files are in the same shared folder (to make it easier for me) along with the master file.
Here is the code:
Private Sub CommandButton1_Click()
Const strMaster As String = "PMT_Master.xls"
Dim wbMaster As Workbook
Dim srcrng As Range
Dim destrng As Range
Check To See If Only One Workbook Open
Feb 13, 2007How do you check to see if this is the only workbook open. Basically, I want something like:
If only workbook open
application.quit
else
activeworkbook.close
Check Workbook Open From Another Pc
Jul 8, 2007how to check if a workbook is open or not, and then do something on the resualt. What I would like to know is, how can I make the program check if the file is open on another on the server where others can access it? I'm reading this back, not sure im explaining it right... If i have a macro that checks if a file is open, is it checking if its open on just my PC, or is it checking if its open else where?
This is the function Roy did for me: [code].........
Check Who Has Workbook Open
Jan 24, 2008I have some code setup to create hyperlinks to all the excel files relevant to the current project a user is working on - it details when each file was last modified so it's possible to see how the project is progressing - and whether or not they need to review any changes.
I could do with including a flag detailing if someone else currently has the file open (prior to them attempting to open the file - as they're more than likely to open as read only and end up saving under a new filename - which I don't want happening) - as many of the projects are shared (one user might be in Edinburgh, the other in London for Example). It would be handy to display the user's excel User Name (as I've persuaded everyone to update this). I've added messageboxes identifying Last modified by user x , dd/mm/yyyy hr:min to auto_open's in the past, but am experiencing brain-freeze today
Private Sub Workbook_Open()
Dim vFileName As String
Dim sLastSaveTime As String
Dim sLastAuthor As String
vFileName = ThisWorkbook.Name
Application.Volatile
sLastSaveTime = _
ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
Application.Volatile
sLastAuthor = _
ThisWorkbook.BuiltinDocumentProperties("Last Author")................
Check If A Workbook Is Open Macro
Jun 21, 2007I am looking to check if its open, if it is, then copy the row, if not, then close it.
I am still new to vb, so learning as I go, but looked at many other posts, and there are so many ways i have seen to open one, I'm not sure which is the best, and none that I have seen show me if they check if its open.
Check Criteria In Workbook On Open
Dec 25, 2007My company used Excel with VBA as a sales tool. We recently updated to new application all together. The sales force, being familiar with Excel and most of them finding it much easier is still giving old pricing quotes based on some of the built in product forms and calculating tools from the Excel files. I want them to still be able to view the excel files they made in order to see old customer data and projects they built but I needed to disable all the features so they can’t save as just build another quote using the tool
I've locked down a copy of the tool how I want it locked down but now I need to figure out a way that anytime excel is opened by anyone in the domain; I need to check if certain criteria exists on that workbook (Sheet1. Name = “xxx” And Sheet21.Name = “zzz”) etc. f it meets the criteria I need to import my revised modules (module1 and module3) and form (frmsplash) over the existing copies of the modules and form. If not I need to allow the intended workbook to open as intended.
They employees initially got the document by downloading it under the same name, but have since saved the document as many other names.
Check If Workbook Is The Only Excel File Open
Jul 17, 2009I am just wondering if there is a way of checking if the workbook that is currently open is the only one that is open, as I have been asked to make my code exit the application if this is the case rather that just close that one file.
View 6 Replies View RelatedCheck Which User Has File/Workbook Open
Jul 28, 2004I have a program that opens an excel-workbook, I first check whether the file is opened by another user(open for read-write). This works fine, but I'd like to know which user has the file open with VBa code. ex.
workbooks.open ....
if open
then msgbox " Book opened by user"
end if
So it's the same as you open an excel(with your windows explorer) on a network and you get the message that the file is already opened by the user....
Open 2nd Workbook From Userform Then Close 2nd Userform?
Jul 4, 2012What I would like to do is to be able to open a second workbook from a user form, preform some work on it then save and come back to the same place in the original user form.
So in steps:
1 : user clicks command button to open user form
2: user then clicks on command button on userform that opens 2nd workbook via a yes / no message box, but closes userform on 1st workbook (would be ideal if this could stay open, but hidden)
3: user then does work on 2nd workbook,
4: userform on 2nd workbook saves then activates the 1st workbook and reopens the userform
This is where no matter what I try I cant get the command button on the 1st userform to be clicked automatically so the yes / no message box appears.
This is part of the code in the 2nd sheet commandbutton that saves / closes / opens
Code:
Unload Me
ActiveWorkbook.save
Windows("ABC.xlsm").Activate
Sheets("Request Sheet").Activate
Call Sheets("Request Sheet").ForceClickOnBouttonXYZ
Call UserForm1.CommandButton6_Click 'this is where I cant get it to work!!
Windows("xyz.xlsm").Close
ABC is the 1st workbook
xyz is the 2nd workbook
This is the code on the 1st workbook I use to call on the 2nd workbook
Code:
Public Sub ForceClickOnBouttonXYZ() Call CommandButton1_Click End Sub