Moving information between Excel spreadsheets

[m4stersplint3r]

Hi everyone,

This is my first time posting here, I've used AutoIt for a few things at work. I most recently used it to copy 4 cells from the second row to 4 cells in different rows and some shared columns in a different spreadsheet. After copying these 4 cells it then takes a 3 cells back to the original sheet adding them to the second row, moving this row to the end of the list so row 3 now becomes row 2 and repeats until it cycles through all the rows and the original row 2 is back at the top with 3 new columns of information. Currently it takes about 5 or 6 minutes to run through 400 rows, would using an array in some way make this process any faster? I think it's about as good as it's gonna get considering what it has to do. Any input would be appreciated.

[water]

Calling Excel to read a single cell in a loop takes a lot of time. Read all used cells with a single call to _Excel_RangeRead, process the array and then write the resulting array with a single call of _Excel_RangeWrite back to Excel.
This will run in a few seconds.

[m4stersplint3r]

How would I call the items in the array one row at a time while writing the results of that row to a new array?

[Subz]

You can use a loop, or transpose, can you post examples i.e. the before and after results you want?

[m4stersplint3r]

#include <Excel.au3>
#include <AutoItConstants.au3>
#include <MsgBoxConstants.au3>
HotKeySet ( "^{1}", "Run_Script")

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 $list = _Excel_BookOpen($oExcel, @ScriptDir & "\List.xlsx")
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 $Tool = _Excel_BookOpen($oExcel, @ScriptDir & "\CarePlan Template Tool Reverse.xlsx")
Local $iRows = $list.ActiveSheet.UsedRange.Rows.Count
Dim $count = 0
;=============================================================================================================
$list.Worksheets(1).Activate
$Tool.Worksheets(1).Activate
;=============================================================================================================
  If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
 EndIf

func Run_Script()
Do
$Start_Date = _Excel_RangeRead ($list, Default, "C2", 3) ; Pulls Start_Date from list
$End_Date = _Excel_RangeRead ($list, Default, "D2", 3) ; Pulls End_Date from list
$Units_Total = _Excel_RangeRead ($list, Default, "I2", 3) ; Pulls Units_Total from list
$Units_Used = _Excel_RangeRead ($list, Default, "J2", 3) ; Pulls Units_Used from list
_Excel_RangeWrite ($Tool, Default, $Start_Date, "P6") ; Puts Start_Date in tool
_Excel_RangeWrite ($Tool, Default, $End_Date, "P7") ; Puts End_Date in tool
_Excel_RangeWrite ($Tool, Default, $Units_Total, "P8") ; Puts Units_Total in tool
_Excel_RangeWrite ($Tool, Default, $Units_Used, "S5") ; Puts Units_Used in tool
$Units_Remaining = _Excel_RangeRead ($Tool, Default, "S7", 3) ; Pulls Units_Remaining from Tool
$Template1Weekly = _Excel_RangeRead ($Tool, Default, "V5", 3) ; Pulls Template 1 weekly units from Tool
$Template2Weekly = _Excel_RangeRead ($Tool, Default, "V6", 3) ; Pulls Template 1 weekly units from Tool
_Excel_RangeWrite ($list, Default, $Units_Remaining, "K2") ; Puts Units_Remaining in list
_Excel_RangeWrite ($list, Default, $Template1Weekly, "M2") ; Puts Units_Remaining in list
_Excel_RangeWrite ($list, Default, $Template2Weekly, "N2") ; Puts Units_Remaining in list
_Excel_RangeCopyPaste($list.ActiveSheet, "2:2", "A" & ($iRows + 1)) ; Copies Row 2 and Pastes to the last row
_Excel_RangeDelete($list.ActiveSheet, "2:2") ; Deletes Row 2 after moving to last row
$count += 1 ; Adds 1 to the count which is number of times function will run
Until $count = ($iRows - 1)
endfunc

while 1
sleep(5)
wend

 

 

This is what I came up with. The tricky part is that when copying from list, the four values from list need to be in the tool before the tool can give me the cells I need to bring back to the list. So all 400 rows can't be moved over at once, they have to go one by one. The tool does a bunch of calculations based on dates and units brought over from the list.

[water]

Drop the second

Local $oExcel = _Excel_Open()

You can have two open WorkBooks with the same Excel application.

[water]

Could you please upload an example of how the WorkBook looks when you start processing and how it looks after processing has finished (means: two separate files).
So we have something to play with.

[m4stersplint3r]

Here are tList_before.xlsxList_after.xlsxhe requested files, sorry it took so long.

[faustf]

please use  tag for  code  <>    and  tidy for order them before past  

in scite ctrl+t

expandcollapse popup

#include <Excel.au3>
#include <AutoItConstants.au3>
#include <MsgBoxConstants.au3>
HotKeySet("^{1}", "Run_Script")


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 $list = _Excel_BookOpen($oExcel, @ScriptDir & "\List.xlsx")
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 $Tool = _Excel_BookOpen($oExcel, @ScriptDir & "\CarePlan Template Tool Reverse.xlsx")
Local $iRows = $list.ActiveSheet.UsedRange.Rows.Count
Dim $count = 0
;=============================================================================================================
$list.Worksheets(1).Activate
$Tool.Worksheets(1).Activate
;=============================================================================================================
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

Func Run_Script()
    Do
        $Start_Date = _Excel_RangeRead($list, Default, "C2", 3) ; Pulls Start_Date from list
        $End_Date = _Excel_RangeRead($list, Default, "D2", 3) ; Pulls End_Date from list
        $Units_Total = _Excel_RangeRead($list, Default, "I2", 3) ; Pulls Units_Total from list
        $Units_Used = _Excel_RangeRead($list, Default, "J2", 3) ; Pulls Units_Used from list
        _Excel_RangeWrite($Tool, Default, $Start_Date, "P6") ; Puts Start_Date in tool
        _Excel_RangeWrite($Tool, Default, $End_Date, "P7") ; Puts End_Date in tool
        _Excel_RangeWrite($Tool, Default, $Units_Total, "P8") ; Puts Units_Total in tool
        _Excel_RangeWrite($Tool, Default, $Units_Used, "S5") ; Puts Units_Used in tool
        $Units_Remaining = _Excel_RangeRead($Tool, Default, "S7", 3) ; Pulls Units_Remaining from Tool
        $Template1Weekly = _Excel_RangeRead($Tool, Default, "V5", 3) ; Pulls Template 1 weekly units from Tool
        $Template2Weekly = _Excel_RangeRead($Tool, Default, "V6", 3) ; Pulls Template 1 weekly units from Tool
        _Excel_RangeWrite($list, Default, $Units_Remaining, "K2") ; Puts Units_Remaining in list
        _Excel_RangeWrite($list, Default, $Template1Weekly, "M2") ; Puts Units_Remaining in list
        _Excel_RangeWrite($list, Default, $Template2Weekly, "N2") ; Puts Units_Remaining in list
        _Excel_RangeCopyPaste($list.ActiveSheet, "2:2", "A" & ($iRows + 1)) ; Copies Row 2 and Pastes to the last row
        _Excel_RangeDelete($list.ActiveSheet, "2:2") ; Deletes Row 2 after moving to last row
        $count += 1 ; Adds 1 to the count which is number of times function will run
    Until $count = ($iRows - 1)
EndFunc   ;==>Run_Script


While 1
    Sleep(5)
WEnd

 

Edited August 7, 2018 by faustf

[water]

I'm back from vacation so now have some spare time to play with AutoIt again.

Does the above script solve your problem?