ROACH部落落

之前在Excel看到一些SAP控制項可以用!
我就在想該不會Excel可以透過巨集來讀SAP吧
這幾天放假在家找的一堆資料才發現
還真的有

且非常簡單


Private Sub CommandButton1_Click()
Dim Functions As Object
Dim func As Object
Dim iTable As Object

'首先要先連線SAP打上用戶端語言帳號密碼跟連線IP

Set oFunction = CreateObject("SAP.LogonControl.1")
Set oConnection = oFunction.NewConnection
oConnection.client = "060"                            'Set the Client
oConnection.Language = "zh"
oConnection.user = user.Text
oConnection.Password = psw.Text
oConnection.ApplicationServer = "192.168.102.2"                 'Set the Server address
oConnection.SystemNumber = "00"
result = oConnection.Logon(0, True)
Set ofun = CreateObject("SAP.FUNCTIONS")
Set ofun.Connection = oConnection


'看要讀取SAP中哪一個Function
Set func = ofun.Add("ENQUEUE_READ")

'==對該function下的傳遞參數
func.Exports("GCLIENT") = "060"
func.Exports("GUNAME") = ""
func.Exports("LOCAL") = "0"


'==設定要傳回的table名稱
Set iTable = func.Tables("ENQ")

If func.Call = True Then

Set iTable = func.Tables("ENQ")

'==傳回得到的資料
 get_data iTable

MsgBox "Get Data OK!"

Else

MsgBox " Call Failed! error: " '+ func.Exception

End If
End Sub




Public Sub get_data(itabtable As Object)

Dim vField As Variant
Static j As Integer

For j = 1 To itabtable.RowCount
ThisWorkbook.Sheets(1).Cells(j, 4) = itabtable.Value(j, "GNAME")  '讀取的欄位資料寫回Excel
ThisWorkbook.Sheets(1).Cells(j, 5) = itabtable.Value(j, "GUSR")
ThisWorkbook.Sheets(1).Cells(j, 6) = itabtable.Value(j, "GUSRVB")
Next
End Sub



這邊還有很多參考的代碼

http://www.cnblogs.com/elegantok/archive/2009/03/13/1410364.html

http://www.itpub.net/thread-1016467-1-1.html


http://www.blueshop.com.tw/board/show.asp?subcde=BRD2005111715130905N&fumcde=FUM200501271723350KG&rplcnt=2