Help With WorkBook Open/Close Problem

[charllie]

Hi Folks,

I am using the following code in a workbook which when activated does the following:

• Opens the workbook "Shift Manager.xls" located in various folders in my G:Drive.
  Locates the worksheet "Shift Manager Screen" in the workbook
  Then performs the code which is in the with statement of the code below
  Then saves and closes the workbook "Shift Manager.xls"

My problem is that this code only works if the workbook "Shift Manager.xls" is closed. However the workbook will be open at all times.
Is there a way of performing this action with the workbook open.

I did think about placing a duplicate copy of the worksheet "Shift Manager Screen" in an archive workbook which will always be closed. Then get the workbook "Shift Manager.xls" to update from this archive folder by refreshing itself every so often. But i am not sure if that will work and how to do it. Also the information that it will need to update will mean that it will have to add up 1 row at a time for all new rows enter.

Can anyone help me to overcome this problem with a solution.

Application.ScreenUpdating = False
        Dim SMS As Worksheet
            
            Workbooks.Open Filename:="G:\Cwmbran-new\Warehouse\lean manu\Mike C\HandPack Time Sheet\On LIne\" & _
                             "Shift Manager.xls" 'This opens the other workbook

        Set SMS = Worksheets("Shift Manager Screen") 'This refers to worksheet in other workbook
        
        With SMS
            .Range("G8").Value = Val(.Range("G8").Value) + 1 & " Jobs Completed" 'Running Total Of Total Jobs Completed
            .Range("D8").Value = Val(.Range("D8").Value) + Val(Me.TotalDiscsTextBox.Value) & " Total Discs Packed" 'Running Total Of Total Discs Packed
        End With
        ActiveWorkbook.Save                'Saves changes in the other workbook
        ActiveWorkbook.Close               'Closes the other workbook
    Application.ScreenUpdating = True

Thanks

 

[pcc]

don' t understand question. If it's closed then why would you want to save and close it, as it's closed so it must be saved anyway!

 

[charllie]

Hi pcc,
Sorry, i will try to make myself a little clearer.

Part1:
At the moment the code as it is works if the workbook "Shift Manager.xls" is already open.

Part2:
But if i run the code as it when "Shift Manager.xls" is closed it will open "Shift Manager.xls" perform the actions as required and then leave it open.

What i am asking is if there is extra code i can add that will leave the workbook "Shift Manager.xls" open if already open or close "Shift Manager.xls" if it was closed when the code is run.

Hope that explains things a bit better.

 

[pcc]

try this - not tested

Sub test_code()
Application.ScreenUpdating = False
        Dim SMS As Worksheet
        Dim openflag As Boolean
        openflag = False
        
        For Each wb In Workbooks
        wbname = wb.Name
        If wbname = "Shift Manager.xls" Then
        openflag = True  ' it was already open
            Windows(wbname).Activate
                  
            GoTo 100
        End If
        Next wb
            Workbooks.Open Filename:="G:\Cwmbran-new\Warehouse\lean manu\Mike C\HandPack Time Sheet\On LIne\" & _
                             "Shift Manager.xls"
100:

        Set SMS = Worksheets("Shift Manager Screen") 'This refers to worksheet in other workbook
        
        With SMS
            .Range("G8").Value = Val(.Range("G8").Value) + 1 & " Jobs Completed" 'Running Total Of Total Jobs Completed
            .Range("D8").Value = Val(.Range("D8").Value) + Val(Me.TotalDiscsTextBox.Value) & " Total Discs Packed" 'Running Total Of Total Discs Packed
        End With
        'ActiveWorkbook.Save                'Saves changes in the other workbook
        'ActiveWorkbook.Close               'Closes the other workbook
        
        '*******************************************************************
        'In this code place the name of worksheet you want to leave showiing
        'on the screen place the name of workbook it is in
        Windows("Team Leader - TEST.xls").Activate
        Sheets("Team Leader Screen").Activate
        '*******************************************************************
        If openflag = False Then ' it was not open so close it
        Windows("Shift Manager.xls").Close False  ' ******** or True if you want to save any changes - you need to decide
        
    Application.ScreenUpdating = True

End Sub

 

[charllie]

Hi PCC,

That works as required. Thank you very much for your help. Couldn't have done it without you.

I appreciate your time. patience and help.

Thanks