Refresh the Excel (xlsx) files Using SAS

[SASuserlot]

I have the Excel (xlsx)  file table saved in a folder. The xlsx file  get the data from web. I use this data as my source.  I don't want to open the excel file every time and refresh it before running the SAS code. I would like to know is there any way I can refresh the excel file using SAS  before converting to SAS dataset (import).

         I tried the following code. but it running me some problems in the Excel file getting a pop-up shown below. Any solutions for this. I am not familiar with VB script ins SAS. Not sure what causing this pop-up , I tried enabling all the macro in the Excel file(file>options>trust center>trust center settings> macro setting> enable vbs macros). Really appreciate your ideas.

options noxwait noxsync;

%let f_name= file ;
%let path= %str (c:\Desktop\ncat 1);

data _null_;
   rc=system('start excel');
 	X=SLEEP(5);
run;

filename cmds dde 'excel|system';



data _null_;

	file cmds;
	put "[open(""\\&path\&f_name..xlsx"")]"; 
	PUT '[RUN("Refresh")]';
	put "[save()]";
	put "[quit()]";

run;

[ballardw]

Microsoft has severely deprecated the use of DDE so any approach that relies on DDE is of potentially limited use. Additionally, other programs out there can and do disrupt or use the channels that DDE was designed to use making DDE non-responsive or non-functional. So you may want to rethink any DDE code at all.

How exactly does the XLSX file "get data from the web"? You may, especially in light of the DDE issues, want to consider SAS Proc HTTP as one of the ways to read data from websites. That would completely remove the "refresh" involved.

[SASuserlot]

I totally understood. I believe you educated on 'DDE' before🙏. Unfortunately, I still have to depend on this system , I asked to change this approach but  I am not in a position to ask the organization to change their regular things. For some reason organization still following these things.

Coming to the second part of the question. xlsx sheet provided by the other team  where they manually get the data and import to the excel sheet like this :

open excel> get data> from web> enter the url> then it created the pivotal table.

I am sure  you are aware of above method. But that's the process they follow it.

Thanks again  for your inputs.

[Sajid01]

I do agree with you @SASuserlot that old habits die hard in progressive entities too.
I suggest, if it is feasible at your level to do this, try importing the data using Proc http etc and use the data in your code. Often one has to work his way around and if your approach is better perhaps it may be accepted.

[SASuserlot]

I tried it  unfortunately there is permission issues doing proc http ( not sure exactly why) from organization, that's the reason  I have to stick with the DDE approach. Thanks again for your input.

[Reeza]

VBS option mentioned above doesn't have this issue.

Tons of examples online you can test, here's a good one that does all Excel files in folder

Set fso = CreateObject("Scripting.FileSystemObject")
Set xl  = CreateObject("Excel.Application")
xl.Visible = True

For Each f In fso.GetFolder("C:\some\folder").Files
  If LCase(fso.GetExtensionName(f.Name)) = "xlsx" Then
    Set wb = xl.Workbooks.Open(f.Path)
    wb.RefreshAll
    wb.Save
    wb.Close
  End If
Next

xl.Quit

Save this to a text file in the folder and modify the parameters as needed (ie file paths)

Then write a SAS program that calls the VBS via X Command is the easiest:

x "cscript ""path to.vbs""";

Add a pause to your script and then update it as needed.

For a single file the VB would probably look like this, SAS side stays the same. I suspect any company allowing DDE enables X commands as well.

Set fso = CreateObject("Scripting.FileSystemObject")
Set xl  = CreateObject("Excel.Application")
xl.Visible = false

 Set wb = xl.Workbooks.Open("path to your excel file.xlsx")
    wb.RefreshAll
    wb.Save
    wb.Close


xl.Quit

[SASuserlot]

Thank you for your suggestions. I tried one of the 2 suggestions given converting the file into 'xlsm' version but still had the same error msg.

I tried your single file VB script, I am encountering the following messages and file was not refreshing.  I am not at all familiar with VB script.  What do you think of these msgs?

Also we still have to use the SAS code I just used the following code

options noxwait noxsync  ;

x "cscript ""D:\Desktop\New folder1\xlrefresh.vbs""";

xlrefresh is the code where vbscript saved.

[Reeza · Accepted solution]

Set fso = CreateObject("Scripting.FileSystemObject")
Set xl  = CreateObject("Excel.Application")
xl.Visible = false

 Set wb = xl.Workbooks.Open("path to your excel file.xlsx")
    wb.RefreshAll
  'pause script for 5000 ms adjust as needed
  WScript.Sleep 5000
    wb.Save
    wb.Close


xl.Quit

Sorry, this is the route I would recommend, 99% sure it will work but I don't have the time to test and develop a full solution. Good Luck!

[SASuserlot]

I understand. Thanks for your time. I really appreciate it.

Update: It worked now. Thanks

[ballardw]

---

@SASuserlot wrote:

Coming to the second part of the question. xlsx sheet provided by the other team  where they manually get the data and import to the excel sheet like this :

open excel> get data> from web> enter the url> then it created the pivotal table.

I am sure  you are aware of above method. But that's the process they follow it.

Thanks again  for your inputs.

---

This sounds like an embedded link to a URL unless you mean that someone actually edits the URL periodically. I have used embedded links to such with just local files on our network. We discovered that while most of the office installs (not all of our computers had the exact same as we were a fairly small company and you ran the version installed when we could afford new computers) that the results would vary depending on exactly which computer did the opening of the Excel files with the link. So I am not a great fan of Excel with such links in general.

It would really help if "the other team" shared exactly what settings they use to access that URL. Likely there is at minimum a permissions bit involving user id and likely password. If this were my project I would approach that other team with, if the process can be implemented, that it removes one or more manual steps from their work load.

If they happen to be petty "empire builders" and just want to obfuscate, then that may not work and management should be involved/invoked. If you can point to a potential benefit such as reduced man-hours or quicker response to your piece of the project if the Proc HTTP could be implemented then management should be interested. If you not the only one with such a requirement then there is potentially multiple improvements.

If file layouts do not change and are either static file names or have a simple to build name such as a date value suffixed to some base then it is very likely that a Proc HTTP approach could be implemented relatively quickly, at least down loading the file. Your comment about "pivot table" may mean that the SAS code to use the data would change as I seldom see a pivot table that makes more sense to read than basic row level data. Then create the equivalent "pivot" in SAS code if actually needed. Which with a bit of experience you may find is much easier to modify consistently than actual "pivots" in Excel when the inevitable change comes up. 

Of course if the data source changes frequently and there are many manual steps in filling in the Excel file this is likely not practical.

[SASuserlot]

I brought this to my department attention. However in order to go through the implementation it taking time, because of security issues and the sensitivity of the information.  So for time being we have to find the alternative way to continue our work. Thank you for suggestion. I do understand, my organization have to update. Again I am not in a position that I can make implement these practices quickly, I wish I am 😉

[Reeza]

It sounds like your job isn't to use the data from the Excel file. In that case, this was never a SAS question really, it should have been posted on an Excel forum for better responses. 

Because if you're using the data from the Excel file in some SAS process how would they know if you used Proc HTTP/URL rather than the Excel as the data source? I think you're trying to use SAS to automate a process that SAS shouldn't be involved in really. 

[SASuserlot]

1. I do understand your concern. Since I saw a post about refreshing the excel pivot table which was answered for xlsm file, so I  thought of its place where I can ask the question.

2. I do agree it's more of manipulating the excel file than using the SAS code data.

3. Reason for this question is, My website is technically a SharePoint and other websites. I tried proc http procedure, where I run into trouble of permission, and password issues. I believe I posted that query previously on how to import the data from SharePoint. But I did not find any good solution.

4. I do use the SAS code after the above requisition.  I import the excel file and convert it to the dataset, from there I will use it for my analysis. Since the original data information is on SharePoint and websites, every time I have to open the source excel sheet (provided by another team  ) and refresh it before I run the SAS code. I asked this question so that I can avoid refreshing the excel file every time manually.

I am not completely pro at SAS , I am still learning. So while posting this question, I thought these kinds of procedures still used in organizations.

I really appreciate your suggestions and your time. Thanks

[Reeza]

Well when you get there, I assume you're trying something like this 

https://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Winnipeg-User-Group/HarryDroo...

[SASuserlot]

This is something that I am looking for eventually that my organization will do. Thanks I will play with this code around. Thanks again for your time.