Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
stefan_schnell
Active Contributor
2,966
In a normal case it isn't possible to use NCo inside VBA, because NCo isn't COM visible. Here now a solution how to do it nevertheless. You can use NCo in VBA via a COM bridge to PowerShell. I presented here the possibility to use PowerShell via this COM bridge with other COM enabled languages, in my example VBScript, but in this case VBA.




Simple example

At first a simple example to show how easy it is to use PowerShell inside VBA:
'-Begin-----------------------------------------------------------------

'-Directives----------------------------------------------------------
Option Explicit

'-Constants-----------------------------------------------------------
Const OUTPUT_CONSOLE = 0
Const OUTPUT_WINDOW = 1
Const OUTPUT_BUFFER = 2

'-Sub PowerShell------------------------------------------------------
Sub PowerShell()

'-Variables-------------------------------------------------------
Dim PS As ActiveXPoshV3.IActiveXPoSH
Dim Line As Variant

Set PS = CreateObject("SAPIEN.ActiveXPoSHV3")
If Not IsObject(PS) Then
Exit Sub
End If

If PS.Init(False) <> 0 Then
Exit Sub
End If

If Not PS.IsPowerShellInstalled Then
Exit Sub
End If

PS.OutputWidth = 132
PS.OutputMode = OUTPUT_BUFFER

PS.Execute "Get-Host;"

For Each Line In PS.Output
Debug.Print Line
Next

PS.ClearOutput

End Sub

'-End-------------------------------------------------------------------

In the sub PowerShell I create at first the object, check it and the existence of PowerShell. If it is all okay I set the output and execute a PowerShell command. In the next step I print the result to the immediate window.






Get NCo Version

In the second example I detect the NCo version. At first the VBA code:
'-Begin-----------------------------------------------------------------

'-Directives----------------------------------------------------------
Option Explicit

'-Constants-----------------------------------------------------------
Const OUTPUT_CONSOLE = 0
Const OUTPUT_WINDOW = 1
Const OUTPUT_BUFFER = 2

'-Sub PowerShell------------------------------------------------------
Sub PowerShell()

'-Variables-------------------------------------------------------
Dim PS As ActiveXPoshV3.IActiveXPoSH
Dim FileName As String
Dim PSLine As String
Dim PSProg As String
Dim Line As Variant
Dim User As String
Dim Password As String

Set PS = CreateObject("SAPIEN.ActiveXPoSHV3")
If Not IsObject(PS) Then
Exit Sub
End If

If PS.Init(False) <> 0 Then
Exit Sub
End If

If Not PS.IsPowerShellInstalled Then
Exit Sub
End If

PS.OutputWidth = 132
PS.OutputMode = OUTPUT_BUFFER

FileName = "001_GetVersion.ps1"
Open FileName For Input As #1
Do While Not EOF(1)
Line Input #1, PSLine
PSProg = PSProg & PSLine & vbCrLf
Loop
Close #1

PS.Execute PSProg

For Each Line In PS.Output
Debug.Print Line
Next

PS.ClearOutput

End Sub

'-End-------------------------------------------------------------------

The only difference to the example above is that I read the following file with the PowerShell commands.
#-Begin-----------------------------------------------------------------

#-Sub Load-NCo--------------------------------------------------------
Function Load-NCo () {

If ([Environment]::Is64BitProcess) {
$Path = "NCo\x64\"
}
Else {
$Path = "NCo\x86\"
}

[Reflection.Assembly]::LoadFile($Path + "sapnco.dll") > $Null
[Reflection.Assembly]::LoadFile($Path + "sapnco_utils.dll") > $Null

}

#-Sub Get-NCoVersion--------------------------------------------------
Function Get-NCoVersion () {

#-Version des NCo anzeigen----------------------------------------
$Version =
[SAP.Middleware.Connector.SAPConnectorInfo]::get_Version()
$PatchLevel =
[SAP.Middleware.Connector.SAPConnectorInfo]::get_KernelPatchLevel()
$KernelRelease =
[SAP.Middleware.Connector.SAPConnectorInfo]::get_KernelRelease()
$SAPRelease =
[SAP.Middleware.Connector.SAPConnectorInfo]::get_SAPRelease()

Write-Host "`r`nNCo verion:" $Version
Write-Host "Patch Level:" $PatchLevel
Write-Host "SAP Release:" $SAPRelease
Write-Host "Kernel Release:" $KernelRelease

}

#-Sub Main------------------------------------------------------------
Function Main () {
If ($PSVersionTable.PSVersion.Major -ge 5) {
Load-NCo
Get-NCoVersion
}
}

#-Main----------------------------------------------------------------
Main

#-End-------------------------------------------------------------------

As you can see it loads the NCo library and gets the version of the library.






Invoke Functions

In my last example I will show how to invoke functions from VBA via NCo. At first the VBA code:
'-Begin-----------------------------------------------------------------

'-Directives----------------------------------------------------------
Option Explicit

'-Constants-----------------------------------------------------------
Const OUTPUT_CONSOLE = 0
Const OUTPUT_WINDOW = 1
Const OUTPUT_BUFFER = 2

'-Sub PowerShell------------------------------------------------------
Sub PowerShell()

'-Variables-------------------------------------------------------
Dim PS As ActiveXPoshV3.IActiveXPoSH
Dim FileName As String
Dim PSLine As String
Dim PSProg As String
Dim Line As Variant
Dim User As String
Dim Password As String

Set PS = CreateObject("SAPIEN.ActiveXPoSHV3")
If Not IsObject(PS) Then
Exit Sub
End If

If PS.Init(False) <> 0 Then
Exit Sub
End If

If Not PS.IsPowerShellInstalled Then
Exit Sub
End If

PS.OutputWidth = 132
PS.OutputMode = OUTPUT_BUFFER

FileName = "002_InovkeFunction.ps1"
Open FileName For Input As #1
Do While Not EOF(1)
Line Input #1, PSLine
Select Case Trim(PSLine)
Case "#USER"
User = InputBox("User:")
PSLine = "$cfgParams.Add(""USER"", """ & User & """)"
Case "#PASSWORD"
Password = InputBox("Password:")
PSLine = "$cfgParams.Add(""PASSWD"", """ & Password & """)"
End Select
PSProg = PSProg & PSLine & vbCrLf
Loop
Close #1

PS.Execute PSProg

For Each Line In PS.Output
Debug.Print Line
Next

PS.ClearOutput

End Sub

'-End-------------------------------------------------------------------

Here I use a tiny trick. I replace some special marked lines in the PowerShell code with variables, in this case the user name and the password. It is not a good idea to use an input box but for our example it is okay.
#-Begin-----------------------------------------------------------------

#-Sub Load-NCo--------------------------------------------------------
Function Load-NCo {

If ([Environment]::Is64BitProcess) {
$Path = "NCo\x64\"
}
Else {
$Path = "NCo\x86\"
}

[Reflection.Assembly]::LoadFile($Path + "sapnco.dll") > $Null
[Reflection.Assembly]::LoadFile($Path + "sapnco_utils.dll") > $Null

}

#-Function Get-Destination--------------------------------------------
Function Get-Destination {

#-Verbindungsparamter---------------------------------------------
$cfgParams = New-Object SAP.Middleware.Connector.RfcConfigParameters
$cfgParams.Add("NAME", "TEST")
$cfgParams.Add("ASHOST", "NSP")
$cfgParams.Add("SYSNR", "00")
$cfgParams.Add("CLIENT", "001")
#USER
#PASSWORD

Return [SAP.Middleware.Connector.RfcDestinationManager]::GetDestination($cfgParams)

}

#-Sub Invoke-SAPFunctionModule----------------------------------------
Function Invoke-SAPFunctionModule {

$destination = Get-Destination

#-Metadaten-------------------------------------------------------
[SAP.Middleware.Connector.IRfcFunction]$rfcFunction =
$destination.Repository.CreateFunction("STFC_CONNECTION")

#-Importparameter setzen------------------------------------------
$rfcFunction.SetValue("REQUTEXT", "Hello World from PowerShell")

#-Funktionsbaustein aufrufen--------------------------------------
$rfcFunction.Invoke($destination)

#-Exportparameter anzeigen----------------------------------------
Write-Host $rfcFunction.GetValue("ECHOTEXT")
Write-Host $rfcFunction.GetValue("RESPTEXT")

}

#-Sub Main------------------------------------------------------------
Function Main () {
If ($PSVersionTable.PSVersion.Major -ge 5) {
Load-NCo
Invoke-SAPFunctionModule
}
}

#-Main----------------------------------------------------------------
Main

#-End-------------------------------------------------------------------






Get the content of a table

Here an example how to read a table via RFC_READ_TABLE and set the content in an Excel Sheet.
'-Begin-----------------------------------------------------------------

'-Directives----------------------------------------------------------
Option Explicit

'-Constants-----------------------------------------------------------
Const OUTPUT_CONSOLE = 0
Const OUTPUT_WINDOW = 1
Const OUTPUT_BUFFER = 2

'-Sub PowerShell------------------------------------------------------
Sub PowerShell()

'-Variables-------------------------------------------------------
Dim PS As ActiveXPoshV3.IActiveXPoSH
Dim FileName As String
Dim PSLine As String
Dim PSProg As String
Dim Line As Variant
Dim User As String
Dim Password As String
Dim TableName As String
Dim Result() As String
Dim Fields() As String
Dim Field As Variant
Dim row As Long
Dim col As Long
Dim Wb As Excel.Workbook
Dim Ws As Excel.Worksheet

Set PS = CreateObject("SAPIEN.ActiveXPoSHV3")
If Not IsObject(PS) Then
Exit Sub
End If

If PS.Init(False) <> 0 Then
Exit Sub
End If

If Not PS.IsPowerShellInstalled Then
Exit Sub
End If

PS.OutputWidth = 132
PS.OutputMode = OUTPUT_BUFFER

FileName = "007_RfcReadTable.ps1"
Open FileName For Input As #1
Do While Not EOF(1)
Line Input #1, PSLine
Select Case Trim(PSLine)
Case "#USER"
User = InputBox("User:")
PSLine = "$cfgParams.Add(""USER"", """ & User & """)"
Case "#PASSWORD"
Password = InputBox("Password:")
PSLine = "$cfgParams.Add(""PASSWD"", """ & Password & """)"
Case "#READTABLE"
TableName = InputBox("Tablename:", "", "SFLIGHT")
PSLine = "Read-Table(""" & TableName & """)"
End Select
PSProg = PSProg & PSLine & vbCrLf
Loop
Close #1

PS.Execute PSProg

'-Write result into sheet-----------------------------------------
Set Wb = ActiveWorkbook
Set Ws = Wb.ActiveSheet

Result = Split(PS.OutputString, vbCrLf)
row = 1
For Each Line In Result
Fields = Split(Line, "~")
col = 1
For Each Field In Fields
Ws.Cells(row, col) = Field
col = col + 1
Next
row = row + 1
Next

PS.ClearOutput

End Sub

'-End-------------------------------------------------------------------

Here now the PowerShell code:
#-Begin-----------------------------------------------------------------

#-Sub Load-NCo--------------------------------------------------------
Function Load-NCo {

If ([Environment]::Is64BitProcess) {
$Path = "NCo\x64\"
}
Else {
$Path = "NCo\x86\"
}

[Reflection.Assembly]::LoadFile($Path + "sapnco.dll") > $Null
[Reflection.Assembly]::LoadFile($Path + "sapnco_utils.dll") > $Null

}

#-Function Get-Destination--------------------------------------------
Function Get-Destination {

#-Connection parameters-------------------------------------------
$cfgParams = New-Object SAP.Middleware.Connector.RfcConfigParameters
$cfgParams.Add("NAME", "TEST")
$cfgParams.Add("ASHOST", "NSP")
$cfgParams.Add("SYSNR", "00")
$cfgParams.Add("CLIENT", "001")
#USER
#PASSWORD

Return [SAP.Middleware.Connector.RfcDestinationManager]::GetDestination($cfgParams)

}

#-Sub Read-Table------------------------------------------------------
Function Read-Table ([String]$TableName) {

$destination = Get-Destination

#-Metadata--------------------------------------------------------
[SAP.Middleware.Connector.IRfcFunction]$rfcFunction = `
$destination.Repository.CreateFunction("RFC_READ_TABLE")

#-Set import parameter--------------------------------------------
$rfcFunction.SetValue("QUERY_TABLE", $TableName)
$rfcFunction.SetValue("DELIMITER", "~")

#-Call function module--------------------------------------------
Try {
$rfcFunction.Invoke($destination)

[SAP.Middleware.Connector.IRfcTable]$Table = `
$rfcFunction.GetTable("FIELDS")

#-Get column names--------------------------------------------
ForEach ($Row in $Table) {
$ColumnNames = $ColumnNames + $Row.GetValue("FIELDNAME") + "~"
}
Write-Host $ColumnNames

[SAP.Middleware.Connector.IRfcTable]$Table = `
$rfcFunction.GetTable("DATA")

#-Get table data----------------------------------------------
ForEach ($Row in $Table) {
Write-Host $Row.GetValue("WA")
}

}
Catch {
Write-Host "Exception" $_.Exception.Message "occured"
}

}

#-Sub Main------------------------------------------------------------
Function Main () {
If ($PSVersionTable.PSVersion.Major -ge 5) {
Load-NCo
#READTABLE
}
}

#-Main----------------------------------------------------------------
Main

#-End-------------------------------------------------------------------

And last but not least here the result:






VBA and UTF-8 Coded Source Files

More and more are UTF-8 encoded files the standard in development environments. With the approach I use here it is not possible to read this kind of files correct, because the Byte Order Mark (BOM) are read as characters. Here a function UTF8Import to read this type of files correct.
'-Begin-----------------------------------------------------------------

'-Directives----------------------------------------------------------
Option Explicit

'-Constants-----------------------------------------------------------
Const OUTPUT_CONSOLE = 0
Const OUTPUT_WINDOW = 1
Const OUTPUT_BUFFER = 2

'-Function UTF8Import-------------------------------------------------
Function UTF8Import(ByVal FileName As String) As String

'-Variables---------------------------------------------------------
Dim oADOStream As Object

Set oADOStream = CreateObject("ADODB.Stream")
With oADOStream
.Charset = "UTF-8"
.Open
.LoadFromFile FileName
UTF8Import = .ReadText(-1)
.Close
End With

End Function

'-Sub PowerShell------------------------------------------------------
Sub PowerShell()

'-Variables---------------------------------------------------------
Dim PS As ActiveXPoshV3.IActiveXPoSH
Dim FileName As String
Dim PSLine As String
Dim PSProg As String
Dim Line As Variant
Dim User As String
Dim Password As String

Set PS = CreateObject("SAPIEN.ActiveXPoSHV3")
If Not IsObject(PS) Then
Exit Sub
End If

If PS.Init(False) <> 0 Then
Exit Sub
End If

If Not PS.IsPowerShellInstalled Then
Exit Sub
End If

PS.OutputWidth = 132
PS.OutputMode = OUTPUT_BUFFER

FileName = "C:\Dummy\PSTest.ps1"
PSProg = UTF8Import(FileName)

PS.Execute PSProg

For Each Line In PS.Output
Debug.Print Line
Next

PS.ClearOutput

End Sub

'-End-------------------------------------------------------------------





Hint

Don't forget to reference to the type library in VBA.






Conclusion

As you can see is it with this tiny indirect way very easy possible to use NCo with VBA. Also you open the gate wide to much more possibilities, e.g. to use C# or VB.net etc. in VBA. You can develop your application inside ISE and use it nearly seamless in VBA. NCo and the SAPIEN module offers x86 and x64 versions, so you can use x86 and x64 Office VBA with the same code.
Labels in this area