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

"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

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

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

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

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

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

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

"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