Refresh Multiple Excel File that are stored in One Drive Automatically

This is what i use to refresh the data in excel spread sheet located on a Microsoft one drive that have a database connection. We are not going over the Internet just the local cache one drive then syncs the files as normal. The code is VB.Net but should be able to be translated to C and the princable can be used to refresh any One drive file.

First we loop the files on the local drive these will be in your profile on the c drive (c:\users\your user).
We then go to the work book and open it  then refresh the data, save the book and close it. Note while this is running you will not be able to do anything else as it keeps grabbing the focus.


There you have it, works on xls and  xlsx files.


Public Sub Main()
        Delay(100)

        Dim dir As New DirectoryInfo(" "C:\Users\path to  local one drive files ")
        For Each f In dir.GetFiles()
            Console.WriteLine(">> FILE-NAME: [" & f.Name & "]")
            ' Console.WriteLine(">> UPDATE-DATE: " & f.lastWriteTime.ToString("YYYY-MM-DD"))
            '   Console.WriteLine(">> CREATE-DATE: " & f.creationTime.ToString("YYYY-MM-DD"))
            Refresh_Workbook(f.FullName.ToString)
        Next

        Delay(30000)


    End Sub
    Public Sub Delay(ByVal dblSecs)
        Threading.Thread.Sleep(dblSecs)

    End Sub






Private Sub Refresh_Workbook(fname As String)

        Dim XLApp As Object
        Dim wr As Object
        XLApp = CreateObject("Excel.Application")
        XLApp.visible = True   ' not required, you do not need to see this happening
        ' Dim mypath As String = "C:\Users\path to  local one drive files "
        ' Dim fname As String = Dir(mypath & "*.xlsx")
        ' Do While Len(fname) > 0
        Try
            wr = XLApp.workbooks.Open(fname)
            Console.WriteLine(Date.Now)
            wr.refreshall()
            Console.WriteLine(Date.Now)
            Console.WriteLine("10000")
            Delay(10000)
            Console.WriteLine(Date.Now)
            wr.Save()



            wr.Close()
            'fname = Dir()
        Catch ex As Exception

        End Try
        'Loop
        Try
            XLApp.Quit()
        Catch ex As Exception

        End Try
        wr = Nothing
        XLApp = Nothing

    End Sub