# Visual Basic > Visual Basic .NET >  Data - Refresh all from simulation from excel

## sapator

Hi.
I'll admit I haven't started a research on this yet but if there is a quick solution someone has done I will appreciate it.
The concept is reading an excel office 365 file and simulation a data-refresh all, button click. I don't necessarily mean pushing the button nor opening the excel but do what an excel refresh all does.
My concern is, if that require a macro to run and if so, can I run it through VB.NET or does not require a macro so I can write vb.net code or cannot be done.
Thanks.

Edit, forgot to mention that there is a high chance this will be done at asp.net so I'm not sure if I can use office.interops but it might be done on winforms. I'm not sure about that, still.

----------


## sapator

So I did a little study.
From what I could find out it is not possible. Maybe it is possible if you have office installed on the IIS server to run an office.interop , I did create a project and managed to add the references and dll files to the web.config without complains from the web app but I get this exception : Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).  , that seems to point that you need the office installed, regardless if you copy any dll's to the bin folder.

I can work this out from a windows form, although not sure if office is also needed, as I have office installed...Maybe I should copy paste the app somewhere without office and run it.

Also, yes, running interrops on IIS is not recommended but I was testing around since I cannot find any other solution, as previously I was only chaning the worksheet values or importing values etc, so I haven't done a refresh. Btw the is no need for a macro, you just do Excel.Workbook.RefreshAll()

If anyone has any idea, would be glad to hear it.

----------


## TysonLPrice

You can set it to do it automatically:

https://support.microsoft.com/en-us/...5-1e3dcf94809f

----------


## sapator

Granted but that is not the question. Although I will rep this.

----------


## ChrisE

> Hi.
> I'll admit I haven't started a research on this yet but if there is a quick solution someone has done I will appreciate it.
> The concept is reading an excel office 365 file and simulation a data-refresh all, button click. I don't necessarily mean pushing the button nor opening the excel but do what an excel refresh all does.
> My concern is, if that require a macro to run and if so, can I run it through VB.NET or does not require a macro so I can write vb.net code or cannot be done.
> Thanks.
> 
> Edit, forgot to mention that there is a high chance this will be done at asp.net so I'm not sure if I can use office.interops but it might be done on winforms. I'm not sure about that, still.


did you try reading the Excel File with OLEDB, just like using a SQL-Query to get all new Data

----------


## sapator

I'm not sure what you mean.
I don't want to read the file. I want to refresh it and save it.

Also using the refresh from excel example, not sure where exactly is the Data type refresh settings. I right click on a pane with data but I see no option for data type.
The only option I see is on the far right writing "Queries and connections" .

----------


## TysonLPrice

> I'm not sure what you mean.
> I don't want to read the file. I want to refresh it and save it.
> 
> Also using the refresh from excel example, not sure where exactly is the Data type refresh settings. I right click on a pane with data but I see no option for data type.
> The only option I see is on the far right writing "Queries and connections" .


Along those lines you could record a Excel macro while the sheet refreshed and capture the VBA code.  That might get you started.

----------


## sapator

Why? That is way more complicated and what exactly would I do with the VBA code? 
My issue has more to do with the asp.net manipulation. If I use a form, I can use macro, do refresh, save, since everyone has office installed there is no problem but from web I can't do anything of these.I'm not even sure I can save the file without having to install office at the server and again not sure it will work.

----------


## Arnoutdv

The Excel file with simulation data is on a server, without Office installed, but needs to work with "updated" data from the Excel file.
Updating the data in the Excel file can only be done when Excel is running, hence the suggestion to use Office automation.

You really have to explain your use-case if you need more functionality then just reading the data from the Excel file

----------


## sapator

So if I run a jet engine and parse the excel it will update data? OK then how would I go by and save the file on the server? Because even if the data is updated the excel must be saved in order to maintain it, correct?
The explanation is simple. Perform an refresh all by asp.net on the server. Why, because they want to to automatically update and send a file, without ever open it by had.
Thanks.

----------


## sapator

OK, possible solution, if doable because as I understand you simply cannot save it properly through IIS. Open the excel from asp.net (hopefully it will get the data) and send a copy of the excel somewhere or save it somewhere and the send it. Do you think it will get the refreshed data if the excel is set to refresh at open that way?
Thanks.

----------


## ChrisE

> I'm not sure what you mean.
> I don't want to read the file. I want to refresh it and save it.


Refresh what ? do you want to add Data or Update Data

this is how you would add data


```
Private PathExcelFile As String = "E:\TestFolder\ExcelTest\vbexcel.xlsx"

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim MyOLEDB As System.Data.OleDb.OleDbConnection
        Dim myCommand As New System.Data.OleDb.OleDbCommand
        Dim sql As String

        MyOLEDB = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathExcelFile & ";Extended Properties='Excel 12.0;HDR=NO;';")
        MyOLEDB.Open()
        myCommand.Connection = MyOLEDB

        Dim V1 As String = "Sapator"
        Dim V2 As Integer = 123
        Dim V3 As Integer = 456

        sql = "INSERT INTO [Tabelle3$] (F1, F2, F3) values (@a1,@a2,@a3)"

        With myCommand
            .CommandType = CommandType.Text
            .Parameters.Add("@a1", OleDbType.VarChar, 30).Value = V1
            .Parameters.Add("@a2", OleDbType.Integer).Value = V2
            .Parameters.Add("@a3", OleDbType.Integer).Value = V3
            .CommandText = sql
            .ExecuteNonQuery()
            .Connection.Close()
            .Dispose()
        End With
        MyOLEDB.Close()
    End Sub
```

----------


## vbdotnut

The only constructive advice I can add to this is that you are doing something really wonky and there's got to be a better way to handle your unexplained root task. You can create an excel file by using ACE/JET in the same way you create a table in SQL IE: "CREATE TABLE BLAH (col1 datatype(size))" so maybe you should be running your queries to get the data and creating a new xl file and sending that, but then again, I feel a bit more stoopider after attempting to understand your requirement

----------


## sapator

I'm not sure what I'm not explaining correctly. I don't want to add nothing to the excel. If I did this manually the way would be to open the excel, press the data-refresh button, save and close.
The data-refresh handles the new data and is automatically added to the excel. I don't want to parse any data, I just want to save the excel with the new data it contains, no parse, don't care about the data just save it.
I can do it probably on a form, although I must admit I did not try to see if the data is actually saved to the excel, I maybe,maybe not can do it on asp.net if I install office on the server so I have the intreops. So I'm out of ideas on asp.net, unless just opening the data with oledb or any other mean (we use telerik spreadsheets) and saving to a new file and sending that file in some location or by email have the updated data from a data-refresh, since the excel has been set as refresh on open.
Thanks.

----------


## vbdotnut

Why can you not replicate the queries that are being refreshed within the excel book by means of ADO thus creating a new excel book and sending that. Why do you need to use the existing workbook

----------


## sapator

The excel has over 10.000 line 4 sheets covering them and a couple of hundred of conditions expressions and formats, it's not a couple of rows. It would take weeks to finish it, so if I can just refresh I'll take it.

----------


## jdc2000

So, let me see if we are understanding how your system works now, assuming a manual process.

You open the Excel workbook that is located on a server file share, on your client computer that has Excel installed, click Refresh, and Save.

If that is correct, the easiest way to automate your task would be to set up a scheduled task that does the same thing.  Create a macro that does the Refresh on opening the workbook, and you are done.

----------


## OptionBase1

https://stackoverflow.com/questions/...he-excel-sheet

https://answers.microsoft.com/en-us/...d-4d2d71ba1395

https://stackoverflow.com/questions/...ng-actual-file

https://stackoverflow.com/questions/...cel-to-refresh

To answer your original post, there is certainly no quick answer, and pretty much any answer that is functional involves using Excel.

What Excel is doing when "Refresh Data" is clicked is probably 98% reliant on the internal workings of Excel.exe itself, and then the final 2% is just blasting out updated data to your spreadsheet in the appropriate cells.

Sounds like you may need to rethink the implementation strategy.

Good luck.

----------


## sapator

So actually after a little munching we went on to run something (sql exe job, windows service, windows scheduler) on an exe that will be on the server that has office installed.
So there is some examples of using Excel.Workbook.RefreshAll() that will supposedly refresh the data , I will see what I can find out and report back if I have issues.
For now, thanks for you time.

----------


## vbdotnut

MS Power Automate comes to mind

----------


## ChrisE

> So actually after a little munching we went on to run something (sql exe job, windows service, windows scheduler) on an exe that will be on the server that has office installed.
> So there is some examples of using Excel.Workbook.RefreshAll() that will supposedly refresh the data , I will see what I can find out and report back if I have issues.
> For now, thanks for you time.


In your case Powershell could be an option
https://github.com/TylerNielsen/powe...ExcelFiles.ps1

----------


## sapator

Ohw, that look ugly.
If I install office to a server and run a console app that refresh the excel, won't this work?
Currently I don't have office in any server so I get an exception even if I copy the dll's of interop assemblies but if I install office 365 it will work? Also the powershell would also require office installed,right?

----------


## OptionBase1

> Ohw, that look ugly.
> If I install office to a server and run a console app that refresh the excel, won't this work?
> Currently I don't have office in any server so I get an exception even if I copy the dll's of interop assemblies but if I install office 365 it will work? Also the powershell would also require office installed,right?


Why not just test your planned implementation on a device that already has Office installed on it?

----------


## sapator

Ye it works on my old office 2010 PC and my new 365 PC. I was not sure that it would work on the old PC as the interops I used are 16.0 (aka 365), that is....Strange but maybe it has backwards compatibility.

----------

