topic Re: Execute Excel VBA Macro using SAS in Microsoft Integration with SAS

Execute Excel VBA Macro using SAS

Skb19121985 — Sun, 02 Jul 2017 09:40:02 GMT

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

Re: Execute Excel VBA Macro using SAS

art297 — Sun, 02 Jul 2017 12:57:38 GMT

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

Re: Execute Excel VBA Macro using SAS

ChrisHemedinger — Sun, 02 Jul 2017 13:52:03 GMT

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.

Re: Execute Excel VBA Macro using SAS

Reeza — Sun, 02 Jul 2017 15:03:06 GMT

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.

Re: Execute Excel VBA Macro using SAS

Skb19121985 — Sun, 02 Jul 2017 16:13:18 GMT

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,

Re: Execute Excel VBA Macro using SAS

Skb19121985 — Sun, 02 Jul 2017 16:19:29 GMT

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

Re: Execute Excel VBA Macro using SAS

Skb19121985 — Sun, 02 Jul 2017 16:26:37 GMT

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

Re: Execute Excel VBA Macro using SAS

ChrisHemedinger — Sun, 02 Jul 2017 20:07:57 GMT

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.

Re: Execute Excel VBA Macro using SAS

Reeza — Sun, 02 Jul 2017 20:33:18 GMT

@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/FareezaKhurshed-CoordinateComplexReports-Spring2014.pdf