locked
Linked Cells Don't update Dynamically from Source File on Network Drive/Local Drive RRS feed

  • Question

  • Hi,

    I've Linked Cells (ActiveX TextBox Controls) in a destination file which doesnt update from when source file is updated on a shared network drive. I've tried putting source file on both network as well as local drive with no luck.

    Note that source file is updated via SQL running using VBA every 15 mins. Source is updated fine but Linked Cells in destination file are not refreshed until:

    1. I manually open source file (either on Network drive or Local drive) which triggers linked cell updates.
    2. On Destination file, I click on Data->Edit Links and click on "Open Sourec" as below:

    Any idea what I am missing here? Why Linked Cells doesnt work dynamically and manual intervention is needed?

    Best regards,

    Zulfiqar

    Tuesday, February 20, 2018 9:27 AM

All replies

  • Hello HussainMD,

    >>Note that source file is updated via SQL running using VBA every 15 mins.

    Could you share us what's the code in VBA to update the source file every 15 mins? I tried to use VBA to open the source excel file and update the value in source file and the linked cell also changed.

    I think we need know how the VBA code update the source file so we could try to reproduce your issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 21, 2018 9:09 AM
  • Hi Terry,

    Hope you are well. Ideally I dont want to open my source excel but expect linked cells to update automatically when source file is changed. Just to point you that my source file remains CLOSED all the time and gets updated every 15 mins by VBScript running from Windows task scheduler every 15 mins.

    So my point is:

    1. Should Linked cells be updated in destination file when source is updated while source file is always CLOSE?
    2. Is it necessary to have Source file OPEN to trigger linked cell updates?

    I'll post my code soon.

    Thank you for your help.

    Hussain

     

    Wednesday, February 21, 2018 9:29 AM
  • Dim UserName As String
    Dim Password As String
    Sub GetDataFromADO(sqlString As String)
    
      Dim objMyConn As New ADODB.Connection
      Dim objMyRecordset As New ADODB.Recordset
      Dim wbBook As Workbook
      Dim wsSheet As Worksheet
      Dim rnStart As Range
      
      Set wbBook = ActiveWorkbook
      Set wsSheet = wbBook.Worksheets("Instrument Search Result")
      GetUserName
      GetPassword
      
      'Sheets("Instrument Search Result").Activate
      
      With wsSheet
        If wsSheet.Cells(2, 17) <> "" Then
          Set rnStart = .Range("T2")
        ElseIf wsSheet.Cells(2, 5) <> "" Then
          Set rnStart = .Range("B2")
          'MsgBox sqlString
        Else
          Set rnStart = .Range("B2")
        End If
      End With
      
        'Declare variables'
            Set objMyConn = New ADODB.Connection
            Set objMyRecordset = New ADODB.Recordset
            Dim strSQL As String
    
        'Open Connection'
            objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=10.xxx.x.xxx;User ID=" & UserName & ";Password=" & Password & ";"
            objMyConn.Open
    
        'Set and Excecute SQL Command'
        
            strSQL = sqlString
            
        'Open Recordset'
            Set objMyRecordset.ActiveConnection = objMyConn
            objMyRecordset.Open strSQL
            
        'Copy Data to Excel'
            rnStart.CopyFromRecordset objMyRecordset
        
    End Sub
    
    
    Sub GetUserName()
    
    If Cells(2, 8) <> "" Then
        UserName = Cells(2, 8).Value
    Else
        MsgBox "Please input your Username!"
        Exit Sub
    End If
    
    End Sub
    
    Sub GetPassword()
    
    If Cells(2, 8) <> "" Then
        Password = Cells(3, 8).Value
    Else
        MsgBox "Please input your Password!"
        
        Exit Sub
    End If
       
    End Sub
    
    Sub ClearResultsBeforeRun()
    
        Sheets("Instrument Search Result").Range("B2:B2").Clear
        
    
    End Sub
    Sub NAV_jump()
    
      runQuery (1)
      Call testNAVJump
    
    End Sub
    Sub testNAVJump()
       'Application.OnTime Now + TimeValue("00:15:00"), "NAV_jump"
        Call HeaderOnly
        Call StaleETFs
        Call ETFNAVvsCalculatedNAVJump
        Call ETFContainsItself
        Call DelistedStockinETForIndex
        Call FixedIncomeAccruedInterest
        
    End Sub
     
    
    Sub runQuery(queryType As Integer)
    
    Dim i As Integer
    Dim searchStr, searchStr1, searchStr2 As String
    Dim sqlString As String
    
    searchStr = "where ts.CurrentName not like 'LIQUIDATED%' and ((tep.NAV - ptep.NAV) / tep.NAV  * 100) >=5 order by 9;select count(*) as ""Count"" from #tempNAVJumpStats;"
    
    'Clear the Sheet'
    ClearResultsBeforeRun
    i = 2
    
    sqlString = "SET NOCOUNT ON;declare @PrevNAVDate date;" _
    & "DECLARE @dayNumber INT; SET @dayNumber = DATEPART(DW, GETDATE()); " _
    & "IF(@dayNumber = 2)" _
        & "SET @PrevNAVDate = DATEADD(day, DATEDIFF(day,0,GETDATE()),-4);Else " _
        & "SET @PrevNAVDate = DATEADD(day, DATEDIFF(day,0,GETDATE()),-2);select " _
    & "Distinct(ts.SecurityID),tf.FamilyID, tf.Name as FamilyName, tlc.Code as Ric, ts.CurrentName as ETFName,tdp.Name as Provider, tep.NAV as 'NAV(T)', ptep.NAV as 'NAV(T-1)',(tep.NAV - ptep.NAV) / tep.NAV  * 100 as 'NAVJump(%)', CONVERT(VARCHAR(10),tep.AsAtDate,105) as PositionDate,CONVERT(VARCHAR(24), tf.LastUpdate, 113) As LastUpdate " _
    & "into #tempNAVJumpStats " _
    & "from SolaDBServer..tblETF te inner join SolaDBServer..tblSecurity ts on ts.SecurityID=te.SecurityID and te.IsUnderConstruction=0 " _
    & "inner join SolaDBServer..tblSecurityProperty sp on sp.SecurityID=te.SecurityID and sp.StartDate<=getdate() and sp.EndDate>getdate() and sp.IsActive=1 " _
    & "inner join SolaDBServer..tblListing tl on tl.SecurityID=te.SecurityID and sp.PrimaryListingID=tl.ListingID " _
    & "inner join SolaDBServer..tblFamily tf on tf.FamilyID=te.FamilyID " _
    & "left join SolaDBServer..tblHoliday th on th.CalendarID=tf.CalendarID and th.HolidayDate = cast(getdate() as date)" _
    & "inner join SolaDBServer..tblDataProvider tdp on tdp.DataProviderID=tf.DataProviderID " _
    & "inner join SolaDBServer..tblListingCode tlc on tlc.ListingID=tl.ListingID and sp.StartDate<=getdate() and sp.EndDate>getdate() and tlc.CodeTypeID=10 " _
    & "inner join SolaDBServer..tblETFPosition tep on te.ETFID=tep.ETFID and tep.AsAtDate>=(select max(x.AsAtDate) from SolaDBServer..tblETFPosition x where x.ETFID=tep.ETFID and (x.IsOpen=1 or x.IsOpen=0)) " _
    & "inner join SolaDBServer..tblETFPosition ptep on te.ETFID=ptep.ETFID and ptep.AsAtDate=@PrevNAVDate and ptep.IsOpen=0 " _
    & "" & searchStr & ""
    
    GetDataFromADO (sqlString)
    
    End Sub
    

    Above code is run using windows task scheduler as below: 

    cmd.exe /C "C:\Users\zulfiqar.dholkawala\Desktop\SOLAExcelDashboard\RunSOLAExcelDashboardMacro.vbs"

    Code in the above VBS is as below:

    Option Explicit
    Dim objExcel, objWB, strPathExcel, objSheet, strMasterPathExcel, objMasterWB
    
    Set objExcel = CreateObject("Excel.Application")
    'strPathExcel = "\\markit.partners\dfs\UK\Shared\Equities\QC\DONT_TOUCH-SOLA_Dashboard_20180208.xls"
    strPathExcel = "C:\Users\zulfiqar.dholkawala\Desktop\SOLAExcelDashboard\DONT_TOUCH-SOLA_Dashboard_20180208.xls"
    Set objWB = objExcel.Workbooks.Open(strPathExcel)
    'objExcel.Application.Run "'\\markit.partners\dfs\UK\Shared\Equities\QC\DONT_TOUCH-SOLA_Dashboard_20180208.xls'!Nav_Jump"
    objExcel.Application.Run "'C:\Users\zulfiqar.dholkawala\Desktop\SOLAExcelDashboard\DONT_TOUCH-SOLA_Dashboard_20180208.xls'!Nav_Jump"
    Set objSheet = objWB.Worksheets("Instrument Search Result")
    objExcel.DisplayAlerts = False
    
    'Save Results
    objWB.Save
    'objWB.RefreshAll
    objWB.Close
    
    
    'strMasterPathExcel = "\\markit.partners\dfs\UK\Shared\Equities\QC\DONT_TOUCH-SOLA_Dashboard_20180208.xls"
    strMasterPathExcel = "C:\Users\zulfiqar.dholkawala\Desktop\SOLAExcelDashboard\DONT_TOUCH-SOLA_Dashboard_20180208.xls"
    Set objMasterWB = objExcel.Workbooks.Open(strMasterPathExcel)
    objExcel.Visible = True
    objExcel.Application.CalculateFull
    objMasterWB.RefreshAll
    objMasterWB.Saved = True
    objMasterWB.Close
    
    
    objExcel.Quit
    
    'Cleanup
    Set objSheet = Nothing
    Set objWB = Nothing
    Set objExcel = Nothing
    Set objMasterWB = Nothing

    Let me know if you need more details..

    Thank you.

    Zulfiqar (Hussain)

    Wednesday, February 21, 2018 1:37 PM
  • Hello ZulfiqarMD,

    >>Set objExcel = CreateObject("Excel.Application")

    You create a new Excel application instance for opening the source file, so the source file and destination file are in different application instances and this cause the destination file failed to updated the value.

    I thinks the destination file is always opened, right? I would suggest you try to get the destination file's application instead of creating a new instance.

    Such as 

    Set objExcel =GetObject(, "Excel.Application")

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 23, 2018 2:20 AM
  • Hi Terry, 

    Thanks for the details:

    Just to answer your question: yes Destination file is like a dashboard and will always be open (read only) - users will not be able to update anything on this file. Only updates on this file are pushed thorugh Source File which runs VBA.

    After doing as you suggested But now I am getting msg box. This means Destination file is closed as soon as Source file is updated and pops up this message. Any Idea why this is happening? and how do I refresh destination file its always OPEN?

    Updated Code below:

    Option Explicit
    Dim objExcel, objWB, strPathExcel, objSheet, strMasterPathExcel, objMasterWB
    
    Set objExcel = CreateObject("Excel.Application")
    strPathExcel = "C:\Users\zulfiqar.dholkawala\Desktop\SOLAExcelDashboard\DONT_TOUCH-SOLA_Dashboard_20180208.xls"
    Set objWB = objExcel.Workbooks.Open(strPathExcel)
    'objExcel.Application.Run "'\\markit.partners\dfs\UK\Shared\Equities\QC\DONT_TOUCH-SOLA_Dashboard_20180208.xls'!Nav_Jump"
    objExcel.Application.Run "'C:\Users\zulfiqar.dholkawala\Desktop\SOLAExcelDashboard\DONT_TOUCH-SOLA_Dashboard_20180208.xls'!Nav_Jump"
    Set objSheet = objWB.Worksheets("Instrument Search Result")
    objExcel.DisplayAlerts = False
    
    'Save Results
    objWB.Save
    objWB.Close
    
    Set objExcel = GetObject(,"Excel.Application")
    strMasterPathExcel = "C:\Users\zulfiqar.dholkawala\Desktop\SOLAExcelDashboard\DONT_TOUCH-SOLA_Dashboard_20180208.xls"
    Set objMasterWB = objExcel.Workbooks.Open(strMasterPathExcel)
    objExcel.Visible = False
    objMasterWB.RefreshAll
    'objMasterWB.Saved = True
    objMasterWB.Close
    
    objExcel.Quit
    
    'Cleanup
    Set objSheet = Nothing
    Set objWB = Nothing
    Set objExcel = Nothing
    Set objMasterWB = Nothing

    Thank you.

    ZulfiqarMD


    ZulfiqarMD

    Friday, February 23, 2018 10:16 AM
  • Hello ZulfiqarMD,

    >>objExcel.Quit

    Since the application is get from current destination workbook's applcation, you should not quit it while opening source file. Delete this line in the VBS.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Saturday, February 24, 2018 1:22 AM
  • Hi Terry,

    commented out >>objExcel.Quit but same result. I can see master sheet is updating as usual fine. and once it is refreshed dashboard sheet is closed without being updated. :(

    While, it is closed and I try to open it manually. I get following message:


    Any idea why this is popping up?

    Thank you.

    Zulfiqar


    ZulfiqarMD


    • Edited by ZulfiqarMD Thursday, March 1, 2018 11:37 AM
    Thursday, March 1, 2018 11:30 AM
  • Hello ZulfqarMD,

    >>objExcel.Visible = False

    I think it is hiden other than closed. Please try to comment out this link for testing again.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 2, 2018 7:46 AM
  • Hello ZulfqarMD,

    >>objExcel.Visible = False

    I think it is hiden other than closed. Please try to comment out this link for testing again.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thanks for your help. above solved auto refresh issue on the Dashboard sheet. But This Master Sheet and Dashboard sheet updates work only locally and not while running this on network drive (as a shared worksheet). Essentially Dashboard sheet will be shared by multiply users to view the regular updates to Master Sheet.

    Not sure if this is possible?

    Thank you.


    ZulfiqarMD

    Friday, March 2, 2018 12:06 PM
  • Hello ZulfiqarMD,

    Practice is the measure of truth. I would suggest you do a test for yourself.

    I will try to build a test environment as soon as possible and then I will also do a test for you.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 5, 2018 8:42 AM
  • Hello ZulfiqarMD,

    Practice is the measure of truth. I would suggest you do a test for yourself.

    I will try to build a test environment as soon as possible and then I will also do a test for you.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    thanks terry,

    yes I tried sheet putting it on network drive, and running VBA from my local machine. Sheet updates fine for me. But same sheet open on other machine doesn't update until master sheet is manually open on that machine. 

    Thank you.

    Zulfiqar


    ZulfiqarMD

    Friday, March 9, 2018 5:01 PM