Execute Excel VBA Macro using SAS

[Skb19121985]

Hi

Could anyone please help me with SAS code that would help execute Excel VBA Macro in SAS platform such that the whole process is automated ?

I know how to open and run an Excel file in SAS , but I don't know how to implement a VBA code in my SAS.

The SAS data that is exported in Excel is :

Name	Age	Gender	School	DOB	DOJ	Qualification
q	1	m	qq	1	4	m
w	2	m	aa	2	5	m
e	3	m	aa	3	6	m
r	4	m	aa	1	4	m
q	5	m	aa	2	7	m
w	6	m	bb	3	56	m
e	7	f	bb	123	4	m
r	8	f	bb	1	5	b
q	9	f	bb	2	6	m
w	0	f	dd	3	4	b
e	1	f	dd	1	6	b
q	1	f	dd	2	4	m
r	2	f	dd	3	4	b
q	2	m	tt	1	5	m
e	2	f	tt	2	4	b
r	3	m	tt	3	6	m
w	3	f	tt	1	4	b
e	4	m	qq	2	4	m
q	5	f	qq	3	6	b
e	5	m	qq	1	4	m
w	6	m	qq	1	4	b
r	7	m	ww	2	65	m
e	78	f	ww	1	4	b
w	8	f	ww	2	4	m
e	4	f	ww	2	4	b
r	5	f	ww	3	6	m
q	6	f	ww	1	4	b
w	7	f	uu	1	6	m
e	8	f	uu	1	6	b

I would like to implement the following Excel VBA code in SAS :

Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 1
Set ws = Sheets("Sheet1")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:C1"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub

[art297 · Accepted solution]

You can find a couple of examples of using SAS to run VB script in the paper at: http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

Art, CEO, AnalystFinder.com

[Skb19121985]

Hi Sir

Thanks for this !

This would be helpful.

I will go through them and would try to figure out if I could do something more with it.

Regards

[ChrisHemedinger]

If you have a SAS process that generates this sheet, maybe you can add a simpler SAS step to create the additional sheets you're looking for.  I didn't carefully read the VBA you've got, but it seems that you're creating some crosstab sheets to summarize the first sheet.  You could probably acheive with PROC TABULATE, PROC SQL, or PROC REPORT -- and then use ODS Excel to export a workbook with the original data and then the summarized data on subsequent sheets.  Fewer moving parts if you don't have to automate Excel.

If you post an example of the subsquent sheets that you want, we could provide a more specific recommendation.

[Skb19121985]

Hi Chris

Thanks for the suggestion.

Actually, I have just cited an example of Excel VBA Macro. In general, it can be any Excel VBA Macro.

I just wanted to know the process through which I could execute any Excel VBA Macro using SAS.

Regards,

[Reeza]

Another approach to this situation. 

Create a template with the macro and or cross tabs you want using pivot tables. 

Add a macro or select the option that forces the tables to refresh when file is opened

For each update, copy template, refresh data source, tables are updated. 

[Skb19121985]

Hi Reeza

Thanks for the suggestions. It is in my mind.

There are different ways to automate the process.

I wanted to automate SAS and Excel together, to an extent that the only platform I work with would be SAS but I at the same time I take control of Excel using Excel VBA Macro.

The Excel VBA code , I cited in my query was just an example.

In short, I wanted to execute any Excel VBA Macro using SAS and get desired result in Excel. Ab initio, it is to be understood that no matter whatever be the Excel VBA Macro, it is tested to run error free in Excel.

Regards

[ChrisHemedinger]

You can launch Excel from SAS using the X command (or SYSTASK) and a VB Script program.  However, this works only when SAS and Excel are on the same machine, and of course, that machine has to be running Windows.

If you are in the (very common) situation where SAS is running on a remote machine (on a server, and perhaps using Linux), then you can't run those X command steps directly.  Instead, you would need to copy the Excel file from the SAS server to a Windows machine, and then run the VB Script from there.  If you're using SAS Enterprise Guide for all of this, you can actually automate these steps:

• Run a SAS program to generate the Excel file
• Use the Data->Copy Files task to copy the Excel file to the local Windows machine
• Use the System Command task (a custom task you can download) to launch the VB Script
• If desired, use the Copy Files task again to upload the final Excel file back to the SAS server

For a very specific example of using SAS and Excel together for Pivot Tables, see this blog post from @Chevell_sas.

[Reeza]

---

@Skb19121985 wrote:

Hi Reeza

Thanks for the suggestions. It is in my mind.

There are different ways to automate the process.

I wanted to automate SAS and Excel together, to an extent that the only platform I work with would be SAS but I at the same time I take control of Excel using Excel VBA Macro.

The Excel VBA code , I cited in my query was just an example.

In short, I wanted to execute any Excel VBA Macro using SAS and get desired result in Excel. Ab initio, it is to be understood that no matter whatever be the Excel VBA Macro, it is tested to run error free in Excel.

Regards

---

Can you assume the VBA macro is something already in your Excel files or do you need to get the VBA code to Excel as well?

That's the difficult part, otherwise, as long as you have XCMD enabled, calling the VBA is a trivial task and I have code for that can be shared, if required. 

Getting VBA to Excel is going to be the issue IMO. I've seen a paper from Koen Vyverman who discusses how to accomplish this (search on Lexjansen.com) but I've found the Excel Template with all my VBA's already is a better mechanism for managing this type of work. Note that you can then save the Excel file as XLSX to remove the macros for your users. Other than the stock template, all of this can be driven from SAS. 

There's some info on how I used DDE/Excel + some javascript (for customized page footers/numbers) in this paper. 

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