Excel to SAP Script for Cost Center KS01

[Former Member]

I have recorded an SAP Script to create a cost center via t-code KS01.  I have updated the script to read an excel spreadsheet.  I assigned variables the cells in my spreadsheet.  And i assigned the SAP fields to the variables.  But I seem to be missing something.  I have tried to run my script, and SAP does try to attach the script to the GUI, but then nothing happens after I click OK in the pop-up box.  Can someone please help?  I want to be able to use the Active worksheet that I have open.  Please help!!  This is my code: If Not IsObject(application) Then   Set SapGuiAuto  = GetObject("SAPGUI")   Set application = SapGuiAuto.GetScriptingEngine End If If Not IsObject(connection) Then   Set connection = application.Children(0) End If If Not IsObject(session) Then   Set session    = connection.Children(0) End If If IsObject(WScript) Then   WScript.ConnectObject session,    "on"   WScript.ConnectObject application, "on" End If Rem-------------------- Rem Set up connection to the Excel data to be keyed into SAP: Dim objExcel Dim objSheet, intRow, i Set objExcel = GetObject(,"Excel.Application") Set objSheet = objExcel.ActiveWorkbook.ActiveSheet Rem END Excel data Rem Set Up Excel File to Start Reading from Cell A2 For i = 100 to objSheet.UsedRange.Rows.Count Rem Place excel value into variable strCN 'variables CC = Trim(CStr(objSheet.Cells(i, 1).Value)) Name = Trim(CStr(objSheet.Cells(i, 2).Value)) Desc = Trim(CStr(objSheet.Cells(i, 3).Value)) Person = Trim(CStr(objSheet.Cells(i, 4).Value)) Barc = Trim(CStr(objSheet.Cells(i, 5).Value)) Category = Trim(CStr(objSheet.Cells(i, 6).Value)) Node = Trim(CStr(objSheet.Cells(i, 7).Value)) CoCode = Trim(CStr(objSheet.Cells(i, 8).Value)) ProfitCtr = Trim(CStr(objSheet.Cells(i, 9).Value)) CostSheet = Trim(CStr(objSheet.Cells(i, 10).Value)) Dept = Trim(CStr(objSheet.Cells(i, 11).Value)) DI = Trim(CStr(objSheet.Cells(i, 12).Value)) Rem-------------------- 'enter master data into SAP session.findById("wnd[0]").maximize session.findById("wnd[0]/tbar[0]/okcd").text = "/nks01" session.findById("wnd[0]").sendVKey 0 session.findById("wnd[0]/usr/ctxtCSKSZ-KOSTL").text = CC session.findById("wnd[0]/usr/ctxtCSKSZ-DATAB_ANFO").text = "01.01.1950" session.findById("wnd[0]/usr/ctxtCSKSZ-DATBI_ANFO").text = "31.12.9999" session.findById("wnd[0]/usr/ctxtCSKSZ-DATBI_ANFO").setFocus session.findById("wnd[0]/usr/ctxtCSKSZ-DATBI_ANFO").caretPosition = 10 session.findById("wnd[0]").sendVKey 0 session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/txtCSKSZ-KTEXT").text = Name session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/txtCSKSZ-LTEXT").text = Desc session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/txtCSKSZ-VERAK").text = Person session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/txtCSKSZ-ABTEI").text = Barc session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/ctxtCSKSZ-KOSAR").text = Category session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/ctxtCSKSZ-KHINR").text = Node session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/ctxtCSKSZ-BUKRS").text = CoCode session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/ctxtCSKSZ-PRCTR").text = ProfitCtr session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/ctxtCSKSZ-PRCTR").setFocus session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/ctxtCSKSZ-PRCTR").caretPosition = 6 session.findById("wnd[0]").sendVKey 0 session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpKZEI").select session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpKZEI/ssubSUBSCREEN_EINZEL:SAPLKMA1:0310/chkCSKSZ-MGEFL").selected = false session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpKZEI/ssubSUBSCREEN_EINZEL:SAPLKMA1:0310/chkCSKSZ-MGEFL").setFocus session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpTMPT").select session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpTMPT/ssubSUBSCREEN_EINZEL:SAPLKMA1:0350/ctxtCSKSZ-KALSM").text = CostSheet session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpTMPT/ssubSUBSCREEN_EINZEL:SAPLKMA1:0350/ctxtCSKSZ-KALSM").setFocus session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpTMPT/ssubSUBSCREEN_EINZEL:SAPLKMA1:0350/ctxtCSKSZ-KALSM").caretPosition = 5 session.findById("wnd[0]").sendVKey 0 session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpADRE").select session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpKOMM").select session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabp+CU1").select session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabp+CU1/ssubSUBSCREEN_EINZEL:SAPLKMA1:0399/ssubCUSTFLDS:SAPLXKM1:0999/txtCSKS-ZZDEPT").text = Dept session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabp+CU1/ssubSUBSCREEN_EINZEL:SAPLKMA1:0399/ssubCUSTFLDS:SAPLXKM1:0999/txtCSKS-ZZDEPT").caretPosition = 7 session.findById("wnd[0]").sendVKey 0 session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabp+CU1/ssubSUBSCREEN_EINZEL:SAPLKMA1:0399/ssubCUSTFLDS:SAPLXKM1:0999/ctxtCSKS-ZZID").text = DI session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabp+CU1/ssubSUBSCREEN_EINZEL:SAPLKMA1:0399/ssubCUSTFLDS:SAPLXKM1:0999/ctxtCSKS-ZZID").setFocus session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabp+CU1/ssubSUBSCREEN_EINZEL:SAPLKMA1:0399/ssubCUSTFLDS:SAPLXKM1:0999/ctxtCSKS-ZZID").caretPosition = 1 session.findById("wnd[0]").sendVKey 0 session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpKZEI").select session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN").select session.findById("wnd[0]/tbar[0]/btn[11]").press NEXT

[script_man · Accepted solution]

Hi Christina,

I think that your courage must be rewarded. You could try the following:

if Not IsObject(application) Then

   Set SapGuiAuto  = GetObject("SAPGUI")

   Set application = SapGuiAuto.GetScriptingEngine

End If

If Not IsObject(connection) Then

   Set connection = application.Children(0)

End If

If Not IsObject(session) Then

   Set session    = connection.Children(0)

End If

If IsObject(WScript) Then

   WScript.ConnectObject session,    "on"

   WScript.ConnectObject application, "on"

End If

Rem--------------------

Rem Set up connection to the Excel data to be keyed into SAP:

Dim objExcel

Dim objSheet, intRow, i

Set objExcel = GetObject(,"Excel.Application")

Set objSheet = objExcel.ActiveWorkbook.ActiveSheet

Rem END Excel data

Rem Set Up Excel File to Start Reading from Cell A2

For i = 2 to objSheet.UsedRange.Rows.Count

    Rem Place excel value into variable strCN 'variables

    CC = Trim(CStr(objSheet.Cells(i, 1).Value))

    Name = Trim(CStr(objSheet.Cells(i, 2).Value))

    Desc = Trim(CStr(objSheet.Cells(i, 3).Value))

    Person = Trim(CStr(objSheet.Cells(i, 4).Value))

    Barc = Trim(CStr(objSheet.Cells(i, 5).Value))

    Category = Trim(CStr(objSheet.Cells(i, 6).Value))

    Node = Trim(CStr(objSheet.Cells(i, 7).Value))

    CoCode = Trim(CStr(objSheet.Cells(i, 8).Value))

    ProfitCtr = Trim(CStr(objSheet.Cells(i, 9).Value))

    CostSheet = Trim(CStr(objSheet.Cells(i, 10).Value))

    Dept = Trim(CStr(objSheet.Cells(i, 11).Value))

    DI = Trim(CStr(objSheet.Cells(i, 12).Value))

    Rem-------------------- 'enter master data into SAP

    session.findById("wnd[0]").maximize

    session.findById("wnd[0]/tbar[0]/okcd").text = "/nks01"

    session.findById("wnd[0]").sendVKey 0

    session.findById("wnd[0]/usr/ctxtCSKSZ-KOSTL").text = CC

    session.findById("wnd[0]/usr/ctxtCSKSZ-DATAB_ANFO").text = "01.01.1950"

    session.findById("wnd[0]/usr/ctxtCSKSZ-DATBI_ANFO").text = "31.12.9999"

    'You can this deactivate.

    'session.findById("wnd[0]/usr/ctxtCSKSZ-DATBI_ANFO").setFocus

    'session.findById("wnd[0]/usr/ctxtCSKSZ-DATBI_ANFO").caretPosition = 10

    session.findById("wnd[0]").sendVKey 0

    session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/txtCSKSZ-KTEXT").text = Name

    session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/txtCSKSZ-LTEXT").text = Desc

    session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/txtCSKSZ-VERAK").text = Person

    session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/txtCSKSZ-ABTEI").text = Barc

    session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/ctxtCSKSZ-KOSAR").text = Category

    session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/ctxtCSKSZ-KHINR").text = Node

    session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/ctxtCSKSZ-BUKRS").text = CoCode

    '------------------------------------- new ----------------------------------------------------------------------------------

     session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/ctxtCSKSZ-WAERS").text = "EUR"

     ' or USD currency

     'session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/ctxtCSKSZ-WAERS").text = "USD"

    '------------------------------------- new ----------------------------------------------------------------------------------

    session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/ctxtCSKSZ-PRCTR").text = ProfitCtr

   ' You can this deactivate.

    'session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/ctxtCSKSZ-PRCTR").setFocus

    'session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN/ssubSUBSCREEN_EINZEL:SAPLKMA1:0300/ctxtCSKSZ-PRCTR").caretPosition = 6

    session.findById("wnd[0]").sendVKey 0

    session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpKZEI").select

    session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpKZEI/ssubSUBSCREEN_EINZEL:SAPLKMA1:0310/chkCSKSZ-MGEFL").selected = false

    ' You can this deactivate.

    'session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpKZEI/ssubSUBSCREEN_EINZEL:SAPLKMA1:0310/chkCSKSZ-MGEFL").setFocus

    session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpTMPT").select

    session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpTMPT/ssubSUBSCREEN_EINZEL:SAPLKMA1:0350/ctxtCSKSZ-KALSM").text = CostSheet

    ' You can this deactivate.

    'session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpTMPT/ssubSUBSCREEN_EINZEL:SAPLKMA1:0350/ctxtCSKSZ-KALSM").setFocus

    'session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpTMPT/ssubSUBSCREEN_EINZEL:SAPLKMA1:0350/ctxtCSKSZ-KALSM").caretPosition = 5

    session.findById("wnd[0]").sendVKey 0

    ' You can this deactivate.

    'session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpADRE").select

    'session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpKOMM").select

    session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabp+CU1").select

    session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabp+CU1/ssubSUBSCREEN_EINZEL:SAPLKMA1:0399/ssubCUSTFLDS:SAPLXKM1:0999/txtCSKS-ZZDEPT").text = Dept

    ' You can this deactivate.

    'session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabp+CU1/ssubSUBSCREEN_EINZEL:SAPLKMA1:0399/ssubCUSTFLDS:SAPLXKM1:0999/txtCSKS-ZZDEPT").caretPosition = 7

    session.findById("wnd[0]").sendVKey 0

    session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabp+CU1/ssubSUBSCREEN_EINZEL:SAPLKMA1:0399/ssubCUSTFLDS:SAPLXKM1:0999/ctxtCSKS-ZZID").text = DI

    ' You can this deactivate.

    'session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabp+CU1/ssubSUBSCREEN_EINZEL:SAPLKMA1:0399/ssubCUSTFLDS:SAPLXKM1:0999/ctxtCSKS-ZZID").setFocus

    'session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabp+CU1/ssubSUBSCREEN_EINZEL:SAPLKMA1:0399/ssubCUSTFLDS:SAPLXKM1:0999/ctxtCSKS-ZZID").caretPosition = 1

    session.findById("wnd[0]").sendVKey 0

    ' You can this deactivate.

    'session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpKZEI").select

    'session.findById("wnd[0]/usr/tabsTABSTRIP_EINZEL/tabpGRUN").select

    session.findById("wnd[0]/tbar[0]/btn[11]").press

NEXT

set objExcel = nothing

set objSheet = nothing 

msgbox "Done!"

First you need to make an important setting, so the pop-up window will not come. See especially the point 2 : http://www.erpexecutive.com/2010/12/how-to-enable-sap-gui-scripting/

Regards,

ScriptMan

[Former Member · Accepted solution]

Thanks scriptman, this is really awesome!  I have one problem though.  is there a way to occupy the script for a few seconds because it's trying to create the next cost center when my ID is still technically in the same transaction creating another cost center.  maybe i can tell the program to go to t-code "/nKS03" for a few seconds.  What do you suggest?  Also the following code didn't work, it just gave me an error. 

set objExel = nothing

set objSheet  = nothing

[Former Member · Accepted solution]

Hi Scriptman.  After i sent my last response, i entered code to make the script send SAP to transaction /nKS03, but even with that, i am still having a problem.  I select a specific hierarchy area for two lines worth of cost center data in my spreadsheet.  My program creates the first cost center, but then when it is creating the second one, it errors and tells me that the hierarchy area is blocked from use because i am editing it.  But then when the script stops, i hit enter in SAP and the error goes away.  Because of this, i entered extra "Enter" button codes right where the code stops, but then it still stops at the heirarchy spot for the second cost center.  I even entered code to tell the program to display a cost center right after it creates it.  That way it would give SAP a second to catch up wiht the script, but it's not working.  Can you help?!  Is there a way to fix this?  I am so close to finishing this thanks to you.  Feel free to email me if it is easier:  christina.soto@pw.utc.com

[script_man · Accepted solution]

Hi Christina,

You can pause the script as follows:

. . .

session.findById("wnd[0]/tbar[0]/btn[11]").press

wscript.sleep 1000              'e,g. for 1 second

NEXT

Regards,

ScriptMan

[Former Member · Accepted solution]

Scriptman,

Thank you so much!  My script works perfectly now!!  I've tested it in an SAP test box with several cost centers and it is extroadinary!  i'm so excited!  I can use it now for the majority of the CO module updates that i make for my job.  I cannot express how thankful I am to you.  It is so great to be able to automate my processes!  Thank you!!  Where did you get your experience from?  Do you have an I.T background?  I have an accounting background, but I am very interested in extending my education to I.T.  I have taken a visual basic class already which allows me to understand what the script's code is doing.  But, i just want to express my gratitude for your help!  🙂

[Former Member]

Hello Script Man.

I have been a fan of your code lately and I`ve used your code extensively with success. So. First of all thanks for helping out.

I am trying to connect Excel and SAP for populating a SAP Transaction with data from Excel. The code pretty much works fine, but after executing the VBA macro, excel does not Quit in Task Manager..  So, when I run the code for the 4th or the 5th time, SAP and Excel both Hang.. Then if I look into my task manager, I see that there are many instances of Excel.. After forcefully ending task, everything appears normal.. Can you help me avoid this. I want to run my VBA Macro as many as times with out closing. Here is my Code : 

Sub Button1()

Dim App, Connection, session As Object

Set SapGuiAuto = GetObject("SAPGUI")

Set App = SapGuiAuto.GetScriptingEngine

Set Connection = App.Children(0)

Set session = Connection.Children(0)

Set xclapp = CreateObject("Excel.Application")

Set xclwbk = xclapp.Workbooks.Open("C:\Users\user1\Desktop\excel.xlsm")

Set xclsht = xclwbk.Sheets("Sheet1")

Application.WindowState = xlMinimized

session.findById("wnd[0]").maximize

session.findById("wnd[0]/tbar[0]/okcd").Text = "/nCO01"

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/ctxtCAUFVD-MATNR").Text = xclsht.Cells(4, 2).Value

...

...

...

...

...

Set xclwbk = Nothing

Set xclsht = Nothing

xclapp.Quit

Set xclapp = Nothing

End Sub

[script_man]

Hi Arun,

I did not believe at all that I have so many fans all over the world.  :-).

You can use the presented script as a VB script. The VBA program would have to make some changes. For example:

Sub Button1()

Dim App, Connection, session As Object

Set SapGuiAuto = GetObject("SAPGUI")

Set App = SapGuiAuto.GetScriptingEngine

Set Connection = App.Children(0)

Set session = Connection.Children(0)

'Set xclapp = CreateObject("Excel.Application")

'Set xclwbk = xclapp.Workbooks.Open("C:\Users\user1\Desktop\excel.xlsm")

Application.Workbooks.Open Filename:= "C:\Users\user1\Desktop\excel.xlsm"

'Set xclsht = xclwbk.Sheets("Sheet1")

Application.sheets("Sheet1").select

Application.WindowState = xlMinimized

session.findById("wnd[0]").maximize

session.findById("wnd[0]/tbar[0]/okcd").Text = "/nCO01"

session.findById("wnd[0]").sendVKey 0

'session.findById("wnd[0]/usr/ctxtCAUFVD-MATNR").Text = xclsht.Cells(4, 2).Value

session.findById("wnd[0]/usr/ctxtCAUFVD-MATNR").Text = Application.Cells(4, 2).Value

...

...

...

...

...

'Set xclwbk = Nothing

ActiveWorkbook.Close

'Set xclsht = Nothing

'xclapp.Quit

'Set xclapp = Nothing

End Sub

After the end of this program, the Excel session is only open that contains the VBA macro. If you close them then Excel is gone in task manager.

Regards,

ScriptMan

[Former Member]

+1
Perfect.. That works like a charm... No wonder, you have chosen the right username - Scriptman.

[Former Member]

Script man,

On a related note about SAP GUI scripting, I find it very difficult to enter data in the Long Text in SAP. In fact, when I record a macro, every step of macro is recorded until I enter the Long Text. When I enter long text data, nothing seems to be recorded in the script ? Why ?

To give you a better understanding of my issue, I have taken some screen shorts for your reference.

Till this screen macro gets recorded, but when I enter the Operation Short Text, the macro refuses to record. Any solution to solve this issue ?

Thanks in Advance

[script_man]

Hi Arun,

why the script recorder does not it , I do not know. The only know the SAP programmer.

But if you open a new thread and there depict the screen where you enter the long text, I could help in some circumstances.

Regards,

ScriptMan

[Former Member]

Hi scriptman,

One more thing, i am able to get the entire message: " Order was create with number #########", but i am unable to get SAP to just sent the order number.  Also, my script randomly stops in the middle of the spreadsheet.  how can i fix these issues?  Thank you in advance for you answer.  🙂

[script_man]

Hi Christina,

First, I thank you for your review. I am glad if I could help you. I'm an I.T. person, so I know my way a little.

Now to your new request::

Unfortunately I do not work with the transaction KO01. It could be that the message appears at the bottom taskbar. Then you could for example try the following:

myText = session.findById("wnd[0]/sbar").text

if InStr(myText,"Order was create with number") >0 then

'Suppose the order number is really at the end

  Order_number = right(myText, 10)

  objSheet.Cells(i, 13).Value = Order_number

end if

Regards,

ScriptMan

[Former Member]

HI Scriptman, Do you ever use the spooler (t-code: SM37?)  I run a 500 line run job.  Then I have to export each spool one by one into a location.  Sometimes a line item will have no spool and i have to skip over it.  Do you know how I can use a script to do this.  I'd like the filename to be the Variant name that i run aka the "Parameters" in the Step List Overview screen.  I recorded the process of exporting the spool, but i don't know what else to do.  My original recording, which i have pasted below is of me going selecting a line with no spool, then goign to the next line, which does havea a spool, and exporting it.  But i don't know how to enter the variatn name as the file name and i don't know how to ask the program to check for a spool, and if there is then to export it, and if there isn't then to bypass it.  Can you help?  Thank you for your continued support.  If Not IsObject(application) Then   Set SapGuiAuto  = GetObject("SAPGUI")   Set application = SapGuiAuto.GetScriptingEngine End If If Not IsObject(connection) Then   Set connection = application.Children(0) End If If Not IsObject(session) Then   Set session    = connection.Children(0) End If If IsObject(WScript) Then   WScript.ConnectObject session,    "on"   WScript.ConnectObject application, "on" End If session.findById("wnd[0]").maximize session.findById("wnd[0]/usr/lbl[42,3]").setFocus session.findById("wnd[0]/usr/lbl[42,3]").caretPosition = 5 session.findById("wnd[0]/tbar[1]/btn[34]").press session.findById("wnd[0]/usr/lbl[42,4]").setFocus session.findById("wnd[0]/usr/lbl[42,4]").caretPosition = 2 session.findById("wnd[0]/tbar[1]/btn[34]").press session.findById("wnd[0]/usr/lbl[14,3]").setFocus session.findById("wnd[0]/usr/lbl[14,3]").caretPosition = 0 session.findById("wnd[0]").sendVKey 2 session.findById("wnd[0]/tbar[1]/btn[48]").press session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").select session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").setFocus session.findById("wnd[1]/tbar[0]/btn[0]").press session.findById("wnd[1]/usr/ctxtDY_PATH").setFocus session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 0 session.findById("wnd[1]").sendVKey 4 session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = "1010_VL01 VF01.XLS.XLS" session.findById("wnd[1]/usr/ctxtDY_FILENAME").setFocus session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 18 session.findById("wnd[1]/tbar[0]/btn[0]").press

[script_man]

Hi Christina,

before getting into your new question, I would like know if you have solved your problem with the restore of the order number to Excel?

Regards,

ScriptMan

[Former Member]

Hi Scriptman,

I am so happy to hear from you.  Yes, i was able to send the order number to excel.  The trim code didn't want to work so i just sent the message to excel and then used excel to trim the order number to it's own cell.  Thank you so much for your help with everything.  Do you know a way to help me with my new question?  I created a new discussion board for it but no replies yet.  I have a script to send the data to local file, but i don't know how to use the variant name as the filename and also go down each step and skip the items that didn't produce a spool.  Can you help? 

[Former Member]

Hi Scriptman,

One more inquiry:

If i am creating internal orders via t-code KO01, how can i get the SAP script to send the order number to the excel spreadsheet that i am using?

for example,

i have a spreadsheet and i'd like a column from my SAP spreadsheet to receive the order number that is generated...?