Hello community,

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.

How To Use dotNET Connector NCo 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 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 = "NCox64" } Else { $Path = "NCox86" } [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.

How To Use dotNET Connector NCo 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 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 = "NCox64" } Else { $Path = "NCox86" } [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-------------------------------------------------------------------

How To Use dotNET Connector NCo 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 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 = "NCox64" } Else { $Path = "NCox86" } [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:

How To Use dotNET Connector NCo Inside VBA'-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:DummyPSTest.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.

How To Use dotNET Connector NCo Inside VBA