vb.net updates Excel file-CodePudding

I have a Excel file which updates itself when it is opened. By "updates" I mean that the Excel file is downloading the data which is stored on a list on a server.

In order to always work on the most current database version, my vb.net application once opens the Excel file, saves it and closes it again. I thought that this will update my Excel-file so that all data included on the server are stored in the Excel file. Sadly this does not work. Do I miss something?

My code is:

    Dim xls As New Excel.Application
    Dim xlwb As Excel.Workbook
    xlwb = xls.Workbooks.Open(ExcelFilePath)
    xlwb.Save()
    xlwb.Close()

CodePudding user response：

If all you want is to retrieve data from a server when the workbook opens, why not use the open event as described here: Microsoft documentation

Then you can download your data, and there is no need to close the excel file. You can just use your data right away.

Sub ThisWorkbook_Open() Handles Me.Open
    'Download your data here.
End Sub

CodePudding user response：

I found the solution to my problem: First I had to uncheck the box in the Excel-file that the data is updated in the background. Second I changed my code to:

    Dim xls As New Excel.Application
    Dim xlwb As Excel.Workbook
    xlwb = xls.Workbooks.Open(ExcelFilePath)
    xlwb.RefreshAll()
    xlwb.Save()
    xlwb.Close()

And then the Excel sheet updates when my vb.net application starts.