Using VBA and SAS

[HunterN]

Hi,

Can someone show me the commands to open SAS using VBA, so that I can
run a program that brings in a .CSV file and creates a SAS file on my PC.

I already have the SAS program working. I also have working the VBA code that will ftp the file to the UNIX machine.

Thanks,
Nancy

 

[BrianB]

You will probably need to use Shell and SendKeys. I suggest you experiment with these first before looking at these, which probably contain all you need - but you will need to adapt it :-

http://www.mrexcel.com/board2/viewtopic.php?t=170917

http://www.mrexcel.com/board2/viewtopic.php?p=835993#835993

This is not a task for the fainthearted.

 

[HunterN]

To BrianB and anyone else that would need to know:

This is the code that I used to open SAS using VBA, and then FTP the file to the UNIX machine.


public UserName as String

Private Sub worksheet_Activate()
UserName = Environ("USERNAME")
End Sub

(The following was code I found while searching the internet for help!)

Public MyPasswd As String

Sub Get_Password()
Frm_Passwd.Show
End Sub

Sub FTP_Put()

Dim strDirectoryList As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer
Dim filePath As String


Call GetDate
If FileDate = "" Then
Exit Sub
End If

On Error GoTo Err_Handler
lStr_Dir = ThisWorkbook.Path
lInt_FreeFile01 = FreeFile 'sets to integer 1
lInt_FreeFile02 = FreeFile 'sets to integer 1

strDirectoryList = lStr_Dir & "\Directory"

' Delete completion file if there
If Dir(strDirectoryList & ".out") <> "" Then
Kill (strDirectoryList & ".out")
End If

' Create the directory path to ftp to
filePath = "cd /cpspb/home/" & UserName
' Create text file with FTP commands - it is called Directory.txt
Open strDirectoryList & ".txt" For Output As #lInt_FreeFile01
Print #lInt_FreeFile01, "open Your ISP address"
Print #lInt_FreeFile01, UserName
Print #lInt_FreeFile01, MyPasswd
Print #lInt_FreeFile01, "binary"
Print #lInt_FreeFile01, filePath
Print #lInt_FreeFile01, "put " _
& ThisWorkbook.Path & "\testspideroutput.sas7bdat" _
& " " & FileDate & "testspideroutput.sas7bdat"
Print #lInt_FreeFile01, "bye"
Close #lInt_FreeFile01


' Create Batch program
Open strDirectoryList & ".bat" For Output As #lInt_FreeFile02
Print #lInt_FreeFile02, "ftp -s:" & strDirectoryList & ".txt"
Print #lInt_FreeFile02, "Echo ""FTP Complete"" > " & strDirectoryList & ".out"
Close #lInt_FreeFile02

' Invoke Direcotry List generator
retVal = Shell(strDirectoryList & ".bat", vbHide)
' Wait for completion
Do While Dir(strDirectoryList & ".out") = ""
DoEvents
Loop

Application.Wait (Now + TimeValue("0:00:03"))

' Clean up files
If Dir(strDirectoryList & ".bat") <> "" Then
Kill (strDirectoryList & ".bat")
End If

' If Dir(strDirectoryList & ".txt") <> "" Then
' Kill (strDirectoryList & ".txt")
' End If

bye:
Exit Sub


Err_Handler:
MsgBox "Error : " & Err.Number & vbCrLf & _
"Description : " & Err.Description, vbCritical
Resume bye

End Sub




Thanks for your help,
Nancy

 

[blackbible]

To BrianB and anyone else that would need to know:

This is the code that I used to open SAS using VBA, and then FTP the file to the UNIX machine.


public UserName as String

Private Sub worksheet_Activate()
UserName = Environ("USERNAME")
End Sub

(The following was code I found while searching the internet for help!)

Public MyPasswd As String

Sub Get_Password()
Frm_Passwd.Show
End Sub

Sub FTP_Put()

Dim strDirectoryList As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer
Dim filePath As String


Call GetDate
If FileDate = "" Then
Exit Sub
End If

On Error GoTo Err_Handler
lStr_Dir = ThisWorkbook.Path
lInt_FreeFile01 = FreeFile 'sets to integer 1
lInt_FreeFile02 = FreeFile 'sets to integer 1

strDirectoryList = lStr_Dir & "\Directory"

' Delete completion file if there
If Dir(strDirectoryList & ".out") <> "" Then
Kill (strDirectoryList & ".out")
End If

' Create the directory path to ftp to
filePath = "cd /cpspb/home/" & UserName
' Create text file with FTP commands - it is called Directory.txt
Open strDirectoryList & ".txt" For Output As #lInt_FreeFile01
Print #lInt_FreeFile01, "open Your ISP address"
Print #lInt_FreeFile01, UserName
Print #lInt_FreeFile01, MyPasswd
Print #lInt_FreeFile01, "binary"
Print #lInt_FreeFile01, filePath
Print #lInt_FreeFile01, "put " _
& ThisWorkbook.Path & "\testspideroutput.sas7bdat" _
& " " & FileDate & "testspideroutput.sas7bdat"
Print #lInt_FreeFile01, "bye"
Close #lInt_FreeFile01


' Create Batch program
Open strDirectoryList & ".bat" For Output As #lInt_FreeFile02
Print #lInt_FreeFile02, "ftp -s:" & strDirectoryList & ".txt"
Print #lInt_FreeFile02, "Echo ""FTP Complete"" > " & strDirectoryList & ".out"
Close #lInt_FreeFile02

' Invoke Direcotry List generator
retVal = Shell(strDirectoryList & ".bat", vbHide)
' Wait for completion
Do While Dir(strDirectoryList & ".out") = ""
DoEvents
Loop

Application.Wait (Now + TimeValue("0:00:03"))

' Clean up files
If Dir(strDirectoryList & ".bat") <> "" Then
Kill (strDirectoryList & ".bat")
End If

' If Dir(strDirectoryList & ".txt") <> "" Then
' Kill (strDirectoryList & ".txt")
' End If

bye:
Exit Sub


Err_Handler:
MsgBox "Error : " & Err.Number & vbCrLf & _
"Description : " & Err.Description, vbCritical
Resume bye

End Sub




Thanks for your help,
Nancy

hi,

can you share the exact code for the same?
i have to do the same thing and the code shared doesnot work.