Fix code - check to see if a workbook is open, if so, save changes then close.
 

Added the "If workbook...." command to code. Not working.

Any suggestions on how to fix this?

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 10/9/2007 by JW'

'
Workbooks.Open Filename:="X:\FHI Share\pcp\jeremy\NEW PCP
DATA.xls", writerespassword:="pcp123"
ActiveWorkbook.RefreshAll
ActiveWorkbook.Close SaveChanges:=True

If Workbook.Open("X:\FHI Share\pcp\jeremy\PCP DATA UPDATE.xls")
Then
ActiveWorkbook.Close SaveChanges:=True
Workbooks("X:\FHI Share\pcp\jeremy\PCP DATA UPDATE.xls").Close


End If

End Sub

Fix code - check to see if a workbook is open, if so, save changes then close.
 

Describe "not working".

Rick


"J.W. Aldridge" wrote in message
ups.com...


Added the "If workbook...." command to code. Not working.

Any suggestions on how to fix this?

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 10/9/2007 by JW'

'
Workbooks.Open Filename:="X:\FHI Share\pcp\jeremy\NEW PCP
DATA.xls", writerespassword:="pcp123"
ActiveWorkbook.RefreshAll
ActiveWorkbook.Close SaveChanges:=True

If Workbook.Open("X:\FHI Share\pcp\jeremy\PCP DATA UPDATE.xls")
Then
ActiveWorkbook.Close SaveChanges:=True
Workbooks("X:\FHI Share\pcp\jeremy\PCP DATA UPDATE.xls").Close


End If

End Sub

Fix code - check to see if a workbook is open, if so, save changes
 

Let's say we want to check if a file named:
Aldridge.xls is open:

Sub jw()
Dim wb As Workbook, s As String
s = "Aldridge"
is_it_open = False
For Each wb In Workbooks
If wb.Name = s Then is_it_open = True
Next
MsgBox (is_it_open)
End Sub

--
Gary''s Student - gsnu200749


"J.W. Aldridge" wrote:



Added the "If workbook...." command to code. Not working.

Any suggestions on how to fix this?

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 10/9/2007 by JW'

'
Workbooks.Open Filename:="X:\FHI Share\pcp\jeremy\NEW PCP
DATA.xls", writerespassword:="pcp123"
ActiveWorkbook.RefreshAll
ActiveWorkbook.Close SaveChanges:=True

If Workbook.Open("X:\FHI Share\pcp\jeremy\PCP DATA UPDATE.xls")
Then
ActiveWorkbook.Close SaveChanges:=True
Workbooks("X:\FHI Share\pcp\jeremy\PCP DATA UPDATE.xls").Close


End If

End Sub

Fix code - check to see if a workbook is open, if so, save changes then close.
 

Code stops at "If".

Compile error. Syntax error

Fix code - check to see if a workbook is open, if so, save changes
 

Thanx GS, but need a little more action than just checking....

Need wb to save and then close if it is found to be open.

Thanx

Fix code - check to see if a workbook is open, if so, save changes then close.
 

Code stops at "If".

Compile error. Syntax error

Try replacing the If-Then block that you added with this...

Dim WB As Workbook
For Each WB In Workbooks
If WB.Name = "PCP DATA UPDATE.xls" Then
If Not ActiveWorkbook Is WB Then
ActiveWorkbook.Close SaveChanges:=True
End If
WB.Close
Exit For
End If
Next

and add this declaration statement to the code window...

Dim WB As Workbook

Rick

Fix code - check to see if a workbook is open, if so, save cha
 

Sub jw()
Dim wb As Workbook, s As String
s = "Aldridge.xls"
is_it_open = False
For Each wb In Workbooks
If wb.Name = s Then is_it_open = True
Next
MsgBox (is_it_open)
If is_it_open Then
Windows("Aldridge.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End Sub

--
Gary''s Student - gsnu200749


"J.W. Aldridge" wrote:


Thanx GS, but need a little more action than just checking....

Need wb to save and then close if it is found to be open.

Thanx