Create multiple records in the application using data from excel sheet rows

Hi Anil,

Above function is reading the excel file, but it is reading the last row

Take a closer look at the following part of your code:

For i = 1 to 4

  s = ""

  For j = 1 to 15

    s = s + VarToString(Excel.Cells(i, j)) + "#"

  Next

Next

Here, you reset the s variable to an empty string on each row iteration. That's why, it ends up only with last row data at the end of the loop. (You can see this if you step through the loop under a debugger.)



Your code should be as follows:

s = ""



For i = 1 to 4

  For j = 1 to 15

    s = s + VarToString(Excel.Cells(i, j)) + "#" 

  Next

Next

Actually, I would recommend integrating the AUT operations and the data reading loop, so that you don't have to do data join and split. For example, you could use something like this instead:

Sub createbudget

  Dim Rightframe, Excel, i



  Set Excel = Sys.OleObject("Excel.Application")

  Call Excel.Workbooks.Open("E:\Budget.xlsx")

  Delay 1000



  Set Rightframe = sys.process.page("http//").document.rightframe



  For i = 1 to 4

    RightFrame.document.all.Item("ddlProj").ClickItem( VarToString(Excel.Cells(i, 1)) )

    RightFrame.document.all.Item("ddlSubProj").ClickItem( VarToString(Excel.Cells(i, 2)) )

  Next



  ' Closes the driver

  Call Excel.ActiveWorkbook.Save

  Call Excel.Workbooks.Close

End Sub

Hi Helen,



Thank you very much for the code you provided.



I need some more suggestion to the code you provided below...

1) Instead of using the row loop directly in the subroutine, I want to make this loop run in a separate subroutine and call the subroutine to input the data into the application.

2) Is it possible to use loop for column also in the below code? For column value; instead of giving excel cell reference as 1, 2 etc, can we give J as reference, the way it is done for row, taking 'i'?

3) OR can we use the string to store row and column values and call the string in the subroutine to input the data, which I use to do in my code provided in my first mail at the top?



Could you please help me in this regard?

VBScript

Sub createbudget

  Dim Rightframe, Excel, i

  Set Excel = Sys.OleObject("Excel.Application")

  Call Excel.Workbooks.Open("E:\Budget.xlsx")

  Delay 1000

  Set Rightframe = sys.process.page("http//").document.rightframe

  For i = 1 to 4

    RightFrame.document.all.Item("ddlProj").ClickItem( VarToString(Excel.Cells(i, 1)) )

    RightFrame.document.all.Item("ddlSubProj").ClickItem( VarToString(Excel.Cells(i, 2)) )

  Next

  ' Closes the driver

  Call Excel.ActiveWorkbook.Save

  Call Excel.Workbooks.Close

End Sub

Hi Helen...



Any suggestions please?

Hi Anil,

1) Instead of using the row loop directly in the subroutine, I want to make this loop run in a separate subroutine and call the subroutine to input the data into the application.

I'm not sure I understand your entire scenario. Do you mean something like in the code below? If not, could you clarify what you need?

' Main routine with row loop

Sub createbudget

  Dim Excel, i



  Set Excel = Sys.OleObject("Excel.Application")

  Call Excel.Workbooks.Open("E:\Budget.xlsx")

  Delay 1000



  For i = 1 to 4

    Call InputData( Excel.Rows(i) )   ' <--- Data input subroutine

  Next



  Call Excel.ActiveWorkbook.Save

  Call Excel.Workbooks.Close

End Sub



Sub InputData(ExcelRow)

  Dim Rightframe

  Set Rightframe = sys.process.page("http//").document.rightframe

  RightFrame.document.all.Item("ddlProj").ClickItem( VarToStr(ExcelRow.Cells(1)) )

  RightFrame.document.all.Item("ddlSubProj").ClickItem( VarToStr(ExcelRow.Cells(1)) )

End Sub

2) Is it possible to use loop for column also in the below code? For column value; instead of giving excel cell reference as 1, 2 etc, can we give J as reference, the way it is done for row, taking 'i'?

As you are feeding column values into different objects -- Item("ddlProj") and Item("ddlSubProj") -- then to make a column loop, you'll need to somehow associate these objects with column indexes 1 and 2. For example, you could store the object names in an array so that array element indexes match column indexes. In this case, you'll be able to do something like this:

Sub InputData(ExcelRow)

  Dim Rightframe, ItemNames, Item, j

  ItemNames = Array("ddlProj", "ddlSubProj")



  Set Rightframe = sys.process.page("http//").document.rightframe

  For j = 0 To 2

    Set Item = Rightframe.document.all.Item( ItemNames(j) )

    Call Item.ClickItem( VarToStr(ExcelRow.Cells(j+1)) )  ' j+1, because array elements are numbered from 0 but Excel columns from 1

  Next

End Sub

(On the other hand, this adds more complexity and reduces code readability.)

3) OR can we use the string to store row and column values and call the string in the subroutine to input the data, which I use to do in my code provided in my first mail at the top?

Of course, you can stick with your original code. :) I just pointed out that using join and split may not be the most effective way to pass data between subroutines, but it's really up to you.



Anyway, I hope you'll find the above useful. Good luck with your automation!

Hi Helen,



You are super... Thank you very much for all the valuable suggestion!! :)