Second workbook not closing as expected
Wow, that is quite a bit shorter! Now I'll go puzzle through it to see what
I can learn :)
Thanks!
Keith
"Die_Another_Day" wrote in message
ups.com...
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 wrote:
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
|