Excel宏读取SAP数据 - 下载本文

执行(Ctrl+F8),生成WSDL(Web Services Description Language)(Ctrl+F9) 点击生成WSDL文件路径 点击按钮后会生成一个网页,需要记住其网址: 服务监控: T-Code:smicm 服务监控 在Excel中导入生成的WSDL文件,引入对应的WS服务.(此功能要求Excel版本较高,如果是2003及其以下版本则需要安装补丁) 此处填入WSDL路径,完成导入WS服务. 在excel中编写一个宏来调用此WS服务读取数据 Sub sap_con()

Dim Z_EXCEL_WS_T2_ws As New clsws_ZEXCELWST2V2Service Dim ZCLASS As String Dim name As String Dim i As Integer

Dim sheetname As String

For i = 0 To 10

name = sheet1.Cells(i + 1, 1).Value If name = \

Else

Call Z_EXCEL_WS_T2_ws.wsm_ZExcelWsT2(name, ZCLASS)

sheet1.Cells(i + 1, 2) = \所在的用户组是\ sheet1.Cells(i + 1, 3) = ZCLASS ' sheet1.Cells(i + 1, 5) = name

End If

Next i

MsgBox \用户信息读取成功\ '''' '''' ''''

End Sub

方法二:

直接使用VBA代码写宏读取信息,不用做任何设置, 缺点是安全性较差,因为登陆用户名密码需要在VBA中指定. 代码如下: Sub sap_table()

Dim iqtable As Object Dim ifields As Object Dim idata As Object Dim itabname As String Dim ifldname As String ' Macro1 Macro

' Macro recorded 9/7/2005 by StoneF

Set oFunction = CreateObject(\Set oConnection = oFunction.NewConnection oConnection.Client = \oConnection.Language = \

oConnection.User = \“ 用户名 密码写入代码中,安全性较差 oConnection.Password = \“ oConnection.ApplicationServer = \oConnection.SystemNumber = \result = oConnection.Logon(0, True)

Set ofun = CreateObject(\Set ofun.Connection = oConnection

Set func = ofun.Add(\

func.Exports(\Set ioptions = func.tables.Item(\Set ifields = func.tables.Item(\Set idata = func.tables.Item(\

'here only add 3 fields ifields.Rows.Add

ifields.Value(1, \ifields.Rows.Add

ifields.Value(2, \ifields.Rows.Add

ifields.Value(3, \

'icall = func.RFC_READ_TABLE(QUERY_TABLE:=\= ioptions)

icall = func.Call

If icall = True Then 'icall=true For i = 1 To idata.Rows.Count ILine = idata.Value(i, \ imatno = Mid(ILine, 1, 4) imtype = Mid(ILine, 5, 16) imgrp = Mid(ILine, 17, 50)

ThisWorkbook.Sheets(1).Cells(i, 1) = imatno ThisWorkbook.Sheets(1).Cells(i, 2) = imtype ThisWorkbook.Sheets(1).Cells(i, 3) = imgrp Next i

MsgBox \表T001T读取成功\Else

MsgBox \End If

End Sub