Extract data from SAP to Excel?

  • Thread starter Thread starter Gunner
  • Start date Start date
G

Gunner

Hi,

Have anyone any recommended sites for this?
I were thinking of using SQL and not send keys in VBA?

However, any tips for this is highly appreciated!

Sincerely,

Gunner
 
Hi
SQL won't help you as it is not recommended to access the SAP tables
directly with SQL!
So several ways to tackle this (though you might better ask this type
of questions in a SAP user group). One way:
Create a SAP function module which gathers your data:
- This should be accessible by VBA (SAP provides sample code for
accessing this with VB, C, C++)

So again: Don't use SQL on SAP's internal data tables :-)
 
This example will strip out Job status information from SAP and email it to you in .csv format using an exchange profile

It will need some customisation for your SAP system, but can be used to retrieve any information you like

Thanks
Will

'-------------------------------------
' VBScript example for SAP Extrac
'-------------------------------------

SID = InputBox("Please Enter a System ID AND CLIENT EG TSA200"

Username = InputBox("Please Enter a Username"

Password = InputBox("Please Enter a Password"

JobNumber = Inputbox("Job number to search for?"

Set LogonControl = CreateObject("SAP.LogonControl.1"

Set conn = LogonControl.NewConnectio

conn.user = Username

conn.password = passwor

conn.System = "00

Conn.language = "EN

conn.Client = Right(SID,3

Select CASE left(SID,3
CASE "TSA
conn.ApplicationServer = "NTQA1"
'conn.host = "NTQA1
CASE "DEA
conn.ApplicationServer = "ntdev1"
'conn.host = "ntdev1
CASE "PRA
conn.ApplicationServer = "ntpr3
'conn.host = "ntpr3"
End Selec

conn.System = Left(SID,3

conn.SystemNumber = "00" ' System (database) ID of the instanc

retcd = conn.Logon(0,False) ' 0=new instance; false=do not suppress dialo

If retcd <> True The

Msgbox ("Error logging on to SAP"

Els

D

Set funcControl = CreateObject("SAP.Functions")

screenlogthis ("Searching for Job Number: " & jobnumber

Set funccontrol.connection = Con
Set RFC_READ_TABLE = funcControl.Add("RFC_READ_TABLE"

Set DELIMITER = RFC_READ_TABLE.Exports("DELIMITER"
DELIMITER.Value = "|

Set QUERY_TABLE = RFC_READ_TABLE.Exports("QUERY_TABLE"
QUERY_TABLE.Value = "TBTCO

Set OPTIONS = RFC_READ_TABLE.Tables("OPTIONS"
Set FIELDS = RFC_READ_TABLE.Tables("FIELDS"

OPTIONS.Rows.Ad
OPTIONS.Value(1, "TEXT") = "STATUS EQ 'F' AND JOBCOUNT EQ '" & JobNumber & "'

FIELDS.Rows.Ad
FIELDS.Value(1, "FIELDNAME") = "JOBNAME

If RFC_READ_TABLE.Call = True Then

Set DATA = RFC_READ_TABLE.Tables("DATA"

If data.rowcount > 0 Then

Msgbox JobNumber & "Job has completed successfully

Exit D

End If

Else

MSGBOX RFC_READ_TABLE.EXCEPTION

Exit Do

End I

WScript.Sleep 1000

Set RFC_READ_TABLE = Nothin
Set FIELDS = Nothin
Set QUERY_TABLE = Nothin
Set DELIMITER = Nothin
Set OPTIONS = Nothin
Set DATA = Nothin
Set FUNCCONTROL = Nothin

Loop

End I



conn.Logof

screenLogthis "Logged off from R/3! "

Function screenLogThis(logWhat)

' ********************************************************************************
' **** Writes message to screen with time / datestamp ***
' *******************************************************************************

WScript.Echo (Now() & ": " & logWhat) ' Echo on Console Scree

End Functio
 
Back
Top