Question Re: Transferring data from Excel to SAP in Additional Q&A

Transferring data from Excel to SAP

Former Member — Mon, 31 May 2010 10:10:49 GMT

Hi Experts,

My purpose is to update customer prices from Excel to SAP automatically with a script. My problem is: I dont know how to call a certain Excel-file?

If I record a script that opens transaction VK11 and put the right constants in right places places, how shall I call it to open an excel-file and then pick information from cells A1(customer number) B1(product number) C1(price) and then save it and then continue picking rows from excel until blank comes ahead?

Could someone please give me a example of this code?

Thanks in advance!

Re: Transferring data from Excel to SAP

script_man — Mon, 31 May 2010 12:30:51 GMT

Hi Degnic,

I am working for some time with Excel and SAP also. You could try the following simple variant.

for example:


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

Set xclapp = CreateObject("Excel.Application")
Set xclwbk = xclapp.Workbooks.Open("c:\tmp\prices.xls")
set xclsht = xclwbk.Sheets("Tabelle1")

for i = 2 to xclapp.ActiveCell.SpecialCells(11).Row
 for j = 1 to xclapp.ActiveCell.SpecialCells(11).Column 
  if j=1 then CUSTOMER =  xclsht.Cells(i,j).Value 
  if j=2 then  PRODUCT =  xclsht.Cells(i,j).Value 
  if j=3 then PRICE =  xclsht.Cells(i,j).Value 
 next
 session.findById("wnd[0]/usr/. . .").text = CUSTOMER
 session.findById("wnd[0]/usr/. . .").text = PRODUCT
 session.findById("wnd[0]/usr/. . .").text = PRICE
  . . .
  'Here follows the rest of the VBScript.
next
msgbox "All " & cstr(xclapp.ActiveCell.SpecialCells(11).Row - 1) & " Excel rows have been processed."

Set xclwbk = Nothing
Set xclsht = Nothing
xclapp.Quit
set xclapp = Nothing

Regards,

ScriptMan

Re: Transferring data from Excel to SAP

Former Member — Tue, 01 Jun 2010 07:07:48 GMT

Hi ScriptMan,

Thanks for very prompt reply! I will get started with this example.

Re: Transferring data from Excel to SAP

Former Member — Tue, 27 Jul 2010 12:52:03 GMT

Hi ScriptMan,

Excellent solution I'll try it also, I have another solution..

Very nice I search for this a long time..

Noa

Re: Transferring data from Excel to SAP

Former Member — Wed, 28 Jul 2010 07:12:34 GMT

Hi,

I agree with you Noa, this solution has been very usefull.

Now I was wondering, how am I able to select a single cell in excel and bring the value of the cell to SAP?

I understand that code under defines colums A and B, and row 2 is the first value.

for i = 2 to xclapp.ActiveCell.SpecialCells(11).Row

for j = 1 to xclapp.ActiveCell.SpecialCells(11).Column

if j=1 then CUSTOMER = xclsht.Cells(i,j).Value

if j=2 then PRODUCT = xclsht.Cells(i,j).Value

But how am I able to select for example cell "C8" and bring the value to here: session.findById("wnd[0]/usr/ctxtF003").text

Degnic

Re: Transferring data from Excel to SAP

script_man — Wed, 28 Jul 2010 09:44:21 GMT

Hi Degnic,

I would suggest that you try the following:


session.findById("wnd[0]/usr/ctxtF003").text = xclsht.Cells(8,3).Value

Regards,

ScriptMan

Re: Transferring data from Excel to SAP

Former Member — Thu, 29 Jul 2010 09:40:58 GMT

Hi ScriptMan

And thank you very much again!

Degnic

Re: Transferring data from Excel to SAP

Former Member — Tue, 03 Aug 2010 08:18:58 GMT

Hi Again,

I created a script, which runs a certain report to me from SAP and creates a excel file of the results. Now my question is:

Is it possible to launch a excel macro with SAP script?

When the excel file is created, it would be great, if the script would already modifie the excel file. Is this possible and how?

Degnic

Re: Transferring data from Excel to SAP

script_man — Tue, 03 Aug 2010 12:41:00 GMT

Hi Degnic,

On the first question, I can present the following example:


. . .
Set xclapp = GetObject(, "Excel.Application")
xclapp.ScreenUpdating = False
xclapp.Visible = False
xclapp.DisplayAlerts = False

Set xclwbk = xclapp.Workbooks.Open("c:\tmp\macroworksheet.xls")
xclapp.Run "macroworksheet.xls!macro_1"
 
Set xclwbk = Nothing
xclapp.Quit
set xclapp = Nothing
. . .

To the second question, I can say the following:

SAP created a workbook in Excel always called "Table of base (1)". Excel - Macro macro_1 must refer to that name.

The launch of macro_1 must be done in VB script to a suitable place. E.g. is suitable to the place where an Excel file is created. After exiting the macro_1 VB script continues normally.

Regards,

ScriptMan

Re: Transferring data from Excel to SAP

Former Member — Wed, 04 Aug 2010 11:28:56 GMT

Hi ScriptMan,

And thank you very much again!

I was unable to launch macro with SAP-script.

I tried with this:

Set xclapp = CreateObject("Excel.Application")

xclapp.ScreenUpdating = False

xclapp.Visible = False

xclapp.DisplayAlerts = False

Set xclwbk = xclapp.Workbooks.Open("c:\temp\file1.XLS")

set xclsht = xclwbk.Sheets("SHEET1")

xclapp.Run "file1.xls!macro1"

But I get this error message, that the macro does not found. I also tried to place the same macro from excel module to "sheet1" and "thisworkbook" in VBA editor, but still it does not found.

You suggest that the first row should be like: Set xclapp = GetObject(, "Excel.Application"). I changed the "GETOBJECT" to "CreateObject", because when I use "GetObject", I get a error message of activeX component. Does this cause the error message of the missing macro?

Degnic

Re: Transferring data from Excel to SAP

Former Member — Wed, 04 Aug 2010 15:21:18 GMT

Hi Degnic - I applied ScriptMan's model and worked like a charm for me. Here's the code I used:

Set xclapp = CreateObject("Excel.Application")

xclapp.ScreenUpdating = False

xclapp.Visible = False

xclapp.DisplayAlerts = False

Set xclwbk = xclapp.Workbooks.Open("C:\Documents and Settings\erkmeu\Application Data\Microsoft\Excel\XLSTART\PERSONAL.xls")

xclapp.Run "PERSONAL.xls!TanksRep"

xclapp.Run "PERSONAL.xls!Create_Tanks_Pivot"

xclapp.Run "PERSONAL.xls!Save_File_Tank"

Set xclwbk = Nothing

xclapp.Quit

set xclapp = Nothing

You'll notice that I am calling 3 Excel macros one after the other.

This helped me in getting 3 separately-run SAP scripts, each calling an Excel macro to generate refined output files, all merged in one script. So it goes like this, for 3 different type of equipment being analyzed:

SAP script 1 for vessels

Excel macros 1-3 - generate pivot and charts

SAP script 2 for tanks

Excel macros 1-3 generate pivot and charts

SAP script 3 safety valves

Excel macros 1-3 generate pivot and charts

All of these run in one SAP script. One click does it all.

ScriptMan - thanks again.

Regards

Umur

Re: Transferring data from Excel to SAP

script_man — Wed, 04 Aug 2010 16:41:45 GMT

Hi Degnic,

as I said the command set xclapp = GetObject (, "Excel.Application") can be used only at the point when an Excel session was already open. Otherwise, it is correct to set instead set xclapp = CreateObject("Excel.Application").

Can start the macro named Macro1 manually from Excel? I see no error in your code.

Umur - I am pleased to have helped you.

Regards,

ScriptMan

Re: Transferring data from Excel to SAP

Former Member — Thu, 05 Aug 2010 12:40:37 GMT

Hi ScriptMan,

And thanks again! I finally got it to work when I tried the different computer and excel version 2003 instead of 2007.

This has been very usufull thread! You must be very valueable to your company. I think you should go and ask for a raise to your sallary. I can recommend you for sure!

Degnic

Re: Transferring data from Excel to SAP

Former Member — Fri, 06 Aug 2010 20:54:56 GMT

ScriptMan - a quick question on your reply to Degnic...

Is there a way to get the script check first if Excel is already open or not? If there is, then a IF statement can apply the correct line whether it is 'get' or 'create'.

Regards

Umur

Re: Transferring data from Excel to SAP

script_man — Sat, 07 Aug 2010 04:08:17 GMT

Hi Umur,

I think there are several solutions. One of them may look like as :


. . .
on error resume next
xclapp = GetObject (, "Excel.Application") 
if err.number > 0 or err.number < 0 then set xclapp = CreateObject("Excel.Application")
on error goto 0
. . .

There is much more between Excel and SAP what one has not yet addressed.

Degnic - Thank you for your positive assessment

Regards,

ScriptMan

Edited by: ScriptMan on Aug 7, 2010 6:09 AM

Re: Transferring data from Excel to SAP

Former Member — Sun, 15 Aug 2010 08:38:08 GMT

This message was moderated.

Re: Transferring data from Excel to SAP

Former Member — Tue, 07 Sep 2010 12:10:39 GMT

Hi,

Lets continue this script, as it has been so usefull.

Does anyone know, is it possible to launch SAP script with excel macro? I added the code of the script at the end of the excel macro code, but it did not work. How shall you call a SAP script in excel macro?

Re: Transferring data from Excel to SAP

Former Member — Tue, 23 Apr 2013 20:12:42 GMT

HI, Script Man

im new about scripts in SAP,

i want do make is optimizate an orders notification process what we do every month.

what i did is to record a script and then (by the notepad program) modificated it, putting the values that i want in a worksheet (excel) and taking the values from that sheet, my whole code is the following one:

******************************************************************************************

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

Set xclapp = CreateObject("Excel.Application")
Set xclwbk = xclapp.Workbooks.Open("C:\Users\ccoaquim\Desktop\Libro1.xls")
set xclsht = xclwbk.Sheets("Hoja3")

for i= 4 to 8

session.findById("wnd[0]/tbar[0]/okcd").text = "/N iw41"
session.findById("wnd[0]").sendVKey 0

if xclsht.Cells(i+1,3).Value > 10 then

session.findById("wnd[0]/usr/ctxtCORUF-AUFNR").text = xclsht.Cells(i,2).Value
session.findById("wnd[0]/usr/ctxtCORUF-AUFNR").setFocus
session.findById("wnd[0]/usr/ctxtCORUF-AUFNR").caretPosition = 9
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/tblSAPLCORUTC_3100").getAbsoluteRow(0).selected = true
session.findById("wnd[0]/usr/tblSAPLCORUTC_3100/txtCORUF-UPD_ICON[0,0]").setFocus
session.findById("wnd[0]/usr/tblSAPLCORUTC_3100/txtCORUF-UPD_ICON[0,0]").caretPosition = 0
session.findById("wnd[0]/tbar[1]/btn[8]").press

session.findById("wnd[0]/usr/txtW_NOPERS").text = xclsht.Cells(i,8).Value
session.findById("wnd[0]/usr/txtW_DURPERS").text = xclsht.Cells(i,6).Value
session.findById("wnd[0]/usr/txtW_AUTOR").text = xclsht.Cells(i,10).Value
session.findById("wnd[0]/usr/ctxtAFRUD-ISDD").text = xclsht.Cells(i,11).Value
session.findById("wnd[0]/usr/ctxtAFRUD-ISDZ").text = xclsht.Cells(i,13).Value
session.findById("wnd[0]/usr/ctxtAFRUD-IEDD").text = xclsht.Cells(i,14).Value
session.findById("wnd[0]/usr/ctxtAFRUD-IEDZ").text = xclsht.Cells(i,16).Value
session.findById("wnd[0]/usr/ctxtAFRUD-GRUND").text = "1001"
session.findById("wnd[0]/usr/txtAFRUD-LTXA1").text = xclsht.Cells(i,17).Value
session.findById("wnd[0]/usr/txtAFRUD-LTXA1").setFocus
session.findById("wnd[0]/usr/txtAFRUD-LTXA1").caretPosition = 1
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 0

else

end if

******************************************************************************

i just notice that the comand "if" is not working, i mean that in the line 25:

"if xclsht.Cells(i+1,3).Value > 10 then"

the script doesnt follow the "if" comand, and it also continue with the comands which are after

the comand : "else"

Please i'd be very thankfull to get an answer

Thanks

Jorge Chávez

Re: Transferring data from Excel to SAP

script_man — Wed, 24 Apr 2013 15:42:27 GMT

Hi Jorge,

you can try this:

. . .

j=i+1

if xclsht.Cells(j,3).Value > 10 then

. . .

Regards,

ScriptMan

Re: Transferring data from Excel to SAP

Former Member — Fri, 03 May 2013 13:07:27 GMT

thanks a lot for ur answer... it worked!

now one more question...

in the part when i put:

Set xclwbk = xclapp.Workbooks.Open("C:\Users\ccoaquim\Desktop\Libro1.xls")

im making reference to a local disc in my pc "C:\",

can i reference a disc which is not a local one, a disc that i had added from my local network ?

i tried like this:

Set xclwbk = xclapp.Workbooks.Open("\\pebk016440\d:\Users\ccoaquim\Desktop\Libro1.xls")

but it didnt work..

thnks for ur answer!