Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
Greetings folks! How do you check to see if a workbook is open? And furthermore, if you do find one that is open, is there a way t force that open workbook to save? Maybe by initializing a loca function or sub on the open workbook which just saves the sheet? Also, what is the VBA code to open a workbook minimized? Thanks in advanced for your time :) -- ph ----------------------------------------------------------------------- ph8's Profile: http://www.excelforum.com/member.php...fo&userid=1987 View this thread: http://www.excelforum.com/showthread.php?threadid=51815 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
"ph8" wrote in message ... Greetings folks! How do you check to see if a workbook is open? Public Function IsWbOpen(ByVal Name as String, Optional ByRef Wb as Workbook= Nothing) As Boolean On Error GoTo Failed Set Wb = Workbooks(Name) IsWbOpen = True Exit Function Failed: Err.Clear Set Wb = Nothing IsWbOpen = False End Function And furthermore, if you do find one that is open, is there a way to force that open workbook to save? Maybe by initializing a local function or sub on the open workbook which just saves the sheet? Public Sub SaveWorkbookIfOpen(ByVal Name As String) Dim Wb as Workboook If IsWbOpen(Name, Wb) Then Wb.Save End If End Sub Also, what is the VBA code to open a workbook minimized? Public Function OpenWorkbookMinimised(ByVal Name As String) As Workbook ' This will cause an error if the workbook Name cannot be opened (e.g. it doesn't exist, isn't a valid workbook etc.) Set OpenWorkbookMinimised = Workbooks.Open(Name) ' Opening a workbook causes it to become the active window: ' There is probably a better way of doing this ActiveWindow.WindowState = xlMinimized End Function HTH, Stewart |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
try this
Private Sub Workbook_Open() ActiveWindow.WindowState = xlMinimized Application.DisplayAlerts = False ActiveWorkbook.Save Application.DisplayAlerts = True End Sub Best N10 "ph8" wrote in message ... Greetings folks! How do you check to see if a workbook is open? And furthermore, if you do find one that is open, is there a way to force that open workbook to save? Maybe by initializing a local function or sub on the open workbook which just saves the sheet? Also, what is the VBA code to open a workbook minimized? Thanks in advanced for your time :). -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=518154 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
On Error Resume Next
Set oWB = Workbooks("Test_WB.xls") On Error Goto 0 If Not oWB Is Nothing Then oWB.Save End If Workbooks.Open "test_WB.xls" ActiveWindow.WindowState = xlMinimized -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ph8" wrote in message ... Greetings folks! How do you check to see if a workbook is open? And furthermore, if you do find one that is open, is there a way to force that open workbook to save? Maybe by initializing a local function or sub on the open workbook which just saves the sheet? Also, what is the VBA code to open a workbook minimized? Thanks in advanced for your time :). -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=518154 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
Thanks for your replies! But I'm afraid I may need some more assistance... Ok. I need a little help with my code. I think I have it right, but I may have misunderstood some of the given examples. Plus, I don't think I am using the "isOpen" property correctly... Code: -------------------- Private Sub ForceUpdateAll() Dim paths(6) As String Dim thedir As String Dim UpdateWarn As String Dim i As Integer UpdateWarn = "WARNING: Warning text goes here..." thedir = Cells(1, "a").Value 'A1's contents are hidden, A1 contains a formula which ' returns directory of current file 'set paths here paths(1) = thedir & "folder1\file.xls" paths(2) = thedir & "folder2\file.xls" paths(3) = thedir & "folder3\file.xls" paths(4) = thedir & "folder4\file.xls" paths(5) = thedir & "folder5\file.xls" paths(6) = thedir & "folder6\file.xls" Dim ATUstatus 'variable for AskToUpdateLinks Status If MsgBox(UpdateWarn, vbYesNo, "Update Links") = vbYes Then If Application.AskToUpdateLinks = True Then ATUstatus = 1 Else ATUstatus = 0 Application.AskToUpdateLinks = False 'disables prompt to update links '(automatically chooses to update them) For i = 1 To 6 If paths(i).IsOpen Then paths(i).Save Else Workbooks.Open (paths(i)) ActiveWindow.WindowState = xlMinimized ActiveWorkbook.Save ActiveWorkbook.Close End If Next i If ATUstatus = 1 Then Application.AskToUpdateLinks = True End If End Sub -------------------- This is what I have. Its intent is to open files which are NOT open, and save them. These files are in a hierarchy and this is the only way data can go up to the top levels. If a file is open, it will reopen the already saved file and save it again, which won't actually accomplish anything come to think of it. So that part can probably be edited out. Unless there is a way to force a save on an open worksheet with the Open worksheet's data? Either way, if the sheet is not open, it should open the file (minimized), save it and close it. Did I do this right? Any help would be appreciated. Thanks guys! -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=518154 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
What is IsOpen? Array items do not have properties or methods, so even if
that IsOpen exits, you couldn 't use it like that Private Sub ForceUpdateAll() Dim paths(6) As String Dim thedir As String Dim UpdateWarn As String Dim i As Integer Dim oWB As Workbook UpdateWarn = "WARNING: Warning text goes here..." thedir = Cells(1, "a").Value 'A1's contents are hidden, A1 contains a formula which ' returns directory of current file 'set paths here paths(1) = thedir & "folder1\file.xls" paths(2) = thedir & "folder2\file.xls" paths(3) = thedir & "folder3\file.xls" paths(4) = thedir & "folder4\file.xls" paths(5) = thedir & "folder5\file.xls" paths(6) = thedir & "folder6\file.xls" Dim ATUstatus 'variable for AskToUpdateLinks Status If MsgBox(UpdateWarn, vbYesNo, "Update Links") = vbYes Then If Application.AskToUpdateLinks = True Then ATUstatus = 1 Else ATUstatus = 0 Application.AskToUpdateLinks = False 'disables prompt to update links '(automatically chooses to update them) For i = 1 To 6 On Error Resume Next Set oWB = Workbooks.paths(i) On Error GoTo 0 If Not oWB Is Nothing Then paths(i).Save Else Workbooks.Open (paths(i)) ActiveWindow.WindowState = xlMinimized ActiveWorkbook.Save ActiveWorkbook.Close End If Next i If ATUstatus = 1 Then Application.AskToUpdateLinks = True End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ph8" wrote in message ... Thanks for your replies! But I'm afraid I may need some more assistance... Ok. I need a little help with my code. I think I have it right, but I may have misunderstood some of the given examples. Plus, I don't think I am using the "isOpen" property correctly... Code: -------------------- Private Sub ForceUpdateAll() Dim paths(6) As String Dim thedir As String Dim UpdateWarn As String Dim i As Integer UpdateWarn = "WARNING: Warning text goes here..." thedir = Cells(1, "a").Value 'A1's contents are hidden, A1 contains a formula which ' returns directory of current file 'set paths here paths(1) = thedir & "folder1\file.xls" paths(2) = thedir & "folder2\file.xls" paths(3) = thedir & "folder3\file.xls" paths(4) = thedir & "folder4\file.xls" paths(5) = thedir & "folder5\file.xls" paths(6) = thedir & "folder6\file.xls" Dim ATUstatus 'variable for AskToUpdateLinks Status If MsgBox(UpdateWarn, vbYesNo, "Update Links") = vbYes Then If Application.AskToUpdateLinks = True Then ATUstatus = 1 Else ATUstatus = 0 Application.AskToUpdateLinks = False 'disables prompt to update links '(automatically chooses to update them) For i = 1 To 6 If paths(i).IsOpen Then paths(i).Save Else Workbooks.Open (paths(i)) ActiveWindow.WindowState = xlMinimized ActiveWorkbook.Save ActiveWorkbook.Close End If Next i If ATUstatus = 1 Then Application.AskToUpdateLinks = True End If End Sub -------------------- This is what I have. Its intent is to open files which are NOT open, and save them. These files are in a hierarchy and this is the only way data can go up to the top levels. If a file is open, it will reopen the already saved file and save it again, which won't actually accomplish anything come to think of it. So that part can probably be edited out. Unless there is a way to force a save on an open worksheet with the Open worksheet's data? Either way, if the sheet is not open, it should open the file (minimized), save it and close it. Did I do this right? Any help would be appreciated. Thanks guys! -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=518154 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
Thanks for your help Bob. There still seems to be a problem with the 'if open' section. It doesn't 'realize' when files are open, executing the open/save/close regardless. Code: -------------------- For i = 1 To 6 On Error Resume Next Set oWB = Workbooks(paths(i)) On Error GoTo 0 If Not oWB Is Nothing Then 'paths(i).Save MsgBox ("Workbook is open: " & paths(i) & ".") Else Workbooks.Open (paths(i)) ActiveWindow.WindowState = xlMinimized Workbooks(Workbooks.Count).Save Workbooks(Workbooks.Count).Close 'ActiveWorkbook.Save 'ActiveWorkbook.Close End If Next i -------------------- And the "isOpen" property I was rather confident wouldn't work. It was mostly wishful coding? Heh. But from the help file: "True if the specified HTML project item is open in the Microsoft Script Editor. Read-only Boolean." -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=518154 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
"ph8" wrote in message ... Thanks for your help Bob. There still seems to be a problem with the 'if open' section. It doesn't 'realize' when files are open, executing the open/save/close regardless. This is because you are testing with the full path of the file, and Excel needs you to test with just the filename. This is not helped by the fact that your files all have the same name, and it is just their folders that differ (but I like a challenge!): Private Sub ForceUpdateAll() Dim filenames(6) As String Dim paths(6) As String Dim filesToReopen(6) As String Dim thedir As String Dim UpdateWarn As String Dim i As Integer Dim oWB As Workbook Dim Win As Window UpdateWarn = "WARNING: Warning text goes here..." thedir = Cells(1, "a").Value 'A1's contents are hidden, A1 contains a formula which ' returns directory of current file ' It might be better to use theDir = ThisWorkbook.Path & "\" ' set filenames here filename(1) = "file.xls" filename(2) = "file.xls" filename(3) = "file.xls" filename(4) = "file.xls" filename(5) = "file.xls" filename(6) = "file.xls" 'set paths here paths(1) = thedir & "folder1\" & filename(1) paths(2) = thedir & "folder2\" & filename(2) paths(3) = thedir & "folder3\" & filename(3) paths(4) = thedir & "folder4\" & filename(4) paths(5) = thedir & "folder5\" & filename(5) paths(6) = thedir & "folder6\" & filename(6) Dim ATUstatus As Boolean 'variable for AskToUpdateLinks Status If MsgBox(UpdateWarn, vbYesNo, "Update Links") = vbYes Then ATUstatus = Application.AskToUpdateLinks Application.AskToUpdateLinks = False 'disables prompt to update links '(automatically chooses to update them) For i = 1 To 6 filesToReopen(i) = "" On Error Resume Next Set oWB = Workbooks(filename(i)) On Error GoTo 0 If Not oWB Is Nothing Then If UCase$(oWb.FullName) = paths(i) Then oWB.Save Else ' Can't Open the file yet, since Excel doesn't support two files open with the same name ' So close the existing one, and remember to open it later filesToReopen(i) = oWB.FullName oWB.Save oWB.Close Set oWB = Nothing EndIf EndIf If oWB Is Nothing Then Set oWB = Workbooks.Open(paths(i)) For Each Win In oWB.Windows Win.WindowState = xlMinimized Next oWB.Save oWB.Close EndIf Next i ' Now Reopen the files that we just closed For i = 1 To 6 If Len(filesToReOpen(i)) Then Workbooks.Open(filesToReOpen(i)) EndIf Next i Application.AskToUpdateLinks = ATUstatus End If End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
The only thing I can think of is that you are not setting the names up
correctly in the paths array. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ph8" wrote in message ... Thanks for your help Bob. There still seems to be a problem with the 'if open' section. It doesn't 'realize' when files are open, executing the open/save/close regardless. Code: -------------------- For i = 1 To 6 On Error Resume Next Set oWB = Workbooks(paths(i)) On Error GoTo 0 If Not oWB Is Nothing Then 'paths(i).Save MsgBox ("Workbook is open: " & paths(i) & ".") Else Workbooks.Open (paths(i)) ActiveWindow.WindowState = xlMinimized Workbooks(Workbooks.Count).Save Workbooks(Workbooks.Count).Close 'ActiveWorkbook.Save 'ActiveWorkbook.Close End If Next i -------------------- And the "isOpen" property I was rather confident wouldn't work. It was mostly wishful coding? Heh. But from the help file: "True if the specified HTML project item is open in the Microsoft Script Editor. Read-only Boolean." -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=518154 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
Thanks for the replies :). S. I. Becker Wrote: This is because you are testing with the full path of the file, and Excel needs you to test with just the filename. This is not helped by the fact that your files all have the same name, and it is just their folders that differ (but I like a challenge!): Where exactly in the code does it have to use a filename instead of a path for the code to work? In truth, the above is just example/mock filenames and paths. The actual file names and paths are unique. So we don't have to inclue the filename variable or the failsafe for same name files being opened. If all we need is the filename, then wouldn't it be simplest to just pull the file name from the full path string? Using "right" and a command that looks for the first \ (backslash) it finds counting from the right? But even if that would work, I am still unclear as to where the filename needs to replace the path name. -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=518154 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
"ph8" wrote in message ... Thanks for the replies :). S. I. Becker Wrote: This is because you are testing with the full path of the file, and Excel needs you to test with just the filename. This is not helped by the fact that your files all have the same name, and it is just their folders that differ (but I like a challenge!): Where exactly in the code does it have to use a filename instead of a path for the code to work? When you test to see if the workbook is already open: On Error Resume Next Set oWB = Workbooks(filenames(i)) ' NB not paths(i), and filenames (with an s) not filename as I posted earlier. On Error GoTo 0 In truth, the above is just example/mock filenames and paths. The actual file names and paths are unique. So we don't have to inclue the filename variable or the failsafe for same name files being opened. Well the code I posted will work anyway. In future, _please_ do not send example code. Post the code you are actually using, it helps us to help you. If all we need is the filename, then wouldn't it be simplest to just pull the file name from the full path string? Using "right" and a command that looks for the first \ (backslash) it finds counting from the right? You could do that, but since in the code you posted you are constructing the full path from the location and individual filename anyway, you might as well remember the filename separately. Consider also that constructing the full path from a location and filename can be done using just the & operator, but deconstructing the filename from the full path is more involved. The whole point of macros is to make life easier! HTH, Stewart |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
I'm sorry about the code issue. I didn't think it would be an problem. But lesson learned :) Thanks for your reply once again. I hate to keep pushing it. I know you probably want me to just us your code and be off with it, but I can't help but want to learn mor thoroughly at what the code does. On Error Resume Next Set oWB = Workbooks(filenames(i)) On Error GoTo 0 How exactly does this check if a workbook is open? From my limite knowledge of VBA, I can see that this would only check if a workbook i open on the local computer? Is that right? If so, then I can't hel but realize that it is exactly what I asked for. :( I failed t specify that these workbooks would be open on networked computers. The project I am working on is a hierarchial set of spreadsheets. Dat from the top tier spreadsheet gets pulled from the spreadsheets belo it, all the way down the chain. The point of this specifi funtion/macro I am trying to construct (with your collective help, o course) is to force data all the way up to the top. If the spreadsheet 'chain' had four levels, or tiers. The first tie would pull data from the second tier. The second tier would pull dat from the third tier. And the third tier would pull data from th fourth. If data is entered at the fourth tier, it will never show u at the first tier unless the second and third tier are opened, an their links updated, then saved. Which is what this macro does, or i suppose to do -- ph ----------------------------------------------------------------------- ph8's Profile: http://www.excelforum.com/member.php...fo&userid=1987 View this thread: http://www.excelforum.com/showthread.php?threadid=51815 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
"ph8" wrote in message ... I'm sorry about the code issue. I didn't think it would be an problem. But lesson learned :) Thanks for your reply once again. My comment was intended to be a lesson, not a lecture. <g I hate to keep pushing it. I know you probably want me to just use your code and be off with it, but I can't help but want to learn more thoroughly at what the code does. Nope, if more people understood what their code does, rather than copy-and-pasting from the NGs without understanding it, the world would be a better place. Taking this line by line: On Error Resume Next If an error occurs, ignore it and just proceed to the next line. Set oWB = Workbooks(filenames(i)) [Try to] set oWB to a reference of the workbook with the name in filenames(i). If this works then all is well, we have a reference to the workbook we want. If not, then an error will occur and oWB will not be set (i.e. will be Nothing). However, because of the line above, the macro will not halt at this point and carry on to the next line. On Error GoTo 0 This has the opposite effect of "On Error Resume Next", i.e. this will turn error checking back on. It also has the effect of resetting the Err Object, so that if an error had occurred up to this point, it is cleared. So at the end of all this, if the desired workbook in open, you have a reference to it in oWB. If not, you have a Nothing reference in oWB. You can do the Is Nothing test to find out what to do next. How exactly does this check if a workbook is open? From my limited knowledge of VBA, I can see that this would only check if a workbook is open on the local computer? Is that right? If so, then I can't help but realize that it is exactly what I asked for. :( I failed to specify that these workbooks would be open on networked computers. Yes, this will only check to see if it is open on the current instance of Excel on the current computer. If the files are open on other computers you will get errors when you try to save them. Opening them will work, but you might get a Read Only / Notify message. One way round this is to make them Shared workbooks, so that they can be open on more than one computer at a time. A caveat of this is that you can't edit code in a shared workbook: you have to unshare it, edit the code, then reshare it, which is a pain. However, you can just put your code in yet another workbook (or addin) and run it from there. The project I am working on is a hierarchial set of spreadsheets. Data from the top tier spreadsheet gets pulled from the spreadsheets below it, all the way down the chain. The point of this specific funtion/macro I am trying to construct (with your collective help, of course) is to force data all the way up to the top. If the spreadsheet 'chain' had four levels, or tiers. The first tier would pull data from the second tier. The second tier would pull data from the third tier. And the third tier would pull data from the fourth. If data is entered at the fourth tier, it will never show up at the first tier unless the second and third tier are opened, and their links updated, then saved. Which is what this macro does, or is suppose to do. If you follow my advice about shared workbooks, you should be OK, but watch out for situations when two or more users edit the same cells at once (including updates from your code). Best practise when using shared workbooks is to save often (Autosave?), even if you haven't done any work, because saving a shared workbook has the effect of loading up other users' changes too. HTH, Stewart |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
Thanks for the information :) This is what I finally ended up going with: Code: -------------------- If MsgBox(UpdateWarn, vbYesNo, "Update Links") = vbYes Then If Application.AskToUpdateLinks = True Then ATUstatus = 1 Else ATUstatus = 0 Application.AskToUpdateLinks = False 'disables prompt to update links '(automatically chooses to update them) For i = 1 To 6 Workbooks.Open (paths(i)) ActiveWindow.WindowState = xlMinimized Workbooks(Workbooks.Count).Save Workbooks(Workbooks.Count).Close Next i If ATUstatus = 1 Then Application.AskToUpdateLinks = True ActiveWindow.WindowState = xlMaximized Else: End If -------------------- Since I can't check if the target spreadsheet is open, I figure just open and save them all. From what I know about files on networks, if someone has a spreadsheet open and this program runs it will still update the links succesfully. But even if it doesn't, being that the workbook was open means an update is imminent (as soon as they save). This function was more or less a catch for those users who were not opening and saving their sheets. My only gripe with the code above is how 'visible' this process is. I warn the users that they will see files opening and closing in the warning, but even still I would like to try to hide the process a little more. Is there a way to open a workbook in the background, or hidden? I tried remaximizing the "ThisWorksheet" (where the above code will be) but that slowed the function down significantly, which I didn't like. Anyone have an ideas? -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=518154 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
My only gripe with the code above is how 'visible' this process is. I
warn the users that they will see files opening and closing in the warning, but even still I would like to try to hide the process a little more. Is there a way to open a workbook in the background, or hidden? I tried remaximizing the "ThisWorksheet" (where the above code will be) but that slowed the function down significantly, which I didn't like. Anyone have an ideas? Look into the Application.ScreenUpdating property .... Stewart |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
If workbook is open, save workbook?
I have. It apparently is only local to the spreadsheet. It didn't keep the function from being as visible. :( -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=518154 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open a template, copy 3 worksheets w ranges and save as workbook | Excel Programming | |||
Using interop.excel to open a workbook, the workbook doesn't ask to save changes. | Excel Programming | |||
Code to open new workbook, prompt for 'save as' name. | Excel Programming | |||
Why when i have more than one workbook open and i want to save ch. | New Users to Excel | |||
What commands do you use to name a workbook, save a workbook,open a workbook | Excel Programming |