none
Redirect output from a command to a cell in Excel

    Question

  • So, I can run a command and output redirect to a text or csv.

    c:\hostname > hostname.txt

    What I would like is to be able to redirect to a specific cell in a spreadsheet (active workbook and all...)

    Something like the following from an already opened workbook:

    Sub RedirectTest()

    Shell ("C:\Windows\System32\cmd.exe /k hostname > r4c15)

    End Sub

    Obviously, the '>' doesn't work. Is there anything that does?


    Monday, June 10, 2013 6:36 PM

Answers

  • After working on this some more, I think I have the answer.

    I've tested this many times and it seems to work on several different devices. :)

    Sub RunAllvariouscommands()
                                'This sets up a wait on return value before moving on. Used in clipboard paste.
    Dim retval
    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 0
                                'This sets up a loop to run the macros using a starting row.
        Dim oRows(4) As Integer
            oRows(0) = 4
            oRows(1) = 21
            oRows(2) = 38
            oRows(3) = 55
            oRows(4) = 72
            Dim count As Integer
            count = 0
        Dim oCell As Range
        Do
            For Each oCell In ActiveSheet.UsedRange.Rows(oRows(count)).Cells
                If ActiveSheet.Cells(oCell.Row + 7, oCell.Column).Value = "Brocade" Then
                    Shell ("C:\Windows\System32\cmd.exe /k cd c:\&&plink" & " " & ActiveSheet.Cells(oCell.Row + 8, oCell.Column).Value & " -l " & Range("A12") & " -pw " & Range("A14") & " " & """uptime""" & " > c:\DailyHC\" & Range("A1") & "\" & ActiveSheet.Cells(oCell.Row + 7, oCell.Column).Value & "\" & ActiveSheet.Cells(oCell.Row - 2, oCell.Column).Value & "_" & Format(Now, "MM-DD-YYYY_hh-mm-ss") & "_uptime.txt&&exit")
                        If Weekday(Date$) = 2 Then
                            Shell ("C:\Windows\System32\cmd.exe /k cd c:\&&plink" & " " & ActiveSheet.Cells(oCell.Row + 8, oCell.Column).Value & " -l " & Range("A12") & " -pw " & Range("A14") & " " & """fabstatsclear""" & " > c:\DailyHC\" & Range("A1") & "\" & ActiveSheet.Cells(oCell.Row + 7, oCell.Column).Value & "\" & ActiveSheet.Cells(oCell.Row - 2, oCell.Column).Value & "_" & Format(Now, "MM-DD-YYYY_hh-mm-ss") & "_fabstatsclear.txt&&exit")
                        End If

                    Shell ("C:\Windows\System32\cmd.exe /k cd c:\&&plink" & " " & ActiveSheet.Cells(oCell.Row + 8, oCell.Column).Value & " -l " & Range("A12") & " -pw " & Range("A14") & " " & """errdump""" & " > c:\DailyHC\" & Range("A1") & "\" & ActiveSheet.Cells(oCell.Row + 7, oCell.Column).Value & "\" & ActiveSheet.Cells(oCell.Row - 2, oCell.Column).Value & "_" & Format(Now, "MM-DD-YYYY_hh-mm-ss") & "_errdump.txt&&exit")
                   
                    Call ClearClipboard
                    retval = wsh.Run("C:\Windows\System32\cmd.exe /k c:&&cd c:\&&plink" & " " & ActiveSheet.Cells(oCell.Row + 8, oCell.Column).Value & " -l " & Range("A12") & " -pw " & Range("A14") & " " & """version | grep OS""" & " | clip&&exit", windowStyle, waitOnReturn)
                    ActiveSheet.Cells(oCell.Row + 12, oCell.Column).Activate
                    ActiveSheet.Paste
                                               'Lots and lots of other ElseIf statements...
            End If
        Next
        count = count + 1
        Loop Until (count = 4)
    End Sub

    Thursday, June 27, 2013 2:56 AM
  • Sorry - I forgot that OpenText does not return a workbook. Replace that line with

            Workbooks.OpenText strPath & strWFile
            Set wkbkWF = ActiveWorkbook

    And you should record a macro where you open one of the text files to get the proper delimiting, etc for the .OpenText parameters, like

        Workbooks.OpenText strPath & strWFile, Origin:=437, StartRow:=1, DataType:=xlDelimited, Comma:=True

    Tuesday, June 11, 2013 5:13 PM

All replies

  • Open the resulting file. You can open the entire file into a workbook using

    Workbooks.OpenText "C:\hostname.txt"

    or you can read the file line by line:

    FileNum = FreeFile()
    Open "C:\hostname.txt" For Input As #FileNum

    etc.

    And then find the value of interest and place it in your cell:

    Thisworkbook.Worksheets(1).Cells(4,15).Value = ".....what you found...."

    A little more specifics on what is in the output file and what you want from it would help refine your options.

    Monday, June 10, 2013 6:56 PM
  • Hello Bernie,

    I have a spreadsheet with many location based macros as well as one macro to emulate all of these other macros based on rows.

    What I am attempting to do is have a line placed into a macro that will poll a target device and pull specific information into a specific cell. IE: telnet to an IP Address, run hostname, and place that return automatically into a specific cell in a column, then repeat for the next column. A new worksheet wouldn't really work in this case.

    My output redirects that I currently have are all text files that are all unique. IE date/time stamped as a part of the filename. With the massive amount of data that I poll, having one generic file wont work.

    From multiple sources:

    hostname

    server1

    hostname

    server2

    etc... where server 1 goes in row 15 on its specific column and server2 goes in row 15 on its specific column.

    Monday, June 10, 2013 7:09 PM
  • I can activate a cell and run the following macro:

    Sub test()
    SendKeys ("test1")
    End Sub

    This indeed places test1 in that cell.

    So the next question is, how do I get the output of a command to "be" the SendKeys?

    Tuesday, June 11, 2013 1:35 PM
  • You can loop through tens, hundreds, thousands of files to extract information. Just name your files logically, and store them where you can find them, and use a macro. For example, to pull a value that would be in cell B4 if you were to open the Txt file in Excel, from every .txt file in the same folder as the workbook with the macro:

    Sub LoopThroughFiles()
        Dim strPath As String
        Dim strWFile As String
        Dim wkbkWF As Workbook
        Dim Counter As Integer

        Counter = 1

        Application.DisplayAlerts = False

        strPath = ThisWorkbook.Path & "\"
        strWFile = Dir(strPath & "*.txt")
        Do While strWFile <> ""
            Set wkbkWF = Workbooks.OpenText(strPath & strWFile)
            With ThisWorkbook.Worksheets(1)
                .Cells(Counter, 1).Value = wkbkWF.Name
                .Cells(Counter, 2).Value = _
                wkbkWF.Worksheets(1).Range("B4").Value
            End With
            Counter = Counter + 1
            wkbkWF.Close False
            strWFile = Dir()
        Loop

    End Sub


    Tuesday, June 11, 2013 2:25 PM
  • Shell("C:\Windows\System32\cmd.exe /k cd c:\&&plink" & " " & ActiveSheet.Cells(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row + 7, ActiveSheet.Buttons(Application.Caller).TopLeftCell.Column).Value & " -l " & Range("A11") & " -pw " & Range("A13") & " " & """hostname""").PutInClipboard  ' & ActiveSheet.Cells(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row + 11, ActiveSheet.Buttons(Application.Caller).TopLeftCell.Column).Paste)

    Tried the .PutInClipboard. Now getting invalid qualifier on the Shell. (The rest is remarked out for now...)

    (+ 7 is IP Address. + 11 is the target to paste...)

    Tuesday, June 11, 2013 2:46 PM
  • Hi Bernie.

    Just saw this. I'll check it out and see if I can make it work.

    Thanks!

    Tuesday, June 11, 2013 2:55 PM
  • Is there any problem with having a loop within a loop?
    Tuesday, June 11, 2013 3:00 PM
  • Getting a compile error: Expected Function or variable @ Set wkbkWF = Workbooks.OpenText(strPath & strWFile) with the '.OpenText' highlighted.
    Tuesday, June 11, 2013 4:58 PM
  • Sorry - I forgot that OpenText does not return a workbook. Replace that line with

            Workbooks.OpenText strPath & strWFile
            Set wkbkWF = ActiveWorkbook

    And you should record a macro where you open one of the text files to get the proper delimiting, etc for the .OpenText parameters, like

        Workbooks.OpenText strPath & strWFile, Origin:=437, StartRow:=1, DataType:=xlDelimited, Comma:=True

    Tuesday, June 11, 2013 5:13 PM
  • No worries. I replaced the line with the  two lines.

    Files are being created via the macro. This macro is added in after (in the same sub).

    I am getting a Run-time error '1004'.

    File (It names the FULL file despite the wildcard in the popup...) could not be found.

    It seems odd that A - the popup names the full file and B - I am looking at the file in the specified location...

    Sub VersionTest()
    Shell ("C:\Windows\System32\cmd.exe /k c:&&cd c:\&&plink" & " " & ActiveSheet.Cells(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row + 7, ActiveSheet.Buttons(Application.Caller).TopLeftCell.Column).Value & " -l " & Range("A11") & " -pw " & Range("A13") & " " & """configshow -all | grep FOS""" & " > c:\DailyHC\" & Range("A1") & "\" & ActiveSheet.Cells(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row + 6, ActiveSheet.Buttons(Application.Caller).TopLeftCell.Column).Value & "\" & ActiveSheet.Cells(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row - 2, ActiveSheet.Buttons(Application.Caller).TopLeftCell.Column).Value & "_" & Format(Now, "MM-DD-YYYY_hh-mm-ss") & "_FOS-Version.txt&&exit")
        Dim strPath As String
        Dim strWFile As String
        Dim wkbkWF As Workbook
        Dim Counter As Integer
        Counter = 1
        Application.DisplayAlerts = False
        strPath = ThisWorkbook.Path & "\" & Range("A1") & "\" & ActiveSheet.Cells(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row + 6, ActiveSheet.Buttons(Application.Caller).TopLeftCell.Column).Value & "\" & ActiveSheet.Cells(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row - 2, ActiveSheet.Buttons(Application.Caller).TopLeftCell.Column).Value
        strWFile = Dir(strPath & "*FOS-Version.txt")
        Do While strWFile <> ""
            Workbooks.OpenText strPath & strWFile
            Set wkbkWF = ActiveWorkbook
            With ThisWorkbook.Worksheets(1)
                .Cells(Counter, 1).Value = wkbkWF.Name
                .Cells(Counter, 2).Value = _
                wkbkWF.Worksheets(1).Range("C15")
            End With
            Counter = Counter + 1
            wkbkWF.Close False
            strWFile = Dir()
        Loop
    End Sub

    The return copied directly from the file:

    FOS version = v7.0.1.0

    (The bit that I want is the vx.x.x.x, but I can deal with that later...)

    Tuesday, June 11, 2013 7:38 PM
  • You need a terminal \ on the strPath:

    strPath = strPath & "\"


    Tuesday, June 11, 2013 7:56 PM
  • Added to the end of the path. No longer getting an error, but nothing is populating.
    Wednesday, June 12, 2013 1:13 PM
  • Put a break on the line:

            With ThisWorkbook.Worksheets(1) 


      and then take a look at Excel - is there a value in cell C15 of the active workbook?

    Wednesday, June 12, 2013 1:22 PM
  • Not sure what you mean by a break... Comment out the line? If this is what you meant by a break, I tried that and recieved a compile error: Invalid or unqualified reference. .Cells is highlighted.

    I probably have that wrong... lol

    There is nothing in C15 currently. I did test a recorded macro by opening the file and copying/pasting the data into the cell, but all I got in the macro was something like activeworkbook.paste.

    Wednesday, June 12, 2013 2:33 PM
  • To insert a break, either put your cursor on the line and press F9, or click in the grey bar to the left of the code line. With either method, a dot will appear in the grey bar and the line will be colored (colors are optional, but the default is a dark red).

    If you open your "FOS-Version.txt" file in Excel and there is nothing in cell C15, then your code:

    With ThisWorkbook.Worksheets(1) 
                .Cells(Counter, 1).Value = wkbkWF.Name 
                .Cells(Counter, 2).Value = _ 
                wkbkWF.Worksheets(1).Range("C15") 
            End With 

    will have nothing to read - What cell contains the information that you want when you open the FOS txt file?


    Wednesday, June 12, 2013 3:15 PM
  • Hmm... There might be confustion...

    The text file has the information that I want to put into C15. What Cells have the information is a combination of a command and the IP Address cell to a plink macro that outputs to a text file. Initially, I want to simply redirect the output to a cell. But this appears to be impossible as there is no way to "write" the output to the clipboard.

    Should I see something with the break after running the macro?

    Wednesday, June 12, 2013 3:58 PM
  • There is confusion. If you open the text file with Excel, what cell does the information that you want appear in?
    Wednesday, June 12, 2013 4:44 PM
  • It doesn't appear anywhere. Or... perhaps I am not opening the file with Excel. (?) (This is probably the case!)

    Going to go look...

    Wednesday, June 12, 2013 5:47 PM
  • Yep. That was the case.

    Excel 2007. Standard (default (next/next/next...)) delim puts the data I am looking for into D1.

    Wednesday, June 12, 2013 5:50 PM
  • I have a question that this brings up...

    I have 20 or so tabs. All of these have D1 already populated with other information.

    Do I need a separate tab for importing and copying to one of the 20 or so tabs?

    Wednesday, June 12, 2013 6:03 PM
  • Then you need to change C15 to D1

     With ThisWorkbook.Worksheets(1) 
                .Cells(Counter, 1).Value = wkbkWF.Name 
                .Cells(Counter, 2).Value = _ 
                wkbkWF.Worksheets(1).Range("D1") 

            End With 

    As for the tabs - do you have named tabs that correspond to the files, or do you want to build a table? That was why I used Counter - to build a table starting at row 1, one row for each file, as Counter is incremented after each file is processed.

    Wednesday, June 12, 2013 6:37 PM
  • I am building a sample spreadsheet right now. Is there a way to send this?
    Wednesday, June 12, 2013 7:14 PM
  • bdeitrick at alum dot mit dot edu

    Send a couple of the txt files as well.

    Wednesday, June 12, 2013 7:18 PM
  • Will do.
    Wednesday, June 12, 2013 7:23 PM
  • When you get this, don't be alarmed at the directions tab. A good portion of this is copied from my actual spreadsheet meant for those who have little to no experience with Excel.
    Wednesday, June 12, 2013 7:28 PM
  • Hello Bernie,

    I was just checking in to see if you have received the spreadsheet.

    Thank you,

    Friday, June 14, 2013 12:05 PM
  • After working on this some more, I think I have the answer.

    I've tested this many times and it seems to work on several different devices. :)

    Sub RunAllvariouscommands()
                                'This sets up a wait on return value before moving on. Used in clipboard paste.
    Dim retval
    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 0
                                'This sets up a loop to run the macros using a starting row.
        Dim oRows(4) As Integer
            oRows(0) = 4
            oRows(1) = 21
            oRows(2) = 38
            oRows(3) = 55
            oRows(4) = 72
            Dim count As Integer
            count = 0
        Dim oCell As Range
        Do
            For Each oCell In ActiveSheet.UsedRange.Rows(oRows(count)).Cells
                If ActiveSheet.Cells(oCell.Row + 7, oCell.Column).Value = "Brocade" Then
                    Shell ("C:\Windows\System32\cmd.exe /k cd c:\&&plink" & " " & ActiveSheet.Cells(oCell.Row + 8, oCell.Column).Value & " -l " & Range("A12") & " -pw " & Range("A14") & " " & """uptime""" & " > c:\DailyHC\" & Range("A1") & "\" & ActiveSheet.Cells(oCell.Row + 7, oCell.Column).Value & "\" & ActiveSheet.Cells(oCell.Row - 2, oCell.Column).Value & "_" & Format(Now, "MM-DD-YYYY_hh-mm-ss") & "_uptime.txt&&exit")
                        If Weekday(Date$) = 2 Then
                            Shell ("C:\Windows\System32\cmd.exe /k cd c:\&&plink" & " " & ActiveSheet.Cells(oCell.Row + 8, oCell.Column).Value & " -l " & Range("A12") & " -pw " & Range("A14") & " " & """fabstatsclear""" & " > c:\DailyHC\" & Range("A1") & "\" & ActiveSheet.Cells(oCell.Row + 7, oCell.Column).Value & "\" & ActiveSheet.Cells(oCell.Row - 2, oCell.Column).Value & "_" & Format(Now, "MM-DD-YYYY_hh-mm-ss") & "_fabstatsclear.txt&&exit")
                        End If

                    Shell ("C:\Windows\System32\cmd.exe /k cd c:\&&plink" & " " & ActiveSheet.Cells(oCell.Row + 8, oCell.Column).Value & " -l " & Range("A12") & " -pw " & Range("A14") & " " & """errdump""" & " > c:\DailyHC\" & Range("A1") & "\" & ActiveSheet.Cells(oCell.Row + 7, oCell.Column).Value & "\" & ActiveSheet.Cells(oCell.Row - 2, oCell.Column).Value & "_" & Format(Now, "MM-DD-YYYY_hh-mm-ss") & "_errdump.txt&&exit")
                   
                    Call ClearClipboard
                    retval = wsh.Run("C:\Windows\System32\cmd.exe /k c:&&cd c:\&&plink" & " " & ActiveSheet.Cells(oCell.Row + 8, oCell.Column).Value & " -l " & Range("A12") & " -pw " & Range("A14") & " " & """version | grep OS""" & " | clip&&exit", windowStyle, waitOnReturn)
                    ActiveSheet.Cells(oCell.Row + 12, oCell.Column).Activate
                    ActiveSheet.Paste
                                               'Lots and lots of other ElseIf statements...
            End If
        Next
        count = count + 1
        Loop Until (count = 4)
    End Sub

    Thursday, June 27, 2013 2:56 AM
  • After working on this some more, I think I have the answer.

    I've tested this many times and it seems to work on several different devices. :)

    Sub RunAllvariouscommands()
                                'This sets up a wait on return value before moving on. Used in clipboard paste.
    Dim retval
    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 0
                                'This sets up a loop to run the macros using a starting row.
        Dim oRows(4) As Integer
            oRows(0) = 4
            oRows(1) = 21
            oRows(2) = 38
            oRows(3) = 55
            oRows(4) = 72
            Dim count As Integer
            count = 0
        Dim oCell As Range
        Do
            For Each oCell In ActiveSheet.UsedRange.Rows(oRows(count)).Cells
                If ActiveSheet.Cells(oCell.Row + 7, oCell.Column).Value = "Brocade" Then
                    Shell ("C:\Windows\System32\cmd.exe /k cd c:\&&plink" & " " & ActiveSheet.Cells(oCell.Row + 8, oCell.Column).Value & " -l " & Range("A12") & " -pw " & Range("A14") & " " & """uptime""" & " > c:\DailyHC\" & Range("A1") & "\" & ActiveSheet.Cells(oCell.Row + 7, oCell.Column).Value & "\" & ActiveSheet.Cells(oCell.Row - 2, oCell.Column).Value & "_" & Format(Now, "MM-DD-YYYY_hh-mm-ss") & "_uptime.txt&&exit")
                        If Weekday(Date$) = 2 Then
                            Shell ("C:\Windows\System32\cmd.exe /k cd c:\&&plink" & " " & ActiveSheet.Cells(oCell.Row + 8, oCell.Column).Value & " -l " & Range("A12") & " -pw " & Range("A14") & " " & """fabstatsclear""" & " > c:\DailyHC\" & Range("A1") & "\" & ActiveSheet.Cells(oCell.Row + 7, oCell.Column).Value & "\" & ActiveSheet.Cells(oCell.Row - 2, oCell.Column).Value & "_" & Format(Now, "MM-DD-YYYY_hh-mm-ss") & "_fabstatsclear.txt&&exit")
                        End If

                    Shell ("C:\Windows\System32\cmd.exe /k cd c:\&&plink" & " " & ActiveSheet.Cells(oCell.Row + 8, oCell.Column).Value & " -l " & Range("A12") & " -pw " & Range("A14") & " " & """errdump""" & " > c:\DailyHC\" & Range("A1") & "\" & ActiveSheet.Cells(oCell.Row + 7, oCell.Column).Value & "\" & ActiveSheet.Cells(oCell.Row - 2, oCell.Column).Value & "_" & Format(Now, "MM-DD-YYYY_hh-mm-ss") & "_errdump.txt&&exit")
                   
                    Call ClearClipboard
                    retval = wsh.Run("C:\Windows\System32\cmd.exe /k c:&&cd c:\&&plink" & " " & ActiveSheet.Cells(oCell.Row + 8, oCell.Column).Value & " -l " & Range("A12") & " -pw " & Range("A14") & " " & """version | grep OS""" & " | clip&&exit", windowStyle, waitOnReturn)
                    ActiveSheet.Cells(oCell.Row + 12, oCell.Column).Activate
                    ActiveSheet.Paste
                                               'Lots and lots of other ElseIf statements...
            End If
        Next
        count = count + 1
        Loop Until (count = 4)
    End Sub



    Monday, January 13, 2014 7:24 AM
  • Hi rkussenterprise,Can you please help me in giving the format of excel sheet you have used here to input the values.because i unable to figure out the values for oRows,oCell.I am writing a similar script. this would help me a lot.Thank you.. Srikanth
    Monday, January 13, 2014 7:24 AM