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

[J.W. Aldridge]

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

 

[Rick Rothstein \(MVP - VB\)]

Describe "not working".

Rick

 

[Guest]

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

 

[J.W. Aldridge]

Code stops at "If".

Compile error. Syntax error

 

[J.W. Aldridge]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Guest]

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