Skip to Content

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.


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.

Enjoy it.

Cheers
Stefan

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply