-1

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.

Community
  • 1
  • 1
Anirudh Chauhan
  • 111
  • 2
  • 9
  • 1
    You're trying to pass an array to something that clearly expects a single value. – Rory Jun 28 '17 at 14:46
  • 1
    Fresh from today: https://stackoverflow.com/questions/44802467/using-left-for-variable/44802555? – A.S.H Jun 28 '17 at 14:46
  • or try to replace the last part to `sht.Range("B1").Value` – Vityata Jun 28 '17 at 14:47
  • `= 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. – Mathieu Guindon Jun 28 '17 at 15:14
  • @Mat'sMug : Then how can i pass a range of values? I want values from B1 to B3 .. – Anirudh Chauhan Jun 29 '17 at 09:28
  • You can't. You need to figure out a way to combine them into one single value. Concatenate them? – Mathieu Guindon Jun 29 '17 at 13:12

2 Answers2

1

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
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Absolutely correct!! This works !! Thankyou so much Now i have 2 heroes : scriptman and mat's mug :) – Anirudh Chauhan Jun 29 '17 at 14:05
  • 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. – Anirudh Chauhan Jun 29 '17 at 14:16
  • 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. – Mathieu Guindon Jun 29 '17 at 14:19
  • 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 – Anirudh Chauhan Jun 29 '17 at 14:20
  • @ScriptMan : can you help here? – Anirudh Chauhan Jun 29 '17 at 14:21
  • And this is why asking about Y when you want to solve X, is wasting everyone's time. Good luck. – Mathieu Guindon Jun 29 '17 at 14:22
  • 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 :-) – Anirudh Chauhan Jun 29 '17 at 14:23
  • 1
    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. – FreeMan Jun 29 '17 at 14:27
0

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

ScriptMan
  • 1,580
  • 1
  • 9
  • 9
  • 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? – Anirudh Chauhan Jun 29 '17 at 09:47
  • First I would test the formula without SAP only in Excel. for example: sht.Range("A1").value = xcl.WorksheetFunction.Sum(sht.Range("B1:B3")) – ScriptMan Jun 29 '17 at 10:46
  • i checked !! Still returning value as 0. – Anirudh Chauhan Jun 29 '17 at 10:58
  • 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 – ScriptMan Jun 29 '17 at 11:19
  • 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 – Anirudh Chauhan Jun 29 '17 at 11:29
  • Cant we do something like this? rng=Range("B1:B3") sht.Range("rng").Value Not sure. need help with the syntax – Anirudh Chauhan Jun 29 '17 at 11:32
  • It does not help. You would have to present the contents of the 3 affected cells here. Then one could talk about it further. – ScriptMan Jun 29 '17 at 12:06
  • The cells contain User IDs like BC1234, CD1234, BCDEFG – Anirudh Chauhan Jun 29 '17 at 13:05
  • @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. – Mathieu Guindon Jun 29 '17 at 13:06
  • 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)) – ScriptMan Jun 29 '17 at 13:18
  • @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 – Anirudh Chauhan Jun 29 '17 at 13:51
  • @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? – Anirudh Chauhan Jun 29 '17 at 13:57
  • 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 Jun 29 '17 at 14:05
  • @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 – Anirudh Chauhan Jun 29 '17 at 14:22
  • Too bad it did not work. In the 6th comment before, the solution you did accept later was already available. – ScriptMan Jun 30 '17 at 07:06
  • @ScriptMan : no worries, scriptman you will always be my hero :) – Anirudh Chauhan Jul 03 '17 at 07:19