Stack Exchange
"The method got an invalid argument" error in Range("B1:B3").Value
I am trying to fetch data from excel sheet into my SAP system with the help of following part of the code :
Dim xcl As Object

Dim wbk As Workbook

Dim sht As Worksheet

Application.DisplayAlerts = False

Set xcl = CreateObject("Excel.Application")

Set wbk = Workbooks.Open("C:\....")

Set sht= wbk.Sheets("excel1")

Dim j As Integer

For j = 0 To 2

 **session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1," & Int(j) & "]").Text = sht.Range("B1:B3").Value**

Next j

End Sub

With lots of research on stackoverflow(@scriptman is my hero), i am finally able to fetch data in SAP system using " & Int(j) & ".
But it only works if  I am using sht.Range("B1").Value
If i use,  sht.Range("B1:B3").Value, I get error in the above bold line as 
"Runtime error 613"
The method got an invalid arguement"
Please help.
I am stuck at this since a month.

You're trying to pass an array to something that clearly expects a single value.

Fresh from today: https://stackoverflow.com/questions/44802467/using-left-for-variable/44802555?

or try to replace the last part to sht.Range("B1").Value

= sht.Range("B1:B3").Value - Try entering =B1:B3 in cell D1. What do you expect Excel to return for D1's value? You need to figure out which of the 3 values you want to assign to that text element; you can't assign a "bunch of values" to a single string.

@Mat'sMug : Then how can i pass a range of values?
I want values from B1 to B3 ..

You can't. You need to figure out a way to combine them into one single value. Concatenate them?


How can I pass a range of values?

You don't. You can't put 3 cell values in 1 slot. You want [B1:B3] in 3 slots, so inside your 0-2 loop you'll take the cell in column B at row j + 1 - I had a hard time formatting that ID string in the code block, so I extracted it into its own variable; it also makes the assignment instruction a bit clearer:
Dim id As String
id = "wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I"

For j = 0 To 2
    session.findById(id & "[1, " & j & "]").Text = sht.Range("B" & j + 1).Value
Next j


Absolutely correct!!
This works !!
Thankyou so much
Now i have 2 heroes : scriptman and mat's mug :)

Just one more thing..
It works with 3 fields
What if I have more than 10 fields in a column in excel sheet?
By your method, SAP is taking only 8 fields at one time.

You control the value of j. I don't know how the SAP API works, that's for you to figure out. Make it loop from 0 to 9 and you'll store B1:B10 into [1,0] through [1,9]; whether or not that's valid depends on how the API works.

Actually what i am trying is after executing a transaction, I get bunch of user ids, then I copy it and paste it into another transaction window.
But the problem is "UPLOAD FROM CLIPBOARD" doesnot work in vba
hence, i copy the user ids into excel and then fetch them into SAP

@ScriptMan : can you help here?

And this is why asking about Y when you want to solve X, is wasting everyone's time. Good luck.

haha..that is because X does not work :)
So, we have to go through Y because Y will definitely work. Just need some help outta there :-)

What Mat'sMug is trying to tell you, @AnirudhChauhan, is that if you had "Actually" asked the "what I am trying to do..." part as your original question, you'd have received an answer to that instead of finding a fix to a problem that doesn't actually address your real problem.

If you create a sum, you can transfer it to SAP.
for example:
session.findById("wnd[1]/usr/tabsTAB_STRIP....ctxtRSCSEL_255-SLOW_I[1," & Int(j) & "]").Text = xcl.WorksheetFunction.Sum(sht.Range("B1:B3"))
Regards,
ScriptMan

yayy!!
Scriptman to the rescue :)
Now the error doesnot show up, but it shows 0 in all the three boxes in SAP
why is that so?

First I would test the formula without SAP only in Excel. for example: sht.Range("A1").value = xcl.WorksheetFunction.Sum(sht.Range("B1:B3"))

i checked !!
Still returning value as 0.

Then the error is in the formula or in the cells to which the formula refers. But you can also test the following. for example: = sht.Range("B1").Value + sht.Range("B2").Value + sht.Range("B3").Value

Still it is returning as 0.
Just a small doubt, why are you using Sum function ?
We dont want to add the values
We just want a set of values in the column(User ids) to be copied into SAP field columns

Cant we do something like this?
rng=Range("B1:B3")
sht.Range("rng").Value
Not sure.
need help with the syntax

It does not help. You would have to present the contents of the 3 affected cells here. Then one could talk about it further.

The cells contain User IDs like BC1234, CD1234, BCDEFG

@AnirudhChauhan you have 3 values and 1 slot. Either you add them up to make 1 value, or you find 2 more slots so you can fit your 3 values.

Now we know, the values can not be added. Do you want to pass the value from Excel, depending on "j"? This could be realized as follows: session.findById("wnd[1]/usr/tabsTAB_STRIP....ctxtRSCSEL_255-SLOW_I[1," & Int(j) & "]").Text = sht.Range("B" & cstr(j+1))

@Mat'sMug : I dont have one slot
i have multiple slots in SAP fields. It is like a column
Hence, I am using j variable in for loop to fetch the values

@ScriptMan : No...it does not depend on j.
j variable is used in for loop as column number in SAP field.
Likewise, we can use variable i to loop the column number in excel
Do you know any sort of this possibility?

To understand what you want to do, you should assign the values from Excel in the following way. for example: session.findById("wnd[1]/usr/tabsTAB_STRIP....ctxtRSCSEL_255-SLOW_I[1,0]").Text = "BC1234"; session.findById("wnd[1]/usr/tabsTAB_STRIP....ctxtRSCSEL_255-SLOW_I[1,1]").Text = "CD1234"; session.findById("wnd[1]/usr/tabsTAB_STRIP....ctxtRSCSEL_255-SLOW_I[1,2]").Text = "BCDEFG"

@ScriptMan : I cannot do that..because I dont know what will be the value of these fields
Moreover, It is not just these three. There are upto 80 userids

Too bad it did not work. In the 6th comment before, the solution you did accept later was already available.

@ScriptMan : no worries, scriptman
you will always be my hero :)
