topic Re: Not getting updated data while importing formula based EXCEL SHEET in SAS Programming

Not getting updated data while importing formula based EXCEL SHEET

AshwaniGupta — Wed, 13 Feb 2019 09:58:39 GMT

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

Re: Not getting updated data while importing formula based EXCEL SHEET

RW9 — Wed, 13 Feb 2019 10:04:44 GMT

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

Re: Not getting updated data while importing formula based EXCEL SHEET

Kurt_Bremser — Wed, 13 Feb 2019 10:06:35 GMT

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.