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

To open an excel file  in Visual studio use the following

Dim xlApp As New Excel.Application
        Dim xlBook As Excel.Workbook



        xlBook = xlApp.Workbooks.Open(path to the file)
        xlApp.Visible = True

Job done nice and simple  excel opens the file and closes the process when you close excel.

If you do GC.collect at the end it will do a garbage collection.

Microsoft Office Compatibility

If you have an old version of Microsoft Office and can not open some of the documents you are receiving then download and install the
Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint File Formats from
http://www.microsoft.com/en-us/download/details.aspx?id=3

This will allow you to view these documents.