Run Unix Command via VBA and return the results in Excel Spreadsheet

Question

I have this Unix command

cat /dataops/profits/name.csv

Can I save it to a .txt file and run via VBA Macro, and then automatically return the results into my Excel spreadsheet?

Answer 1

The hardest part is getting the file from the Unix box over to your Windows computer. You have a few different approaches to this:

1) Here's a purely VBA approach. You'll need to download the free command-line version of Putty called plink from here. In this approach you're streaming the data on your command-line and transferring the data into a local text file.

cmd = "C:\MyUtilities\plink.exe -pw PASSWORD USERNAME@IPADDRESS cat /dataops/profits/name.csv"

Set datafile = CreateObject("Scripting.FileSystemObject").CreateTextFile("c:\name.csv", True)

Set WshShell = CreateObject("WScript.Shell")
Set oExec = WshShell.Exec(cmd)
Do While oExec.Status = 0
   If Not oExec.StdOut.AtEndOfStream Then
      datafile.WriteLine oExec.StdOut.Readall
   End If
Loop
datafile.Close

2) Another approach would be to run a Windows scheduled task batch file that uses FTP to transfer the file over, as explained here. Personally I think this will be your best bet, because the VBA code can be focused on just loading up the file, rather than connecting to Unix and getting the file too.

3) If you have a Samba share set up on your Unix server for a directory, then you'll be able to see that directory from Windows and can just go there to get the file.

Once you have the file on your Windows computer, you can open Excel and load the file like this:

Set xl = CreateObject("Excel.Application")
xl.Visible = True
Set wb = xl.Workbooks.Open("c:\name.csv")