Transferring data from Excel to SAP

[#1]

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!

D

[Former Member]

Hi ScriptMan,

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

Very nice I search for this a long time..

Noa

[Former Member]

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

[script_man]

Hi Degnic,

I would suggest that you try the following:

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

Regards,

ScriptMan

[Former Member]

Hi ScriptMan

And thank you very much again!

Degnic

[Former Member]

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

[script_man]

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

[Former Member]

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

[Former Member]

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

[script_man]

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

[Former Member]

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!

BR

Degnic

[Former Member]

ScriptMan - a quick question on your reply to 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").

-

---

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

[script_man]

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

[Former Member]

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?

D

[Former Member]

Hey Script Man and guys!,

I am building a table in excel listing some vendors. Each vendor has a corresponding vendor number in our system. I have all of the vendor numbers in an excel column. I am creating a button next to each vendor, and what I want is that when clicking the button, it will run an SAP transaction (ME2L), copy their vendor number into the corresponding vendor field in SAP, and execute the transaction (if possible, if not, then the user can click execute).

I was able to make it open up the transaction, when clicking the button, but I can't seem to be able to copy and paste the vendor number to the Vendor field in SAP. It gives me an error and it says I need an object.

Here's what I have so far: (Note that my VBA coding skills are veeeery limited haha). Please help!

[script_man]

Hi Ivana,

welcome to the forum. This thread still seems to be up to date after a few years.

You can try the following:

session.findById("wnd[0]/usr/ctxtVendor").Text = cells(9,15).value   'for vendor number in row = 9 and column = 15

or

session.findById("wnd[0]/usr/ctxtVendor").Text = cells(activeCell.Row, activeCell.Column).value   'for the contents of an active cell

Regards,

ScriptMan

[Former Member]

Hey Script Man!

Thanks for the reply

Weeeell.. I tried both of them. It still won't work and it tells me that the control couldn't be found by id, and it relates to that command. Does that mean that "Vendor" is not the field's id?

Thanks,

Ivana

[holger_khn]

Hello.

Is your code snippet from SAP Scripting recording?

When I do an recording I got below 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
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").text = "/nME2L"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtEL_LIFNR-LOW").text = "MyVendor"
session.findById("wnd[0]/tbar[1]/btn[8]").press

[Former Member]

Hey there!

No, I'm doing it on Visual Basic on Excel.

Never really used vbs.

[holger_khn]

Sure. But from where you got the correct component identifier?

like "session.findById("wnd[0]/usr/ctxtVendor").Text"?

This is not existing. You Need to perform an SAP Scripting recording which can be implemented in Excel VBA coding.

[Former Member]

OMG! I'm so happy right now haha thaaank you so much!