Journal Entry Excel Macro via UI API. Error: Form - Bad Value

[john_obrien7]

Hi all,

I have an excel macro that creates a JE in SAP B1 via UI API.

It just opens the JE form and sets the values in the relevant columns from the spread sheet.

All works fine with GL accounts.

For BP codes however, I haven't been able to make it work.

Normally you would use CTRL+TAB to set the BP code.

Code for setting this value is as follows:

Set oEdit = oMatrix.Columns.Item("1").Cells.Item(iRow - 5).Specific()           'GL Acc

oEdit.Value = Cells(iRow, Range("JE_Lines[Account Code]").Column).Value

Error is as follows: Form - Bad Value

Can anyone assist on setting this value with a BP Code via UI API?

Thanks!

John

[edy_simon · Accepted solution]

Hi John

You need to emulate the Ctrl+Tab keys after setting the BP Code.

Use it like

SBO_Application.SendKeys("KeysCombination")

Below is the documentation from UI help.

RegardsA
Edy

Visual Basic
Public Sub SendKeys( _    ByVal Text As String _ )

Parameters

Text

The key combination to send

Remarks

Each key is represented by one or more characters.

• To specify a single keyboard character, use the character itself. For example, to send the letter A, use A for Text. 
• To represent more than one character, append each additional character to the one preceding it. For example, to send the letters A, B, and C, use ABC for Text. 
• The plus sign (+), caret (^), percent sign (%), tilde (~), and parentheses ( ) have special meanings in SendKeys. To send one of these characters, enclose it within braces ({}). For example, to send the plus sign, use {+}. Brackets ([ ]) have no special meaning in SendKeys, but you must enclose them in braces. In other applications, brackets do have a special meaning that may be significant when dynamic data exchange (DDE) occurs. To specify brace characters, use {{} and {}}.

To send characters that aren't displayed when you press a key, such as ENTER or TAB, and keys that represent actions rather than characters, use the codes shown below: 

Key	Code
Backspace	{BACKSPACE}, {BS}, or {BKSP}
Break	{BREAK}
Caps Lock	{CAPSLOCK}
Delete	{DELETE} or {DEL}
Down Arrow	{DOWN}
End	{END}
Enter	{ENTER}
Esc	{ESC}
Help	{HELP}
Home	{HOME}
Insert	{INSERT} or {INS}
Left Arrow	{LEFT}
Num Lock	{NUMLOCk}
Page Down	{PGDN}
Page Up	{PGUP}
Right Arrow	{RIGHT}
Scroll Lock	{SCROLLLOCK}
Tab	{TAB}
Up Arrow	{UP}
F1, F2, F3...	{F1}, {F2}, {F3}...

To specify key combinations with any of the SHIFT, CTRL, and ALT keys, precede the key code with one or more of the following codes:

Key	Code
Shift	{+}
Ctrl	{^}
Alt	{%}

To specify that any combination of SHIFT, CTRL, and ALT should be held down while several other keys are pressed, enclose the code for those keys in parentheses. For example, to specify holding down SHIFT while E and C are pressed, use " +(EC)". To specify holding down SHIFT, while E is pressed, followed by C without SHIFT, use " +EC".

Warning

• SendKeys does not support sending the same key repeatedly.
• SendKeys does not support sending keyboard combinations that are not relevant to SAP Business One. For example, all the function keys currently do not have any functionality associated with them.

See

[john_obrien7 · Accepted solution]

Hi Edy,

Yes, this is correct, I also have to do a sendkeys for the actual BP code value itself.

This is because if I just use a set the value of the edit text using oEdit.Value = .... then it will throw that error immediately. I found that the I have to emulate a click on the field to set the focus, then send the value via sendkeys, then send CTRL+Tab.

Thanks!

John

[john_obrien7]

I managed to resolve this one using sendkeys.

If the value is a BP then do this:

oMatrix.Columns.Item("1").Cells.Item(iRow - 5).Click

SBO_App.SendKeys (Cells(iRow, Range("JE_Lines[Account Code]").Column).Value)

SBO_App.SendKeys ("^{TAB}")

Thanks,

John

[Former Member]

Hi John,

try setting the BP debitor account in account code.

oBP.GetByKey(bpCode)

oBP.DebitorAccount << this one.

Regards,

Jim

[john_obrien7]

Hi Jim,

Thanks for the input. 
I don't think that would be sufficient because it wouldn't be against this particular BP.  Also, B1 does not allow direct posting to Ctrl accounts. That's why I need to use the BP code.

Thanks,

John