Second workbook not closing as expected

[KR]

We have multiple workbooks on a network (one per user). When each is opened,
a second (source) XLS file on the same network drive is opened, and a few
values are compared to see if the information in the source file is newer
than in the individual file. If so, the newer information is copied into the
individual file.

The following code works for the comparison, but the source file is not
closing as expected. I'm trying to figure out if there is something wrong
with my syntax related to closing the sourceWB, or possibly something else
(for example, if the worksheet in the individual workbook is protected can
the paste fail without an error? If so, I'll have to check and potentially
unprotect those sheets in code)
Thanks for any insights!
Keith
'---------------------------------------------------------------------------
------
Sub GetUpdatedData()
'open the shared workbook in read-only, check the values in A1 on each
sheet, and copy over the sheets that have been updated in the sourceWB

Dim SourceWB As Workbook
Dim SelfID As Workbook

Set SelfID = Workbooks(Excel.ActiveWorkbook.Name)

MyVPS = Sheet4.Range("A1").Value
MyVReasons = Sheet5.Range("A1").Value
MyVAwards = Sheet6.Range("A1").Value

Set SourceWB = Workbooks.Open("\\NetworkPath\DATA.xls", False, True)

CheckVPS = SourceWB.Worksheets("PS").Range("A1").Value
CheckVReasons = SourceWB.Worksheets("Reasons").Range("A1").Value
CheckVAwards = SourceWB.Worksheets("Awards").Range("A1").Value

RunSubTree = False
If MyVPS <> CheckVPS Then
SourceWB.Activate
SourceWB.Worksheets("PS").Activate
SourceWB.Worksheets("PS").Cells.Select
Application.CutCopyMode = False
Selection.Copy
SelfID.Activate
SelfID.Sheets("PS").Activate
SelfID.Sheets("PS").Cells.Select
ActiveSheet.Paste
ActiveSheet.Range("A1").Copy
End If

If MyVReasons <> CheckVReasons Then
SourceWB.Activate
SourceWB.Worksheets("Reasons").Activate
SourceWB.Worksheets("Reasons").Cells.Select
Application.CutCopyMode = False
Selection.Copy
SelfID.Activate
SelfID.Sheets("Reasons").Activate
SelfID.Sheets("Reasons").Cells.Select
ActiveSheet.Paste
ActiveSheet.Range("A1").Copy
End If

If MyVAwards <> CheckVAwards Then
SourceWB.Activate
SourceWB.Worksheets("Awards").Activate
SourceWB.Worksheets("Awards").Cells.Select
Application.CutCopyMode = False
Selection.Copy
SelfID.Activate
SelfID.Sheets("Awards").Activate
SelfID.Sheets("Awards").Cells.Select
ActiveSheet.Paste
ActiveSheet.Range("A1").Copy
End If

SourceWB.Close (False)

End Sub

 

[Die_Another_Day]

I'm not sure why it is failing but one thing I noticed was the syntax
that you used for closing:
SourceWB.Close (False)
when you are not returning a value you do not need to use ().
For example
Var1 = MsgBox("Blah",vbYesNo) 'Requires paranthesis as you are asking
for the value
MsgBox "Blah", vbYesNo 'No paranthesis required as nothing is being
returned
also I took the liberty of cleaning up your code:

Sub GetUpdatedData()
'open the shared workbook in read-only, check the values in A1 on each
sheet, _
and copy over the sheets that have been updated in the sourceWB


Dim SourceWB As Workbook
Dim SelfID As Workbook


Set SelfID = Workbooks(Excel.ActiveWorkbook.Name)


MyVPS = Sheet4.Range("A1").Value
MyVReasons = Sheet5.Range("A1").Value
MyVAwards = Sheet6.Range("A1").Value


Set SourceWB = Workbooks.Open("\\NetworkPath\DATA.xls", False,
True)


CheckVPS = SourceWB.Worksheets("PS").Range("A1").Value
CheckVReasons = SourceWB.Worksheets("Reasons").Range("A1").Value
CheckVAwards = SourceWB.Worksheets("Awards").Range("A1").Value


RunSubTree = False
If MyVPS <> CheckVPS Then
Application.CutCopyMode = False
SourceWB.Worksheets("PS").Cells.Copy _
SelfID.Sheets("PS").Cells(1, 1)
End If


If MyVReasons <> CheckVReasons Then
Application.CutCopyMode = False
SourceWB.Worksheets("Reasons").Cells.Copy _
SelfID.Sheets("Reasons").Cells(1, 1)
End If


If MyVAwards <> CheckVAwards Then
Application.CutCopyMode = False
SourceWB.Worksheets("Awards").Cells.Copy _
SelfID.Sheets("Awards").Cells(1, 1)
End If


SourceWB.Close False


End Sub

Charles Chickering

 

[KR]

Wow, that is quite a bit shorter! Now I'll go puzzle through it to see what
I can learn
Thanks!
Keith