Excel Single Cell Loop Across then Down

[jtaft]

I am looking for some help copying values from an Excel file which contain address information in seprte Colums and seprate cells.
 

The loop should first copy content from A1 (Lets say this is Zip Code) (I will then paste this date into Web Part)
Then copy data from B1 (Lets say this is Address)  (I will then paste this date into Web Part)
Then Copy from C1 (Lets say this is house number)  (I will then paste this date into Web Part)

Once complete the loop should then move to A2,B2,C2 excendra.

 

I am currently able to open my file using

[autoit]

Local $oExcel = _Excel_Open()
 If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
 Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\SFDC_ZIP.xlsx")
 If @error Then
  MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\SFDC_ZIP.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
  _Excel_Close($oExcel)
  Exit
 EndIf

I am then copy the content from the first cell
Local $sResult = _Excel_RangeCopyPaste($oWorkbook.Activesheet, "A1")

[/Autoit]

Then I start a loop

Do
        Local $sData = ClipGet()
                    (Bunch of code in between- calling web part)

        $sData = ClipGet()
        $sResult = $sResult + 1

Until $sResult = 4
    

 

Edited August 26, 2018 by jtaft

[water]

And your question is?

[jtaft]

Question is how do I make this work!

I am looking for some help copying values from an Excel file which contain address information in seprte Colums and seprate cells.
 

The loop should first copy content from A1 (Lets say this is Zip Code) (I will then paste this date into Web Part)
Then copy data from B1 (Lets say this is Address)  (I will then paste this date into Web Part)
Then Copy from C1 (Lets say this is house number)  (I will then paste this date into Web Part)

Once complete the loop should then move to A2,B2,C2 excendra.

Edited August 24, 2018 by jtaft

[FrancescoDiMuro]

@jtaft

Why don't you read all the used range of your data, and work with it ( faster, easier ) ?

[jtaft]

Thanks, does that mean to use an array using _Excel_Rangread?  I tried starting with that and was able to read my data to an array, however was struggling with getting my script to read across the top set of cells and then start over on the second set of cells.

 

does anyone have a snippet of code I could try working with?  

[water]

Something like this:

$aExcelData = _ExceL_RangeRead(...)
For $iRow = 0 To UBound($aExcelData, 1) - 1
    For $iCol = 0 To UBound($aExcelData, 2) - 1
        ; Process $aExcelData[$iRow][$iCol] here. It does row by row and column by column in each row.
    Next
Next

 

[jtaft]

Thanks water.  I will give this a shot.  Do I put this in my "Do" loop?  Many thanks!

[water]

No, it replaces everything after _Excel_Open and _Excel_BookOpen.

[jtaft]

Ok, So far I have been able to get it to read the excel file into the array.

Were you say
     ; Process $aExcelData[$iRow][$iCol] here. It does row by row and column by column in each row.
Not sure what to put here.

I have added
$aExcelData[$iRow][$iCol]  = "Row: " & $aExcelData & " - Col: " & $aExcelData (Here is were I am not sure what to put)

Also at what point and how to I copy the first cell? (in this case looks like (Col0)

[FrancescoDiMuro]

3 minutes ago, jtaft said:

Also at what point and how to I copy the first cell? (in this case looks like (Col0)

What do you mean?

When you have used _Excel_RangeRead(), you have already copied the content of the range you specified in the function, in an array.

So, now, it depends from what you have to do with that data, and it's about your expected result

[jtaft]

hmmmm. Thanks Francesco,

I see it is copying the entire content into the array (Haven't worked with arrays before)
The need is to basically go back and forth from the excel file copying the date from cell A1 going back to the web page and pasting it into a field, go back to the excel get the date from A2 go back to the we b page and past the data into a field on the web page and repeat this process through cell A3 and A4.  Once complete the process needs to repeat starting with B1-B4and so on,until it has completed the entire excelfile.

In this case the array! (hence my question about copying the data)

I was trying todo this with _Excel_RangeCopyPaste which I was able to only copy the data from A1 and notable to figure out the loop.

I do like the idea of the array as it will be much faster.......

Thanks

 

[jtaft]

Here is my code that I am working with that might help to show what I am trying to do so yo can point me in the right direction of what I am doing wrong!!!

 

expandcollapse popup

#include <MsgBoxConstants.au3>
#include <File.au3>
#include <IE.au3>
#include <Array.au3>
#include <Excel.au3>
#include <Array.au3>

;Local $File = Winactivate ("SFDC_ZIP.xlsx - Excel")
;Local $SFDC = WinWaitActive("Salesforce - Unlimited Edition")



; Launch and Login to SFDC
SFDC()

Sleep(8000)

If WinActive("Salesforce - Unlimited Edition") Then
        WinSetState("Salesforce - Unlimited Edition","",@SW_MAXIMIZE)

        ;Send("{Tab 8}")
ElseIf WinActive("Philips Access Management - Internet Explorer") Then
        ;WinWaitActive("Philips Access Management - Internet Explorer")
    ;Sleep(3000)
    Send("{Tab 8}")
    Sleep(3000)
    Send("{Tab}")
    Sleep(3000)
    Send("{Tab}")
    Send("{Enter}")
    WinWaitActive("Salesforce - Unlimited Edition")
    WinSetState("Salesforce - Unlimited Edition","",@SW_MAXIMIZE)
    Send("{Tab 8}")
EndIf


    Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\SFDC_ZIP.xlsx")
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\SFDC_ZIP.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)

        $aExcelData = _ExceL_RangeRead($oWorkbook, Default, Default, 1)
            For $iRow = 0 To UBound($aExcelData, 1) -1
                For $iCol = 0 To UBound($aExcelData, 2) -1
                ConsoleWrite($aExcelData[$iRow][$iCol] & @CRLF)

        Sleep (2000)
        MouseClick ("Left",406, 42, 1)
        ;Send ("URL")
        Sleep(2000)
        Send("^v") ; Paste Account ID
        Send("{ENTER}")
        Sleep(5000)
        Send ("{SHIFTDOWN}")
        Send ("{DOWN 6}")
        Send ("{SHIFTUP}")
        Sleep (1000)
        MouseClick ("Left", 704, 220,2) ;Selects Zip Code
        ;Send ("^c"); Coppies Zip Code
        Send("{Tab 13}")
        Send ("{Tab}") ; Selects Postal Code in Trilium
        Send("^v") ;Paste Zip Code in Trilium
        Send ("{SHIFTDOWN}")
        Send ("{Tab 3}")
        Send ("{SHIFTUP}")
        Send ("{ENTER}") ;Executes the AutoPopulate in Trilium
        MouseClickDrag ("Left", 570, 210, 768, 210) ; Select Address
        ;Send ("^c"); Coppies address
        Send("{Tab 17}")
        Send("^v");Paste the street in trilium
        MouseClick ("Left", 547, 210,2);Selects the address number
        ;Send ("^c"); Coppies the address number
        Send("{Tab 18}")
        Send("^v"); Paste the House Number in Trilium
        Send ("{Tab 4}") ;Highlights the box Copy to Home Address in trilium
        Send ("{SPACE}") ;Checks the box Copy to Home Address in trilium
        Send ("{Tab 3}") ;Highlights the box Copy to Shipping Address in trilium
        Send ("{SPACE}") ;Checks the box Copy to Shipping Address in trilium
        Sleep (1000)
        Send ("{Tab 3}") ;Highlights the Save button in trilium
        Send ("{ENTER}") ;Selects the save button in trilium
        Sleep (3000)

            ;$aExcelData [$iRow][$iCol] = ;"Row: " & $aExcelData & " - Col: " & $aExcelData

            Next
                Next
        Exit
    EndIf


Func SFDC()
    ;_IECreate("https://philipsb2c.my.salesforce.com/home/home.jsp ", 0, 1, 1, 1)
    EndFunc   ;==>SFDC

 

SFDC_BOT.au3

[FrancescoDiMuro]

@jtaft

Could you please provide a reproducible .xlsx file to play with? 

And, about IE Automation, I higly reccomend you to use this or one of these

[jtaft]

ok, attaches a couple files! 
One with screen shots of what I am trying to do
Second is xls with
A1 = Account Id to past in url field to pull up account
A2 = the Zip Code
A3 = The Street Name
A4 = is the house Number

I realize the script will need to be reworked once I get the loop working!

Thanks for you help to All!!

 

Address Validation Steps.docx

SFDC_ZIP.xlsx

[FrancescoDiMuro]

Hi @jtaft
I saw that you are trying to automate something that doesn't provide information with Au3Info tool; but, what about source code?
If you right click on the wepbage, what can you take from there?
By the way, here you are a little hint to do what you're trying to do

Excel_Sample.zip

Cheers

[jtaft]

 

Thanks for the help on this! I have been at this all day and need to come back to the Well of knowledge!

 have been able to get the URL to open and pass in the data from the Array [0]
I then put in some code to bring focus to the SFDC Imbedded Servlet (Trillium Address Lookup)

If I view the source of the Postal Code text box, it returns the following code
I have tried bringing focus to this field using _IE UDFs with no luck
 

<INPUT role=textbox id=pageId:form:pgblock:pgblocksec:pgblocksecitem1:theTextInput1 class=ui-autocomplete-input maxLength=20 name=pageId:form:pgblock:pgblocksec:pgblocksecitem1:theTextInput1 autocomplete="off" jQuery17106472765921748174="2">

I then tried just again using mouse clicks and sends to select the Postal Code box before the Zip code array fired and figured it would have at least populated the field with the ZIP..... But it did not

expandcollapse popup

; Navigate to all IDs, and insert the required information
                For $i = 0 To UBound($arrResult) - 1

                    ; Building URL
                    $strCompleteURL = $strPartialURL & $arrResult[$i][0]


                    ; Navigate to URL
                    _IENavigate($objIE, $strCompleteURL)
                    ConsoleWrite("URL = " & $strCompleteURL & @CRLF)
                    WinSetState("Person Account:","",@SW_MAXIMIZE)
                    Send ("{SHIFTDOWN}")
                    Send ("{DOWN 10}")
                    Send ("{SHIFTUP}")
                    Sleep (1000)
                    MouseClick("Left",819, 590,1) ; Activates Trillium SFDC Servlet
                    Sleep (1000)

                     Send ("{Tab 1}") ;Selects postal code in SFDC Servlet
                    Sleep (1000)
                    ; Insert the ZIP Code
                    ConsoleWrite("Postal Code = " & $arrResult[$i][1] & @CRLF)
                       ;****Expecting the Zip code to write to postal code, ut it is not*****


                    Sleep(20000)

                    ; Insert the Street Name
                    ConsoleWrite("Street Name = " & $arrResult[$i][2] & @CRLF)

                    ;Sleep (20000)
                    ; Some Action

                    ; Insert the House Number
                    ConsoleWrite("House Number = " & $arrResult[$i][3] & @CRLF & @CRLF)
                    ; Some Action

                Next
            EndIf
        EndIf
    EndIf

Any further insight would be helpful and greatly appreciated!!!!!
Thanks

[jtaft]

Not the cleanest, but I was able to get this working!!!!
Thanks FrancescoDiMuro for the foundation and pointers!!!!
 

Not the cleanest because I still need to use Send Commands (Better than mouse clicks)

But wrote the values from the array like so:

 

;; Insert the ZIP Code
                    $zip = $arrResult[$i][1]
                    ConsoleWrite("ZIP Code = " & @CRLF)
                    Send ( ""&$zip)
                    Sleep (1000)

[FrancescoDiMuro]

@jtaft

Happy to see that!

By the way, don't stop "searching" for a better solution about automation commands.

There are several UDF about that.

Try them, and see if you can improve your script even more

Cheers