This is not an Excel Programming issue although I would like to incorporate
this functionallity into Excel once it is working.

I need a batch program script either for windows or MVS that will download a
fixed length record from the mainframe to my pc for subsequent processing and
analysis. Does anyone out there have such an animal or perhaps can point me
to a resource from one?

Thanks.

JEB

Here's one that used to work for me (haven't used it in a few years, though):

This goes in a General module:

Option Explicit
Private Const PROCESS_QUERY_INFORMATION As Long = &H400
Private Const STILL_ACTIVE As Long = &H103

Private Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, lpExitCode As Long) As Long

''' Arguments: szCommandLine The command line to execute using Shell.
''' iWindowState (Optional) The window state parameter to pass
''' to the Shell function. Default = vbHide.
''' See Shell function help for other options.
Sub ShellAndWait(ByVal szCommandLine As String, _
Optional ByVal iWindowState As Integer = vbHide)

Dim lTaskID As Long
Dim lProcess As Long
Dim lExitCode As Long
Dim lResult As Long

''' Run the Shell function.
lTaskID = Shell(szCommandLine, iWindowState)

''' Get the process handle from the task ID returned by Shell.
lProcess = OpenProcess(PROCESS_QUERY_INFORMATION, 0&, lTaskID)

''' Loop while the shelled process is still running.
Do
''' lExitCode will be set to STILL_ACTIVE as long as the shelled process
' is running.
lResult = GetExitCodeProcess(lProcess, lExitCode)
DoEvents
Loop While lExitCode = STILL_ACTIVE

End Sub


==========
And this goes behind a userform that collected the mainframe's id and password:

Option Explicit
Private Sub CommandButton1_Click()

Dim myUserId As String
Dim myPassword As String
Dim intFileNum As Long
Const myFTPtxt_file = "c:\myftp.txt"
Const work_directory = "C:\"
Const strFile As String = "SomeMFFileNameHere.data"

myUserId = Trim(TextBox2.Text)
myPassword = Trim(TextBox3.Text)

On Error GoTo do_ftpError

intFileNum = FreeFile()
Close #intFileNum
Open myFTPtxt_file For Output As #intFileNum

'Create the FTP Command File.
Print #intFileNum, "open xxxxxxx"
Print #intFileNum, myUserId
Print #intFileNum, myPassword
Print #intFileNum, "put " & work_directory & strFile
Print #intFileNum, "bye"
Close #intFileNum

'Execute the FTP Commands to upload the file.
ShellAndWait "ftp.exe -v -s:" & myFTPtxt_file, vbMinimizedNoFocus

'Delete both temporary files.
Kill myFTPtxt_file

Application.StatusBar = False
Exit Sub

do_ftpError:
Close #intFileNum
MsgBox "FTP Failed: The VB Error Was As Follows:" & _
Chr(13) & Error(Err), vbCritical

End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub



JEB wrote:

This is not an Excel Programming issue although I would like to incorporate
this functionallity into Excel once it is working.

I need a batch program script either for windows or MVS that will download a
fixed length record from the mainframe to my pc for subsequent processing and
analysis. Does anyone out there have such an animal or perhaps can point me
to a resource from one?

Thanks.

JEB

--

Dave Peterson

Dave;
Thank you very much. I'll give it a try and let you know how it
works.
John


"Dave Peterson" wrote:

Here's one that used to work for me (haven't used it in a few years, though):

This goes in a General module:

Option Explicit
Private Const PROCESS_QUERY_INFORMATION As Long = &H400
Private Const STILL_ACTIVE As Long = &H103

Private Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, lpExitCode As Long) As Long

''' Arguments: szCommandLine The command line to execute using Shell.
''' iWindowState (Optional) The window state parameter to pass
''' to the Shell function. Default = vbHide.
''' See Shell function help for other options.
Sub ShellAndWait(ByVal szCommandLine As String, _
Optional ByVal iWindowState As Integer = vbHide)

Dim lTaskID As Long
Dim lProcess As Long
Dim lExitCode As Long
Dim lResult As Long

''' Run the Shell function.
lTaskID = Shell(szCommandLine, iWindowState)

''' Get the process handle from the task ID returned by Shell.
lProcess = OpenProcess(PROCESS_QUERY_INFORMATION, 0&, lTaskID)

''' Loop while the shelled process is still running.
Do
''' lExitCode will be set to STILL_ACTIVE as long as the shelled process
' is running.
lResult = GetExitCodeProcess(lProcess, lExitCode)
DoEvents
Loop While lExitCode = STILL_ACTIVE

End Sub


==========
And this goes behind a userform that collected the mainframe's id and password:

Option Explicit
Private Sub CommandButton1_Click()

Dim myUserId As String
Dim myPassword As String
Dim intFileNum As Long
Const myFTPtxt_file = "c:\myftp.txt"
Const work_directory = "C:\"
Const strFile As String = "SomeMFFileNameHere.data"

myUserId = Trim(TextBox2.Text)
myPassword = Trim(TextBox3.Text)

On Error GoTo do_ftpError

intFileNum = FreeFile()
Close #intFileNum
Open myFTPtxt_file For Output As #intFileNum

'Create the FTP Command File.
Print #intFileNum, "open xxxxxxx"
Print #intFileNum, myUserId
Print #intFileNum, myPassword
Print #intFileNum, "put " & work_directory & strFile
Print #intFileNum, "bye"
Close #intFileNum

'Execute the FTP Commands to upload the file.
ShellAndWait "ftp.exe -v -s:" & myFTPtxt_file, vbMinimizedNoFocus

'Delete both temporary files.
Kill myFTPtxt_file

Application.StatusBar = False
Exit Sub

do_ftpError:
Close #intFileNum
MsgBox "FTP Failed: The VB Error Was As Follows:" & _
Chr(13) & Error(Err), vbCritical

End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub



JEB wrote:

This is not an Excel Programming issue although I would like to incorporate
this functionallity into Excel once it is working.

I need a batch program script either for windows or MVS that will download a
fixed length record from the mainframe to my pc for subsequent processing and
analysis. Does anyone out there have such an animal or perhaps can point me
to a resource from one?

Thanks.

JEB

--

Dave Peterson