1 / 12

INTRODUCTION

A Primer for Integrating Microsoft Excel, Access, and SAS with Visual Basic For Applications ASA Meetings 2013 Montreal, Canada, Martin Selzer Ph.D. INTRODUCTION. Author: Martin Selzer ( mselzer@att.net ) Download Paper at website: www.urdobro.com

kizzy
Download Presentation

INTRODUCTION

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. A Primer for Integrating Microsoft Excel, Access, and SAS with Visual Basic For Applications ASA Meetings 2013 Montreal, Canada, Martin Selzer Ph.D. INTRODUCTION • Author: Martin Selzer (mselzer@att.net) Download Paper at website: www.urdobro.com To copy paper – save paper to a local folder • The following slides address reading and writing Excel Spreadsheets, Access database tables, and SAS data step files from VBA and SAS. • Frequently used SQL statements for use in VBA also are provided. • Slides address running programs from other applications • Run SAS from VBA (Slide 11) • Run VBA macros from SAS (Slide 12) • Programs given can provide a good reference for programmers and analysts working with files and programs in Excel, Access, and SAS mselzer@att.net

  2. A Primer for Integrating Microsoft Excel, Access, and SAS with Visual Basic For Applications ASA Meetings 2013 Montreal, Canada, Martin Selzer Ph.D. Set References in VBA Editor Click Tools, then References In the References box, check the references shown here and click OK. mselzer@att.net

  3. A Primer for Integrating Microsoft Excel, Access, and SAS with Visual Basic For Applications ASA Meetings 2013 Montreal, Canada, Martin Selzer Ph.D. 1. Using EXCEL or ACCESS as a Data Repository - Extract Data with SQL into Recordset* Public Sub runQuery(ssql, rs, excelVersion, pathFile, queryType) Dim dbwork As String, providerAndDataSource As String, conn As Connection On Error GoTolabelerror Set conn = CreateObject("ADODB.Connection") If queryType = "access" Then If excelVersion <> "2010" Then conn.Provider = "Microsoft.Jet.OLEDB.4.0" conn.Open (dbwork) Else queryType = "access" providerAndDataSource = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathFile conn.OpenproviderAndDataSource & ";Persist Security Info=False;JetOLEDB:DatabasePassword=mHello" End If End If If queryType = "excel" Then providerAndDataSource = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathFile & ";" & _ "Extended Properties=""Excel 12.0;HDR=Yes"";" conn.OpenproviderAndDataSource End If Set rs = CreateObject("ADODB.recordset") rs.CursorLocation = adUseClient rs.CursorType = adOpenStatic rs.LockType = adLockBatchOptimistic rs.Openssql, conn Exit Sub labelerror: MsgBox ("Found sql error.") End Sub * Note to use the recordset the database connection must remain open. To close the database, add the lines: conn.close Set conn = Nothing This opens DB connection if using excel version < 2010 Set Access DB path here This opens DB connection if using excel version = 2010 Optional if not password protected This opens connection to Excel file using excel version >= 2010 This is where recordset is created. A recordset is a data structure containing output from the executed sql statement. You can search the web to understand more on working with recordsets. mselzer@att.net

  4. A Primer for Integrating Microsoft Excel, Access, and SAS with Visual Basic For Applications ASA Meetings 2013 Montreal, Canada, Martin Selzer Ph.D. 2. Extracting Data with SQL from ACCESS - Some Useful SQL Queries* SOME SQL QUERIES AVAILABLE IN VBA SELECT ssql = "SELECT * FROM <Some_TableName> " & _ "WHERE <Some_Var_Name> = <Some_Value>" UPDATE ssql = "UPDATE <Some_TableName> " & _ "SET <Some_Var_Name> = <Some_Value> " & _ "WHERE <Some_Var_Name> = <Some_Value>" DROP TABLE ssql = "DROP Table <Some_TableName>" COPY TABLE ssql = "SELECT <Some_Table_Name>.* INTO <Another_Table_Name> FROM <Some_Table_Name>" ADD OR DROP VARIABLE TO TABLE ssql = "ALTER TABLE <Some_TableName> " & _ "ADD <Some_Variable_Name> <Some MS Access variable Type like number or text (<num chrs>)>" Examples: ssql = "ALTER Table testDB ADD testvar Text (20)" ssql = "ALTER Table <Some_TableName> DROP <Some_Variable_Name>" DELETE RECORD FROM TABLE ssql = "DELETE FROM <Some_TableName> WHERE <Some_Var_Name> = <Some_Value>" LEFT OR RIGHT JOIN OF TABLES (see page 6 for INNER JOIN)** ssql = "SELECT * FROM <Some_TableName> LEFT JOIN <Another_TableName> " & _ "ON <Some_TableName>.<Some_Var_Name> = <Another_TableName>.<Some_Var_Name> " ssql = "SELECT * FROM <Some_TableName> RIGHT JOIN <Another_TableName> " & _ "ON <Some_TableName>.<Some_Var_Name> = <Another_TableName>.<Some_Var_Name> " UNION OF TABLES (RESULT SIMILAR TO SAS MERGE STATEMENT) ssql = "SELECT * FROM <Some_TableName> LEFT JOIN <Another_TableName> " & _ " ON <Some_TableName>.<Some_Var_Name> = <Another_TableName>.<Some_Var_Name> " & _ "UNION " & _ "SELECT * FROM <Some_TableName> RIGHT JOIN <Another_TableName> " & _ "ON <Some_TableName>.<Some_Var_Name> = <Another_TableName>.<Some_Var_Name> " * When modifying tables by Copying table or Add or Drop Variable, do not have database open in Windows while running VBA code. ** Left (Right) joins give records from the table Left (Right) of the JOIN phrase and matching records from the joined table. In contrast, inner joins (see page 6) give only matching records from the on clause which are in both data sets referenced in the ssql statement. mselzer@att.net

  5. A Primer for Integrating Microsoft Excel, Access, and SAS with Visual Basic For Applications ASA Meetings 2013 Montreal, Canada, Martin Selzer Ph.D. 2. Extracting Data with SQL from ACCESS – Continued CREATE TABLE ssql = "CREATE TABLE <Some_TableName> <Some_VarName1> <Some_VarType1> ", & _ "Some_VarName2> <Some_VarType2>" etc. INSERT RECORDS INTO TABLE ssql = "INSERT INTO <Some_TableName> (<Some_VarName1, Some_VarName2, ...>) (<Some_Value1, Some_Value2, ...>)" COUNT NUMBER OF RECORDS IN TABLE ssql = "SELECT Count(*) FROM <Some_TableName>" GET TOP NUMBER OF RECORDS FROM TABLE ssql = "SELECT Top 2 * FROM <Some_TableName>" PUT CONTENTS OF EXCEL SHEET INTO DATABASE TABLE ssql = "SELECT * INTO [" & tabletowrite & "] " & _ "FROM [Excel 8.0;DATABASE=" & PathToRead & FileToRead & "].[" & SheetToRead & "$];" ssql = "SELECT * INTO " & tabletowrite & _ " FROM [Excel 12.0; DATABASE=" & PathToRead & FileToRead & "].[" & SheetToRead & "$];" Note: Some_ExcelWorkbookPath could be c:\<Some_DosFolder>\<Some_ExcelWorkbookName.xls> PUT CONTENTS OF A TABLE FROM ONE DATABASE INTO A TABLE IN ANOTHER DATABASE ssql = "SELECT * INTO <Some_DataBaseName.<Some_TableName> FROM <Some_DatabasePath>.<Some_TableName> " Note: Some.DataBasePath could be c:\<Some_DosFolder>\<Some_DatabaseName.mdb> CREATE TABLE AND INSERT RECORDS FROM ANOTHER TABLE ssql = "SELECT * INTO <Some_TableName> FROM <Some_TableName> " & _ "WHERE <Some_Var_Name> = <Some_Value>" ORDER RESULTS (Descending and Ascending) OF QUERY BY SOME VARIABLE ssql = "SELECT * FROM <Some_TableName> ORDER BY <Some_VarName> DESC" ssql = "SELECT * FROM <Some_TableName> ORDER BY <Some_VarName> ASC" GET RECORDS WITH DISTINCT VARIABLE VALUES FROM TABLE ssql = "SELECT DISTINCT (" & <varlist_separated by commas> & ") FROM <Some_TableName>" GET RECORDS WITH DISTINCT VARIABLE VALUES FROM TABLE AND CREATE NEW TABLE ssql = "SELECT DISTINCT (<Some_VarName1, Some_VarName2, ...>) INTO <Some_TableName > FROM " & _ "<Another_TableName>" From xls file. From xlsx file. mselzer@att.net

  6. A Primer for Integrating Microsoft Excel, Access, and SAS with Visual Basic For Applications ASA Meetings 2013 Montreal, Canada, Martin Selzer Ph.D. 2. Extracting Data with SQL from ACCESS – Continued* SOME SQL QUERIES AVAILABLE IN VBA SELECT VARIABLES AND PERFORM ARITHMETIC FUNCTION ON VARIABLE ssql = "SELECT SUM(Some_VarName) AS <result_VarName>, " & _ "VAR(<Some_VarName>) AS <AnotherResult_VarName> From <Some_TableName>" NOTE: Can be used with min, max, and count functions. SUM sums all record values, VAR gives variance of all record values. SELECT VARIABLE AND PERFORM ARITHMETIC FUNCTION ON VARIABLE BASED OF GROUPS DEFINED BY SELECTED VARIABLE ssql = "SELECT <Some_VarName>, Sum (<Another_VarName>) AS <result_VarName> FROM <Some_TableName> " & _ "GROUP BY <Some_VarName> " SELECT VARIABLE AND PERFORM ARITHMETIC FUNCTION ON VARIABLE BASED OF GROUPS DEFINED BY SELECTED VARIABLE AND RETURN GROUPS BASED ON HAVING CRITERIA** ssql = "SELECT <Some_VarName>, Sum(<Another_VarName>) AS <result_VarName> FROM <Some_TableName> " & _ "GROUP BY <Some_VarName> HAVING Sum(<Another_VarName>) > <Some_Value>" SELECT VARIABLE AND PERFORM ARITHMETIC FUNCTION ON VARIABLE BASED OF GROUPS DEFINED BY SELECTED VARIABLE AND RETURN GROUPS BASED ON HAVING CRITERIA WITH WILD CARD STRING SPECIFICATION BY []% ssql = "SELECT <Some_VarName>, Sum(<Another_VarName>) FROM <Some_TableName> " & _ "GROUP BY <Some_VarName> " & _ "HAVING Sum (<Another_VarName>) > <Some_Value> and <Some_VarName> LIKE '[<chr><chr><chr>]%'" INNER JOINS WITH WHERE, GROUP BY, AND ORDER BY STATEMENT (see note on Page 4 for Right and Left Joins for explanation of Inner Join) SELECT <table1.var1, table1.var2,…>, sum(table3.var1 *table4.var1) AS <varname> FROM ((table1 INNER JOIN table2 ON (table1.var1=table2.var1) AND (table1.var2=table2.var2)) INNER JOIN table3 ON table2.var3 = table3.var1) INNER JOIN table4 ON (table2.var4 = table4.var1) AND (table2.var5 =table4.var2 ) WHERE table1.var3 = ‘<some value>’ GROUP <table1.var1, table1.var2,…> ORDER BY table1.var1, table1.var2 EXAMPLE: SELECT project.ta, project.prodCode, project.projTitle, project.InCharge,project.deliverable, sum(estimatesFTE.intDays *activityDiff.weight ) AS actDays FROM ((project INNER JOIN activity ON (project.projTitle=activity.projTitle) AND (project.inCharge=activity.inCharge)) INNER JOIN activityDiff ON activity.activityDiff = activityDiff.activityDiff) INNER JOIN estimatesFTE ON (activity.activityValue=estimatesFTE.activity) AND (activity.deliverable=estimatesFTE.deliverable) WHERE project.deliverable = 'Report' GROUP By project.ta, project.prodCode, project.projTitle, project.InCharge,project.deliverable ORDER BY project.ta *When using wild card specification, differences exist between VBA in Excel2010 and earlier versions. The example given is for Excel VBA 2010. ** The WHERE clause is used to subset records based on database variables and HAVING is used to subset records with variables derived from functions such as SUM(). Generally, use WHERE to filter records; use HAVING to filter groups. mselzer@att.net

  7. A Primer for Integrating Microsoft Excel, Access, and SAS with Visual Basic For Applications ASA Meetings 2013 Montreal, Canada, Martin Selzer Ph.D. 3. Reading and Writing to Excel Spreadsheets and Access Database Tables 3.1 Subroutine to Specify Paths, Filenames, Databases, and Work Sheets Sub SpecifyInputsOutputs(PathToWrite, FileToWrite, SheetToWrite, PathToRead, FileToRead, SheetToRead, DBToWrite, _ tabletowrite, DBToRead, tabletoRead) PathToWrite = "C:\Usersl\Public\yourPath\" FileToWrite = "yourOutBook.xlsx" SheetToWrite = "yourOutSheet" PathToRead = "C:\Users\Public\yourPath\" FileToRead = "yourInBook.xlsx" SheetToRead = "yourInSheet" DBToWrite = "yourDBOut.mdb" tabletowrite = "yourDBTableOut" DBToRead = "yourDBIn.mdb" tabletoRead = "yourDBTableIn" End Sub 3.2 Subroutine to Read from an Open SpreadSheet and Write to an Open SpreadSheet using Cell References Sub ReadOpenSSWriteOpenSS() Dim PathToWrite As String, PathToRead As String Dim FileToRead As String, FileToWrite As String, SheetToWrite As String, SheetToRead As String Dim DBToRead As String, DBToWrite As String, DBTableToRead, DBTableToWrite Dim i As Long, j As Long Dim StartOutRow As Long, StartOutCol As Long, numRecs As Long, numcols As Long Call SpecifyInputsOutputs(PathToWrite, FileToWrite, SheetToWrite, PathToRead, FileToRead, SheetToRead, DBToWrite, DBTableToWrite, _ DBToRead, DBTableToRead) StartOutRow = 1 StartOutCol = 1 numRecs = 10 numcols = 10 For i = StartOutRow To numRecs + StartOutRow For j = StartOutCol To numcols + StartOutCol Workbooks(FileToWrite).Sheets(SheetToWrite).Cells(i, j) = _ Workbooks(FileToRead).Sheets(SheetToRead).Cells(i, j) Next j Next i End Sub These variables are set by the programmer depending on the input file and output requirements. This is simple embedded loop to write cells from one sheet to another. mselzer@att.net

  8. A Primer for Integrating Microsoft Excel, Access, and SAS with Visual Basic For Applications ASA Meetings 2013 Montreal, Canada, Martin Selzer Ph.D. 3. Reading and Writing to Excel Spreadsheets and Access Database Tables - Continued 3.3 Subroutine to Read from an Open or Closed SpreadSheet or Database and Write to an Open SpreadSheet using SQL and cell references Sub ReadSSorDBWriteOpenSS() Dim PathToWrite As String, PathToRead As String Dim FileToRead As String, FileToWrite As String, SheetToWrite As String, SheetToRead As String, ssql As String Dim DBToRead As String, DBToWrite As String, DBTableToRead, DBTableToWrite, strWorkbook As String Dim rs As ADODB.Recordset, conn As ADODB.Connection Dim EntirePath_OpenWorkbook As String, getSheet As String, excelVersion As String, providerAndDataSource As String Dim startCol As Long, outRow As Long, outCol As Long, inField As Long excelVersion = "2010" Call SpecifyInputsOutputs(PathToWrite, FileToWrite, SheetToWrite, PathToRead, FileToRead, SheetToRead, DBToWrite, _ DBTableToWrite, DBToRead, DBTableToRead) ssql = "SELECT * FROM [" & SheetToRead & "$]" Call runQuery(ssql, rs, excelVersion, PathToRead & FileToRead, "excel") 'ssql = "SELECT * FROM [" & DBTableToRead & "]" 'Call runQuery(ssql, rs, excelVersion, PathToRead & DBToRead, "access") startCol = 1 outRow = 1 outCol = startCol rs.MoveFirst While rs.EOF = False If outRow = 1 Then For inField = 0 To rs.Fields.Count - 1 Workbooks(FileToWrite).Sheets(SheetToWrite).Cells(outRow, outCol) = rs.Fields(inField).Name outCol = outCol + 1 Next inField outRow = outRow + 1 rs.MoveNext End If outCol = startCol For inField = 0 To rs.Fields.Count - 1 Workbooks(FileToWrite).Sheets(SheetToWrite).Cells(outRow, outCol) = rs.Fields(inField).Value outCol = outCol + 1 Next inField outRow = outRow + 1 rs.MoveNext Wend End Sub This is code that runs query and puts data from excel sheet into recordset. Input sheet may be open or closed. This is code to open Excel sheet as database connection. This is code that runs query and puts data from database into recordset. To use this code, uncomment code and comment code above. This is code with embedded loops to write recordset to output Excel sheet. mselzer@att.net

  9. A Primer for Integrating Microsoft Excel, Access, and SAS with Visual Basic For Applications ASA Meetings 2013 Montreal, Canada, Martin Selzer Ph.D. 3. Reading and Writing to Excel Spreadsheets and Access Database Tables - Continued 3.4 Subroutine to Read from an Open or Closed SpreadSheet or Database and Write to a Closed Workbook Sub ReadSSorDBWriteClosedSS() 'Note: If you use option explicit you will need to declare some variables here. ' To get all the code on one slide I am not showing these declarations Dim rs As ADODB.Recordset, XLToWrite As Excel.Application Call SpecifyInputsOutputs(PathToWrite, FileToWrite, SheetToWrite, PathToRead, FileToRead, SheetToRead, _ DBToWrite, DBTableToWrite, DBToRead, DBTableToRead) excelVersion = "2010" ssql = "SELECT * FROM [" & SheetToRead & "$]" Call runQuery(ssql, rs, excelVersion, PathToRead & FileToRead, "excel") 'ssql = "SELECT * FROM [" & DBTableToRead & "]" 'Call runQuery(ssql, rs, excelVersion, PathToRead & DBToRead, "access") Set XLToWrite = CreateObject("Excel.Application") XLToWrite.Visible = False: XLToWrite.Application.DisplayAlerts = False XLToWrite.Workbooks.Open Filename:=PathToWrite & FileToWrite, ReadOnly:=False XLToWrite.Workbooks(FileToWrite).Activate n = XLToWrite.ActiveWorkbook.Worksheets.Count: foundSheet = False: countSheets = 1 While foundSheet = False And countSheets <= n nameis = XLToWrite.Workbooks(FileToWrite).Worksheets(countSheets).Name If XLToWrite.Workbooks(FileToWrite).Worksheets(countSheets).Name = SheetToWrite Then foundSheet = True Else countSheets = countSheets + 1 End If Wend If foundSheet = True Then XLToWrite.Workbooks(FileToWrite).Worksheets(SheetToWrite).Delete XLToWrite.Application.DisplayAlerts = True: XLToWrite.Workbooks(FileToWrite).Activate: XLToWrite.Sheets.Add.Name = SheetToWrite outColLetter = "a": outRowNum = "2" ReDim FieldNames(rs.Fields.Count) As String XLToWrite.Workbooks(FileToWrite).Sheets(SheetToWrite).Range(outColLetter & outRowNum).CopyFromRecordset rs For j = 0 To rs.Fields.Count - 1 FieldNames(j) = rs.Fields(j).Name Next j XLToWrite.Workbooks(FileToWrite).Sheets(SheetToWrite).Cells(1, 1).Resize(1, rs.Fields.Count) = FieldNames XLToWrite.ActiveWorkbook.Close savechanges:=True: XLToWrite.Visible = True: XLToWrite.Quit End Sub This is code that runs query and puts data from excel sheet in recordset. This is code that runs query and puts data from database into recordset. To use uncomment code and comment code above. This code opens file in the background and looks for sheet to write and adds it if it does not exist. If it exists, it deletes it and creates it again to erase existing data. This is code that writes data to sheet and then closes workbook open in the background. This is code to write field names to output sheet.. mselzer@att.net

  10. A Primer for Integrating Microsoft Excel, Access, and SAS with Visual Basic For Applications ASA Meetings 2013 Montreal, Canada, Martin Selzer Ph.D. 3. Reading and Writing to Excel Spreadsheets and Access Database Tables – Continued 3.5 Subroutine to Read from an Open or Closed SpreadSheet or database and Write to a Database* Sub ReadSSorDBWriteDB() 'Note: If you use option explicit you will need to declare some variables here. ' To get all the code on one slide I am not showing these declarations Call SpecifyInputsOutputs(PathToWrite, FileToWrite, SheetToWrite, PathToRead, FileToRead, SheetToRead, _ DBToWrite, tabletowrite, DBToRead, tabletoRead) Dim DB As Database Dim T As TableDef excelVersion = "2010" Set DB = OpenDatabase(PathToWrite & DBToWrite) FoundTable = False For Each T In DB.TableDefs If tabletowrite = T.Name Then FoundTable = True Next If FoundTable = True Then ssql = "Drop Table " & tabletowrite Call runQuery(ssql, rs, excelVersion, PathToWrite & DBToWrite, "access") End If 'NOTE: Use Below if Reading from Excel file and comment out code in next note below 'ssql = "SELECT * INTO [" & tabletowrite & "] " & _ "FROM [Excel 8.0;DATABASE=" & PathToRead & FileToRead & "].[" & SheetToRead & "$]" ssql = "SELECT * INTO " & tabletowrite & _ " FROM [Excel 12.0; DATABASE=" & PathToRead & FileToRead & "].[" & SheetToRead & "$];" Call runQuery(ssql, rs, excelVersion, PathToWrite & DBToWrite, "access") 'NOTE: Use Below if Reading from database file and comment out code in note above 'ssql = "SELECT * INTO " & PathToWrite & DBToWrite & "." & tabletowrite & " FROM " & tabletoRead 'Call runQuery(ssql, rs, excelVersion, PathToRead & DBToRead, "access") End Sub This code checks if this table exists and deletes it if it does. Use this ssql for Excel 8.0 files. Use this ssql for Excel 12.0 files. *Note: The path to the database and database name cannot have spaces. mselzer@att.net

  11. A Primer for Integrating Microsoft Excel, Access, and SAS with Visual Basic For Applications ASA Meetings 2013 Montreal, Canada, Martin Selzer Ph.D. 4. Using Proc Import & Proc Export in SAS to Read & Write Excel SpreadSheets or Access DBTables* 4.1 Reading Excel Spreadsheet in SAS proc import DATAFILE="<Some_Path\Some_ExcelFileName>" OUT =<Some_SAS_Work_DataSetName> DBMS=EXCEL REPLACE; sheet = "<Some_ExcelSheetName>";run; 4.2 Reading Access Database Table in SAS proc import table="<Some_Access_TableName>" out= <Some_SAS_Work_DataSetName> dbms=access replace; database="<Some_Path\Some_Access_DatabaseName>"; 4.3 Writing Excel Spreadsheet in SAS proc export data=<Some_SAS_Work_DataSetName> outfile="<Some_Path\Some_ExcelFileName>" dbms=excel replace; Sheet = "<some sheet name>"; run; 4.4 Writing Access Database Table in SAS proc export data=<Some_SAS_Work_DataSetName> outtable = "<Some_Access_TableName>" dbms=access replace;database="Some_Path\Some_AccessDataBaseName>"; Note: For all Excel and Access DB file designations, it is a good idea to include file extension specifiers such as .xlsx or accdb. *Note: SAS users make extensive use of the Output Delivery System (ODS) in SAS to output data to Excel from SAS generated by procedures. The ODS system is not addressed in this essay. 5. Executing VBA code from SAS options noxwait noxsync; filename excel DDE 'EXCEL|SYSTEM'; data _null_; rc=system('start excel'); rc = sleep(5); run; data _null_; file excel; put "[open(""<yourpath><yourExcelFile>"")]"; *i.e. "C:\testPgm.xlsm"; put '[run("<yourModule>.<yourRoutine>")]'; * i.e. moduleTest.macroTest; put "[save()]"; put "[quit()]"; run; mselzer@att.net

  12. A Primer for Integrating Microsoft Excel, Access, and SAS with Visual Basic For Applications ASA Meetings 2013 Montreal, Canada, Martin Selzer Ph.D. 6. Execute SAS From VBA: Integrating SAS with Excel and Access DatabasesUsing the DOS Shell ‘NOTE: Code before Subroutine below is global and at top of VBA program Private Declare Function GetExitCodeProcess Lib "kernel32" _ (ByVal hProcess As Long, _ lExitCode As Long) As Long Private Declare Function OpenProcess Lib "kernel32" ( _ ByVal dwDesiredAccess As Long, _ ByVal bInheritHandle As Long, _ ByVal dwProcessId As Long) As Long Sub RoutineToRunSASProgramFromVBA() Dim program As String Const AccessType = &H400 Const StillActive = &H103 formWaitSAS.Show (vbModeless) formWaitSAS.Repaint program = "C:\Program Files\SAS.922.EN\SASFoundation\9.2\sas.exe" & _ " <Some_Path>\Some_SASProgramName>" 'Example 'program = "C:\Program Files\SAS\SASFoundation\9.2\sas.exe" & _ " E:\DATA\Safe\Papers\testPgmASA2013\sasPgmTest.sas" TaskID = Shell(program) hProcess = OpenProcess(AccessType, False, TaskID) Do GetExitCodeProcess hProcess, lExitCode DoEvents Loop While lExitCode = StillActive Unload formWaitSAS MsgBox ("SAS code Executed.") End Sub These two statements show a form with a message that SAS is executing. If you do not want this to happen, comment these lines. This statement is the path and file name for executing PC SAS. It may differ in you computer environment. This statement unloads the form created in the statements above in brackets. If those statements are commented or deleted, then comment or delete this statement. mselzer@att.net

More Related