Require help to find a procedure to run an excel macro in the server (Automated)

[kiran28]

I am new to SAS. I am currently working on a project. I have designed an Excel Macro which will automate a process and provide an output. The user has to upload an excel file into the server, and my program has to fetch the excel file, run the Macro in it, and provide another excel file as the output. All this has to take place in the background automatically at runtime. I asked for suggestions from my friends and they asked me try the same with SAS. I would like to be provided with procedures and Ideas on how to use SAS on my project or any other feasible method.

Thank you.

[RW9]

Depends on the inputs output methods.  SAS can read Excel files in, do processing, and then write Excel files out no problem.  But how will you get the file in, and how will you provide the file back to the client, these are the main questions.  And the answer to which will drive what SAS product you choose, or process you go with.  For instance there is a SAS server setup with stored processes that can automate process.  Or you could just have desktop SAS installed and run a program.  Or you could have a batch program which calls SAS with the relevant SAS code.

Its a case of how long is my bit of string...

[RandyMullis]

Hi, @kiran28. I would first figure out the SAS code to accomplish what you want. (Read in the file, manipulate the data, write out the result file). Then I would focus on how to call the SAS program in an automated way. SAS is very good at executing in batch (using the Task Scheduler if you're on Windows).

I'm sure the users here can help with both aspects of what you're trying to do, but you can help us help you by breaking it down into two parts.

Randy

[kiran28]

I could pretty well understand what you are saying. Its like. The input of the Excel file is through an UPLOAD button on the website. Thats been linked to a root folder. and we would like to give the output through a "Download the file" button. and the process in the middle is what I am trying to figure out. I have used HTML and PHP to create the front end pages. am wondering what to do next for manipulating the data.

Thank you again.

[Reeza]

Why are you using SAS at all here?

According to what you've stated:

I have designed an Excel Macro which will automate a process and provide an output.

So why not stick with a VBS or OS solution rather than a SAS solution?

Is your server a Windows or Unix server and does it have Excel available?

---

@kiran28 wrote:

I am new to SAS. I am currently working on a project. I have designed an Excel Macro which will automate a process and provide an output. The user has to upload an excel file into the server, and my program has to fetch the excel file, run the Macro in it, and provide another excel file as the output. All this has to take place in the background automatically at runtime. I asked for suggestions from my friends and they asked me try the same with SAS. I would like to be provided with procedures and Ideas on how to use SAS on my project or any other feasible method.

Thank you.

---

[kiran28]

Yes. My server is Windows based and Excel is available as well. I thought SAS would be required to run the macro in the server as a background process by automating it.

[Reeza]

Is this an Excel Macro (built into a workbook) or a SAS macro program?

---

@kiran28 wrote:

Yes. My server is Windows based and Excel is available as well. I thought SAS would be required to run the macro in the server as a background process by automating it.

---

Why do you think SAS needs to be involved here?

[Reeza]

And you've posted this in Analytics U forum, but I assume you're not using SAS UE. If you are this isn't possible due to the lock down nature. Otherwise I can move this post to the main SAS Programming forum.

[kiran28]

I dont know much about SAS as i said i am new to this. I was just looking for help.

[Reeza]

I get that, I'm trying to understand your problem so that we can offer a solution. Sometimes SAS isn't the solution, especially when someone mentions Excel macros. Rather you'd be better served:

1. Using OS/VBS commands

2. Migrating your full process to SAS. 

---

@kiran28 wrote:
I dont know much about SAS as i said i am new to this. I was just looking for help.

---

This is a really old presentation but it somewhat aligns how SAS can be used in this sort of thing, but DDE isn't sustainable in the long run for building processes. And if you have a server it's not a solution I would consider production ready and stable. 

http://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/FareezaKhu...

DDE is not enabled in Excel 2016, you'll need to enable it first if you decide to go down that route.