Technical Articles
How To Use dotNET Connector NCo Inside VBA
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.
Be the first to leave a comment
You must be Logged on to comment or reply to a post.