Excel VBA - Get File via SSH

A customer had daily data-files on a Unix based system and he looked for a simple solution to visualize this data.

A simple solution for this was an excel template, created statically out of the downloded (winscp) data from
this unix application and showing that in a line diagram. The problem here was, that we need SSH in VBA,
which you could buy as extension or using other complicated stuff, but if you have putty installed, there is a
simple solution via plist and pscp and the sehll() command to get the data.

Below is the code to get the newest file from a defined directory on a Unix system via VBA and load it into
your excel to show the results in a prepared diagram.

Main part is the code template which is modified by your parameters "host", "user" and "pwd" put into a
separate sheet and executed via thew Shell() command.

Code

Sub

GetSSHFileList()

Dim

cmd

As

String

Dim

tmp

As

String

Dim

cmdFilepath

As

String

Dim

host$, user$, pwd$

Dim

cmdFilepath

As

String

Dim

ResultPath$, list$, remoteFile$

host = Range(

"Parameter!B1"

).Text

user = Range(

"Parameter!B2"

).Text

pwd = Range(

"Parameter!B3"

).Text

' Create the command which should be executed with plink to execute remotely via ssh a unix command

' and redirect the result into a file to be usabel here in Excel

tmp = Environ(

"TEMP"

)

cmd =

"C:\tools\net\putty\plink.exe %USER%@%HOST% -pw %PWD% ls -l /tmp/*.dat>"

+ tmp +

"\filelist.txt"

cmd = Replace(Replace(Replace(cmd,

"%USER%"

, user),

"%HOST%"

, host),

"%PWD%"

, pwd)

cmdFilepath = tmp +

"\list.cmd"

' Create the command file to be executed

Open cmdFilepath

For

Output

As

#1

Write #1, cmd

Close #1

' Execute the ssh command

Call

Shell(cmdFilepath, vbNormalFocus)

Application

.Wait(1)

' Read the data coming from the former executed command (stdout has been redirected into <ResultPath>

ResultPath = tmp +

"\filelist.txt"

Open ResultPath

For

Input

As

#1

Input #1, list

Close #1

' Get the newest file

vArray = Split(list, Chr(10))

count = UBound(vArray)

If

count > 0

Then

remoteFile= vArray(count - 1)

' Last entry from the ls output is empty

vArray = Split(lastfile,

"/"

)

' Get the filename

vArray(0) =

""

' remove the rest of the ls command

remoteFile= Join(v,

"/"

)

' Create the pure filename

End

If

' Create the command to transfer the unix file via pscp to the local machine

localPath = tmp +

"\data.txt"

' local filename

cmd =

"C:\tools\net\putty\pscp.exe -pw %PWD% %USER%@%HOST%:"

+ remoteFile +

" "

+ localPath

cmd = Replace(Replace(Replace(cmd,

"%USER%"

, user),

"%HOST%"

, host),

"%PWD%"

, pwd)

Call

Shell(cmd, vbNormalFocus)

Application

.Wait(5)

' Give it a bit time for transport

Call

LoadData

' Load the data into a defined tab

End

Sub

' ===========================================================

' Load the data into a tab which is used as source for your diagram

'

' This should be created via RecordMacro To refelct your data

' ===========================================================

Sub

LoadData()

' Select your sheet

Sheets(

"Data"

).Select

' remove all datasources

For

Each

e

In

ActiveWorkbook.Connections

e.Delete

Next

' Select the data columns and delete everything

Columns(

"A:I"

).Select

Selection.ClearContents

' Select the first cell

Range(

"A1"

).Select

' Load the data into the sheet

With

ActiveSheet.QueryTables.Add(Connection:=

"TEXT;C:\temp\temp\data.txt"

,

Destination:=Range(

"$A$1"

))

.Name =

"data"

.FieldNames =

True

.RowNumbers =

False

.FillAdjacentFormulas =

False

.PreserveFormatting =

True

.RefreshOnFileOpen =

False

.RefreshStyle = xlInsertDeleteCells

.SavePassword =

False

.SaveData =

True

.AdjustColumnWidth =

True

.RefreshPeriod = 0

.TextFilePromptOnRefresh =

False

.TextFilePlatform = 850

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter =

False

.TextFileTabDelimiter =

True

.TextFileSemicolonDelimiter =

False

.TextFileCommaDelimiter =

False

.TextFileSpaceDelimiter =

False

.TextFileColumnDataTypes =

Array

(1, 1, 1, 1, 1, 1, 1, 1, 1)

.TextFileDecimalSeparator =

"."

.TextFileThousandsSeparator =

","

.TextFileTrailingMinusNumbers =

True

.Refresh BackgroundQuery:=

False

End

With

Call

RefreshDiagramRows(MaxRows())

' Set the diagram data to the new data boundaries

End

Sub

' ===========================================================

' This function refreshes all data in the diagram depending

' On the New loaded data

' ===========================================================

Sub

RefreshDiagramRows(maxRow)

Dim

vArray, vvArray

Sheets(

"Diagramm"

).Select

ActiveSheet.ChartObjects(

"Diagramm 1"

).Activate

ActiveChart.PlotArea.Select

For

Each

Series

In

ActiveChart.SeriesCollection

vArray = Split(Series.Formula,

","

)

For

i = 1

To

2

vvArray = Split(vArray(i),

"$"

)

vvArray(4) = maxRow

vArray(i) = Join(vvArray,

"$"

)

Next

Series.Formula = Join(vArray,

","

)

Next

End

Sub

' ===========================================================

' Get the amount of loaded rows

' ===========================================================

Function

MaxRows()

As

Integer

Dim

vArray

Range(

"A1"

).Select

Selection.End(xlDown).Select

vArray = Split(ActiveCell.Address,

"$"

)

MaxRows = vArray(UBound(vArray))

End

Function


Submitted by	Category	Review Cycle	.	Public
Joachim Mutter/sysarc on 04/21/2020 at 02:37 PM	Excel