Not getting updated data while importing formula based EXCEL SHEET

[AshwaniGupta]

Hello All,

Need your help.

I am facing one issue, I have one excel file with 2 sheets(tabs) in that , I am exporting data in first sheet and second sheet have formulas which is linked to first sheet. Now when i tried to import data from second sheet at that time i am not getting updated data, but when i attached that EXCEL file in mail it is giving correct updated data.

Could you someone please help me to get this resolve.

I tried one shortcut:-  after exporting data in firstsheet, i manually open excel go to second sheet and then data-refresh all

afetr that when i tried i am getting updated data, but this is not good for my code, i can not do refresh every time .

Thanks in advance.

Ashwani Gupta

[RW9]

When SAS exports data it does not call Excel functionality like refresh rest of workspace.  Add a simple OnLoad VBA macro to your Excel file so that when it loads it refreshes the workspace:

https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.refreshall

[Kurt_Bremser]

Updating cells that contain a formula is always done dynamically while a workbook is loaded in Excel. The workbook file itself is a dead, inert object, and only the things you explicitly do to it (like loading data to a spreadsheet) will happen.

If you want logic to happen while only SAS is active, you need to do all of it in SAS.

In my opinion, this kind of split processing is just a disaster waiting to happen. Move all the logic into SAS. Formulas or VBA code in Excel make sense only when no better tool is available. But you do have SAS, so make use of it.