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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,993
Messages
5,834,772
Members
430,320
Latest member
Napzz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top