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


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 = "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


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 = "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


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 = "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


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: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


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.

Enjoy it.

Cheers
Stefan

New NetWeaver Information at SAP.com

Very Helpfull

User Rating: Be the first one !