Using VBA and SAS

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479
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

New Member
Joined
Jun 29, 2015
Messages
1
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,084
Messages
5,570,129
Members
412,305
Latest member
Mozz
Top