Help needed with VBA script to SAP Loop

M

mark_m66

New Member
Joined
May 29, 2014
Messages
2
Hello experts,

I am trying to write a vba script to script data from excel to SAP. The script below is to create multiple rows of information from Excel to one document in SAP. This example is for a Purchase order but would need to be able to do the same thing in various accounting transactions as well.

Could someone look at the script below and tell me where I am going wrong (Newbie here) I am getting the error" [TABLE="width: 63"]
<tbody>[TR]
[TD="width: 63"]Error # 13 was generated by VBAProject
Type mismatch[/TD]
[/TR]
</tbody>[/TABLE]

The variable data in this script is PO# and QTY

Would truly appreciate some help!!

Here is the faulty script:
Option Explicit
#If Win64 Then
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
Dim oSapGuiAuto As Object, oGuiApplication As Object, oConnection As Object, Session As Object
Dim lLastRow As Long, lDataRow As Long
Dim A As String, B As String, C As String, D As String, E As String, F As String, G As String, H As String
Dim I As String, J As String, K As String, L As String, M As String, N As String, O As String, sMessage As String
Dim sStartingTransaction As String, sContinue
Private Sub PlayBack_The_Script()
'called from the main sub - "Excel_Starts_Here"
On Error GoTo ErrorHandler
'//************************************************
'//******PASTE YOUR RECORDED SCRIPT BELOW**********
'// ** Lines that begin Session.findById("wnd *
'//************************************************
'// *** to use variable data in Spreadsheet Column 'A', change from .Text = "..." to .Text = A (no quotes)
'// *** to use variable data in Spreadsheet Column 'B', change from .Text = "..." to .Text = B ect.
'//__________________________________________________________________________________________________________
Do Until Sheets("sap_data").Cells(A, C) = ""
Session.findById("wnd[0]").maximize
Session.findById("wnd[0]/tbar[0]/okcd").Text = "/nme21n"
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0016/subSUB0:SAPLMEGUI:0030/subSUB1:SAPLMEGUI:1105/ctxtMEPO_TOPLINE-SUPERFIELD").Text = "50900"
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0016/subSUB0:SAPLMEGUI:0030/subSUB1:SAPLMEGUI:1105/ctxtMEPO_TOPLINE-SUPERFIELD").SetFocus
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0016/subSUB0:SAPLMEGUI:0030/subSUB1:SAPLMEGUI:1105/ctxtMEPO_TOPLINE-SUPERFIELD").caretPosition = 5
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0016/subSUB1:SAPLMEVIEWS:1100/subSUB1:SAPLMEVIEWS:4000/btnDYN_4000-BUTTON").press
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/ctxtMEPO1211-EMATN[4,0]").Text = A
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/txtMEPO1211-MENGE[6,0]").Text = B
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB1:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1102/tabsHEADER_DETAIL/tabpTABHDT9/ssubTABSTRIPCONTROL2SUB:SAPLMEGUI:1221/ctxtMEPO1222-EKORG").Text = "1000"
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB1:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1102/tabsHEADER_DETAIL/tabpTABHDT9/ssubTABSTRIPCONTROL2SUB:SAPLMEGUI:1221/ctxtMEPO1222-EKGRP").Text = "10"
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/txtMEPO1211-MENGE[6,0]").SetFocus
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/txtMEPO1211-MENGE[6,0]").caretPosition = 17
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/ctxtMEPO1211-NAME1[15,0]").Text = "1000"
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/ctxtMEPO1211-NAME1[15,0]").caretPosition = 4
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/tbar[0]/btn[11]").press
Sheets("sap_data").Cells(A, 10).Value = Session.findById("wnd[0]/sbar").Text


A = A + 1


Loop




'//__________________________________________________________________________________________________________
'//******PASTE YOUR RECORDED SCRIPT ABOVE**********
'//******END OF RECORDED SAP SCRIPT*********************************
'//*****************************************************************
' test for error and log off messages
If Session.ActiveWindow.Text = "Error" Then Err.Raise 99 ' error detected?
'


If Session.ActiveWindow.Children.Count > 1 Then ' see if the collection item exists
sMessage = Session.ActiveWindow.Children.Item((Session.ActiveWindow.Children.Count - 1)).Text ' message found
If sMessage = "" Then sMessage = "OK"
Sheets("SAP_DATA").Cells(lDataRow, 17) = sMessage
Else
Sheets("SAP_DATA").Cells(lDataRow, 17) = "OK" ' message - no error found
End If
'
If Session.ActiveWindow.Name = "wnd[1]" And Session.ActiveWindow.Text = "Log Off" Then
Session.findById("wnd[1]/usr/btnSPOP-OPTION2").press
Sheets("SAP_DATA").Cells(lDataRow, 16) = "error"
End If
'-------------------------------------------------------
Exit Sub
ErrorHandler:
Sleep (1000)
'return the error message 3/4/06 - ***
sMessage = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description '& Err.HelpFile & Err.HelpContext
Sheets("SAP_DATA").Cells((lDataRow), 21) = sMessage


On Error Resume Next
If Session.ActiveWindow.Type = "GuiModalWindow" Then
Sheets("SAP_DATA").Cells(lDataRow, 18) = Session.ActiveWindow.Text & " " & Session.ActiveWindow.PopupDialogText '3/16/2006 - *** - additional message to return
Else
Sheets("SAP_DATA").Cells(lDataRow, 18) = Session.ActiveWindow.Text
End If
'
If Session.ActiveWindow.Children.Count > 1 Then
Sheets("SAP_DATA").Cells(lDataRow, 19) = Session.ActiveWindow.Children.Item((Session.ActiveWindow.Children.Count - 1)).Text
End If


If Session.ActiveWindow.SystemFocus Is Nothing Then
' ------------
If sStartingTransaction = "SESSION_MANAGER" Then sStartingTransaction = ""
' 12/15/04 test and close window[1] - ***
If Session.ActiveWindow.Name = "wnd[1]" Then
Session.findById("wnd[1]").Close
Else
If Session.ActiveWindow.Name <> "wnd[0]" Then '3/17/06 - ***
AppActivate "Microsoft Excel"
MsgBox " Lost SAP focus "
ContinueOrCancel 'ask

Else
Session.findById("wnd[0]/tbar[0]/okcd").Text = "/n" & sStartingTransaction
Session.findById("wnd[0]/tbar[0]/btn[0]").press
'
End If
End If
' ------------
Else
Sheets("SAP_DATA").Cells(lDataRow, 20) = Session.ActiveWindow.SystemFocus.Text
If Session.ActiveWindow.Name = "wnd[3]" Then
Session.findById("wnd[3]/tbar[0]/btn[0]").press 'auto ackn error
Session.findById("wnd[2]/tbar[0]/btn[12]").press ' hit cancel
'********** test before pressing the cancel key on window [1]- *** 12/15/04
Sleep (10)
End If
If Session.ActiveWindow.Name = "wnd[2]" Then
Session.findById("wnd[2]/tbar[0]/btn[0]").press 'auto ackn error
'********** test before pressing the cancel key on window [1]- *** 12/15/04
Sleep (10)
End If
If Session.ActiveWindow.Name = "wnd[1]" Then
If Session.ActiveWindow.Children.Count > 0 Then ' 3/16/06 - ***
If Session.ActiveWindow.Text = "Information" Then ' blow off the message
Session.findById("wnd[1]/tbar[0]/btn[0]").press
Resume '***This retries the keystroke that caused the error***3/5/2004
End If
End If
Session.findById("wnd[1]/tbar[0]/btn[12]").press ' pound on that cancel key
End If

If Session.ActiveWindow.Name = "wnd[0]" Then
'test for starting at the menu
If sStartingTransaction = "SESSION_MANAGER" Or sStartingTransaction = "" Then 'avoid an error 3/17/06 - ***
If Session.ActiveWindow.Text <> "SAP Easy Access" Then Session.findById("wnd[0]/tbar[0]/btn[12]").press ' hit cancel
'
Session.findById("wnd[0]/tbar[0]/okcd").Text = "/n" '3/17/06 - ***
Session.findById("wnd[0]/tbar[0]/btn[0]").press
'
Else
Session.findById("wnd[0]/tbar[0]/okcd").Text = "/n" & sStartingTransaction
Session.findById("wnd[0]/tbar[0]/btn[0]").press
End If
Else
ContinueOrCancel 'ask
End If 'Session.ActiveWindow.Name = "wnd[0]"
End If 'Session.ActiveWindow.SystemFocus Is Nothing
'
Exit Sub
End Sub
Sub Run_SAP_Script()
'---------------------------------------------------------------
' *** abc abc February 12, 2004 |
' changed error handling to ignore informational messages 3/5/04|
' replaced Look routine with Sleep. 1/22/07 |
'---------------------------------------------------------------
' Activate Scripting |
Set oSapGuiAuto = GetObject("SAPGUI") ' |
Set oGuiApplication = oSapGuiAuto.GetScriptingEngine ' |
If (oConnection Is Nothing) Then ' |
Set oConnection = oGuiApplication.Children(0) ' |
End If ' |
If (Session Is Nothing) Then ' |
Set Session = oConnection.Children(0) ' |
End If ' |
If (Session Is Nothing) Then Exit Sub ' |
sStartingTransaction = Session.info.transaction '|
'----------------------------------------------------------------
Application.CutCopyMode = False
Rows("1:1").Select '3/4/06 - ***
Selection.Insert Shift:=xlDown ' give it a blank row to find and delete
[A:A].SpecialCells(xlBlanks).EntireRow.Delete
lLastRow = Sheets("SAP_DATA").UsedRange.Rows.Count
'---------------------------------------------------------
If lLastRow = 0 Then lLastRow = 1 ' run at least once
'---------------------------------------------------------
'*******************************************
' Shrink Excel and move it out of the way
Application.WindowState = xlMinimized
'*******************************************
' Try one
lDataRow = 2
Read_Spreadsheet
PlayBack_The_Script
'Give the user a chance to verify
If lDataRow < lLastRow Then
AppActivate "Microsoft Excel"
Application.WindowState = xlMaximized
sContinue = MsgBox("First Transaction is complete... Continue Processing?", vbOKCancel)
If sContinue = vbOK Then ' User chose Yes.
Application.WindowState = xlMinimized
'--------------------------------
' Run the script
For lDataRow = 3 To lLastRow
Read_Spreadsheet
PlayBack_The_Script
Next lDataRow
'--------------------------------
End If ' - run all transactions
End If ' lDataRow < lLastRow
' Cleanup
Set Session = Nothing
Set oConnection = Nothing
Set oGuiApplication = Nothing
Set oSapGuiAuto = Nothing
Application.WindowState = xlMaximized
MsgBox "Done"
End Sub


Private Sub Read_Spreadsheet()
A = Trim(Sheets("SAP_DATA").Cells(lDataRow, 1))
B = Trim(Sheets("SAP_DATA").Cells(lDataRow, 2))
C = Trim(Sheets("SAP_DATA").Cells(lDataRow, 3))
D = Trim(Sheets("SAP_DATA").Cells(lDataRow, 4))
E = Trim(Sheets("SAP_DATA").Cells(lDataRow, 5))
F = Trim(Sheets("SAP_DATA").Cells(lDataRow, 6))
G = Trim(Sheets("SAP_DATA").Cells(lDataRow, 7))
H = Trim(Sheets("SAP_DATA").Cells(lDataRow, 8))
I = Trim(Sheets("SAP_DATA").Cells(lDataRow, 9))
J = Trim(Sheets("SAP_DATA").Cells(lDataRow, 10))
K = Trim(Sheets("SAP_DATA").Cells(lDataRow, 11))
L = Trim(Sheets("SAP_DATA").Cells(lDataRow, 12))
M = Trim(Sheets("SAP_DATA").Cells(lDataRow, 13))
N = Trim(Sheets("SAP_DATA").Cells(lDataRow, 14))
O = Trim(Sheets("SAP_DATA").Cells(lDataRow, 15))


End Sub


Private Sub ContinueOrCancel()
' -------------------------------------------------------------------
sContinue = MsgBox("Do you want to Continue?", vbRetryCancel)
If sContinue = vbRetry Then ' User chose Yes.
MsgBox "recycle SAP transaction (Green Arrow back) to " & sStartingTransaction
Else
lDataRow = lLastRow '
End If
' -----------------------------------------------------------------------


End Sub
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Hi and welcome to the MrExcel Message Board.

The error is from Excel rather than SAP. It is trying to say that it cannot perform some action as the variables are of the wrong type. It can multiply the character 1 by the figure 1 but it cannot do things like multiply "A1" by 1, for instance.

I am guessing that you did not create the original part of the script but just added your ME21N transaction into someone else's existing script? So I am going to assume that the pre-existing script is probably OK.

The additional code does look a bit suspect. For instance, I would not expect it to be inside a Do Until loop because the main script has a loop built in.

The only bit of Excel arithmetic in there is the A=A+1 step. Is that supposed to be incrementing a Material Number? What format are your Material Numbers in? The variable A is intended to be read from the spreadsheet as far as I can see and not be calculated.

I also have a slight problem with this line, apart from the fact that I think the loop may be unnecessary: Do Until Sheets("sap_data").Cells(A, C) = ""

Cells() requires two numbers to give a cell reference, e.g. Cells(2,3) meaning Range("C2").

Sorry, I can't actually run the script because I don't have access to SAP.
 
Upvote 0
Hello Rick,

Thanks for the reply. you were right about the arithmetic error, I don't know how I missed that...

The inner loop however is intentional, what I am trying to accomplish is to create all of the variable data in column A to write to one document rather than a seperate doc per row.

I changed to be as below, however it is still not working, any other ideas? The Do Until is confusing me

Session.findById("wnd[0]").maximize
Session.findById("wnd[0]/tbar[0]/okcd").Text = "/nme21n"
Session.findById("wnd[0]").sendVKey 0
Do Until Sheets("sap_data").Cells(2, 1) = ""
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0016/subSUB0:SAPLMEGUI:0030/subSUB1:SAPLMEGUI:1105/ctxtMEPO_TOPLINE-SUPERFIELD").Text = "50900"
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0016/subSUB0:SAPLMEGUI:0030/subSUB1:SAPLMEGUI:1105/ctxtMEPO_TOPLINE-SUPERFIELD").SetFocus
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0016/subSUB0:SAPLMEGUI:0030/subSUB1:SAPLMEGUI:1105/ctxtMEPO_TOPLINE-SUPERFIELD").caretPosition = 5
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0016/subSUB1:SAPLMEVIEWS:1100/subSUB1:SAPLMEVIEWS:4000/btnDYN_4000-BUTTON").press
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/ctxtMEPO1211-EMATN[4,0]").Text = A
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/txtMEPO1211-MENGE[6,0]").Text = B
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB1:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1102/tabsHEADER_DETAIL/tabpTABHDT9/ssubTABSTRIPCONTROL2SUB:SAPLMEGUI:1221/ctxtMEPO1222-EKORG").Text = "1000"
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB1:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1102/tabsHEADER_DETAIL/tabpTABHDT9/ssubTABSTRIPCONTROL2SUB:SAPLMEGUI:1221/ctxtMEPO1222-EKGRP").Text = "10"
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/txtMEPO1211-MENGE[6,0]").SetFocus
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/txtMEPO1211-MENGE[6,0]").caretPosition = 17
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/ctxtMEPO1211-NAME1[15,0]").Text = "1000"
Session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/ctxtMEPO1211-NAME1[15,0]").caretPosition = 4
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/tbar[0]/btn[11]").press
Sheets("sap_data").Cells(A, 10).Value = Session.findById("wnd[0]/sbar").Text






Loop

Thanks,

Mark
 
Upvote 0
Hi.

A bit more information about the "it is still not working" would be helpful. Put yourself in my position, I don't know how much you know about Excel or SAP, I don't know exactly what you are trying to do, I don't have a copy of SAP so I can't check very much and I can't see any error messages or see at what point things stop working.

However, pressing on regardless :)

If you are trying to put all the spreadsheet rows on one screen then you will need some changes. The master script works like this:
Code: 
Do While Data Exists
         Get the next data row
         Run your script
                 Call ME21N
                 Update one row 
                 End and Save ME21N
         End your script
Next
If you go with the Do Until logic then you will need to step through the spreadsheet rows in your part of the script and also increment the row numbers on the ME21N screen. They are the numbers in the square brackets. (e.g. /tblSAPLMEGUITC_1211/txtMEPO1211-MENGE[6,0]").Text = B) The SAP Wizard should be able to show you how they change.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Help needed with VBA script Method ’Children' of object ’ISapMainWindowTarget' failed
Replies
1
Views
2K
Zaifee
Z
B
  • Question Question
Issue with Excel VBA using multiple For and Next
Replies
1
Views
723
Micron
M
T
  • Question Question
Macro to download SAP report to Excel
Replies
0
Views
3K
texasguy66
T
S
  • Question Question
SAP GUI DOES NOT SAVE in MACRO VBA
Replies
1
Views
2K
Zaifee
Z
S
  • Question Question
SAP GUI Scripting does not save the report
Replies
2
Views
1K
szymon94
S

Forum statistics

Threads
1,223,775
Messages
6,174,458
Members
452,566
Latest member
Bonnie_bb

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top