Desktop productivity for business analysts and programmers

Excel automation

Reply
Occasional Contributor
Posts: 12

Excel automation

Hi Everyone,

 

I need some help to automate my excel work:

I will explain what I do currently..

 

SAS CODE(run as a trigger run)

SAS code which generates a table which I export in Excel(lets call it Data Sheet).

This has numbers for Prior month and current month to date(by date) for revenue.

 

EXCEL WORK:

Excel workbook Report has sheets Sheet1 and Dsiplay1

I copy and paste data from Data Sheet and paste it in Sheet1.

Excel sheet Display1 has formulas which take data from Sheet1. I convert Display1 excel sheet as pdf and send it via email.

Once the data comes in for a day’s revenue, I have to freeze it which means I manually copy and paste the data for that day as a value in my excel(Display1 Sheet)…Numbers for Prior days will change when pulled on future date but we want to see the number as it first appeared on that day…

Eg, when I do a pull on 8th july, I get the below numbers:

01Jul201890
02Jul2018123
03Jul2018256
04Jul2018123
05Jul2018256
06Jul2018548
07Jul2018567
08Jul2018400

Now when I do a pull on 9th July I might get :

01Jul2018100
02Jul2018150
03Jul2018200
04Jul2018250
05Jul2018300
06Jul2018350
07Jul2018400
08Jul2018450


 But in my report, I do want to see 7th July as it was pulled on 8th (567) not 400 and so forth for the coming days…

 

My current code runs through a trigger run everyday at 5am , so when I come to office I have the data ready and do the Excel work..What I want to do is to automate the excel work as well so I do not have to work on it everyday..the mail automatically gets out!

 

If this isn't the right spot for such a question, please direct me to the appropriate forum.

 

Thanks a lot!! Smiley Happy

Super User
Posts: 23,980

Re: Excel automation

Posted in reply to new_sas_user_4

This is the right place but your question is a bit too vague to offer a full solution. I can say that all of this can be done with SAS since I've done it multiple times. Out of curiousity, is there any reason you couldn't generate the full report in SAS outputted to a PDF report and then email that PDF? That's likely the easiest solution depending on the complexity of your data process/report. 

 

As an interim:

 

  1. Create an Excel template with the formulas you want. In template, create a named range large enough to hold all of your data. It can be bigger but if it's smaller you'll get errors. Make the template also have an excel macro that will export to a  specific location with a fixed or dynamic file name as needed. Sometimes I create a new named range that will contain the file name and location. 
  2. Export data to named range. Use the libname method here. 
  3. Look into either DDE or VBS, I recommend VBS to run the macro in the workbook to create the PDF
  4. Use SAS to call the VBS and to email as necessary.

I still think redesigning your process in SAS fully is likely to be easier. 

 

Don't know if this has enough details but this is a walkthrough of how I did it:

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

 

 


@new_sas_user_4 wrote:

Hi Everyone,

 

I need some help to automate my excel work:

I will explain what I do currently..

 

SAS CODE(run as a trigger run)

SAS code which generates a table which I export in Excel(lets call it Data Sheet).

This has numbers for Prior month and current month to date(by date) for revenue.

 

EXCEL WORK:

Excel workbook Report has sheets Sheet1 and Dsiplay1

I copy and paste data from Data Sheet and paste it in Sheet1.

Excel sheet Display1 has formulas which take data from Sheet1. I convert Display1 excel sheet as pdf and send it via email.

Once the data comes in for a day’s revenue, I have to freeze it which means I manually copy and paste the data for that day as a value in my excel(Display1 Sheet)…Numbers for Prior days will change when pulled on future date but we want to see the number as it first appeared on that day…

Eg, when I do a pull on 8th july, I get the below numbers:

01Jul2018 90
02Jul2018 123
03Jul2018 256
04Jul2018 123
05Jul2018 256
06Jul2018 548
07Jul2018 567
08Jul2018 400

Now when I do a pull on 9th July I might get :

01Jul2018 100
02Jul2018 150
03Jul2018 200
04Jul2018 250
05Jul2018 300
06Jul2018 350
07Jul2018 400
08Jul2018 450


 But in my report, I do want to see 7th July as it was pulled on 8th (567) not 400 and so forth for the coming days…

 

My current code runs through a trigger run everyday at 5am , so when I come to office I have the data ready and do the Excel work..What I want to do is to automate the excel work as well so I do not have to work on it everyday..the mail automatically gets out!

 

If this isn't the right spot for such a question, please direct me to the appropriate forum.

 

Thanks a lot!! Smiley Happy


 

Occasional Contributor
Posts: 12

Re: Excel automation

Thanks Reeza!!

One of the reasons I couldn't  do this is :

 

There is data (such as revenue target for each day fixed one time at start of the month)

which needs to be a column in the final display ...also a column which has variance(difference) between actuals (what I pull each day) and the target....and a few other columns some of which are fixed data and some dynamic or manually calculated..

 

and the other reason: I do not know the process to generate the full report in SAS outputted to a PDF report and then email that PDF..

 

Could you please elaborate on he interim solution you suggested..I didnt understand steps 2,3,4...using libname and thereafter..I do not use excel macros as of now..

 

also, if there is any link or site to learn how to do it, please direct me there...

I am new to using SAS or excel macros and automation...

Super User
Posts: 23,980

Re: Excel automation

Posted in reply to new_sas_user_4

See page 15 on the PDF linked above for an example of exporting data using the libname method. 

 

The process for any of this is relatively simple though - break it down into small enough steps and then tackle each step independently. 

You'll move faster with this type of approach. 

 

The calculations don't sound cumbersome so it may be worth pushing it all to SAS. It seems this is beyond your skill set at the moment so you have 3 options:

 

1. Learn how to do it - takes time

2. Pay someone else - takes money

3. Find an alternative/mixed approach - suggestions above are that. 

 

You can search any topic on lexjansen.com. For example:

How to email a report:

http://support.sas.com/resources/papers/proceedings17/1300-2017.pdf

 

The VBS and Excel VB macro code are also in the original PDF linked in my previous response. Maybe I'll write it up someday fully. The slides were for a presentation but without me talking they're less useful Smiley Happy

Ask a Question
Discussion stats
  • 3 replies
  • 86 views
  • 0 likes
  • 2 in conversation