SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Execute Excel VBA Macro using SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Execute Excel VBA Macro using SAS

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 :

 

NameAgeGenderSchoolDOBDOJQualification
q1mqq14m
w2maa25m
e3maa36m
r4maa14m
q5maa27m
w6mbb356m
e7fbb1234m
r8fbb15b
q9fbb26m
w0fdd34b
e1fdd16b
q1fdd24m
r2fdd34b
q2mtt15m
e2ftt24b
r3mtt36m
w3ftt14b
e4mqq24m
q5fqq36b
e5mqq14m
w6mqq14b
r7mww265m
e78fww14b
w8fww24m
e4fww24b
r5fww36m
q6fww14b
w7fuu16m
e8fuu16b

 

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

 


Accepted Solutions
Solution
‎07-07-2017 07:49 AM
PROC Star
Posts: 7,492

Re: Execute Excel VBA Macro using SAS

Posted in reply to Skb19121985

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

 

View solution in original post


All Replies
Solution
‎07-07-2017 07:49 AM
PROC Star
Posts: 7,492

Re: Execute Excel VBA Macro using SAS

Posted in reply to Skb19121985

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

 

Occasional Contributor
Posts: 15

Re: Execute Excel VBA Macro using SAS

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

Community Manager
Posts: 2,956

Re: Execute Excel VBA Macro using SAS

Posted in reply to Skb19121985

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.

Occasional Contributor
Posts: 15

Re: Execute Excel VBA Macro using SAS

Posted in reply to ChrisHemedinger

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,

 

Super User
Posts: 19,873

Re: Execute Excel VBA Macro using SAS

Posted in reply to Skb19121985

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. 

Occasional Contributor
Posts: 15

Re: Execute Excel VBA Macro using SAS

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

 

Community Manager
Posts: 2,956

Re: Execute Excel VBA Macro using SAS

Posted in reply to Skb19121985

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.

Super User
Posts: 19,873

Re: Execute Excel VBA Macro using SAS

Posted in reply to Skb19121985

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...

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 636 views
  • 3 likes
  • 4 in conversation