Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Second workbook not closing as expected
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
KR KR is offline
external usenet poster
  
Posts: 11
Default Second workbook not closing as expected
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 644
Default Second workbook not closing as expected
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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
KR KR is offline
external usenet poster
  
Posts: 11
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Closing a possible Workbook kyle Excel Programming 2 July 11th 06 08:14 PM
Closing a Workbook Brian Matlack[_42_] Excel Programming 1 January 12th 06 12:48 AM
Closing Hidden Workbook when Active Workbook is Closed SusanK521 Excel Programming 5 September 24th 05 12:27 AM
Help with closing down a workbook?? Don Excel Worksheet Functions 1 May 9th 05 04:05 AM
closing excel after closing a workbook CWalsh[_2_] Excel Programming 3 January 21st 04 03:33 PM


All times are GMT +1. The time now is 03:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement