displaying result of shell command

  • Thread starter Thread starter Ian Haining
  • Start date Start date
I

Ian Haining

I haven't dirtied my hands with Excel programming for years and am kind
of lost.
I've created a simple macro to send a remote command ("rsh") to our UNIX
server, and this should return several lines of text as a result (works
OK in a command prompt window). However, I'm not sure how to go about
getting the results to display in an Excel worksheet. I can redirect the
output to a text file, and I was thinking along the lines of importing
the text file to a text box, but I don't know how to do this.
Can anyone help, or suggest a better way of doing this.
 
Ian,

Below is a function that will return the contents of a selected text file as
a string, which you can then use to set the textbox's text.

Of course, you don't need to use the GetOpenFileName method: any way that
you want to pass the file path and name to the function would be okay as
well.

HTH,
Bernie
MS Excel MVP

Sub Test()
MsgBox ReadFile
'You could use this like
'Userform1.Textbox1.Text = ReadFile
End Sub

Function ReadFile() As String
Dim SourceNum As Integer
Dim Temp As String
Dim myFile As String
Dim myContent As String

myFile = Application.GetOpenFilename("Text Files (*.txt),*.txt,")

' Open the source text file.
SourceNum = FreeFile()
Open myFile For Input As SourceNum
myContent = "nothing yet, bd"

' Read each line of the source file and append it to the string
Do While Not EOF(SourceNum)
Line Input #SourceNum, Temp
If myContent = "nothing yet, bd" Then
myContent = Temp
Else
myContent = myContent & Chr(10) & Temp
End If
Loop

ReadFile = myContent

End Function
 
And a couple of links that may help if you have to wait for your rsh command to
finish.

http://support.microsoft.com/?kbid=214248
XL2000: How to Force Macro Code to Wait for Outside Procedure

Here's a link to a nice ShellAndWait function that does that.
http://groups.google.com/groups?threadm=ump2dlfcBHA.1656@tkmsftngp03

(I like to use the second one.)

And in addition to Bernie's code, you might be able to just incorporate a
File|open in your code. Maybe you could use excel's built-in text to columns
feature to parse your data.
 
Back
Top