If workbook is open, save workbook?

  • Thread starter Thread starter ph8
  • Start date Start date
P

ph8

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 :)
 
ph8 said:
Greetings folks!

How do you check to see if a workbook is open?
Click to expand...

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?
Click to expand...

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?
Click to expand...

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
 
try this

Private Sub Workbook_Open()
ActiveWindow.WindowState = xlMinimized
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub

Best N10
 
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)
 
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!
 
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)
 
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 said:
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.
Click to expand...

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
 
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)
 
Thanks for the replies :).

S. I. Becker said:
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!):
Click to expand...

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 said:
Thanks for the replies :).



Where exactly in the code does it have to use a filename instead of a
path for the code to work?
Click to expand...

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.
Click to expand...

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?
Click to expand...

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
 
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
Click to expand...

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
 
ph8 said:
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.
Click to expand...

My comment was intended to be a lesson said:
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.
Click to expand...

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:
If an error occurs, ignore it and just proceed to the next line.
[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.
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.
Click to expand...

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.
Click to expand...

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
 
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?
 
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?
Click to expand...

Look into the Application.ScreenUpdating property ....

Stewart
 
I have. It apparently is only local to the spreadsheet. It didn't keep
the function from being as visible. :(
 
Back
Top