johnnny Posted June 17, 2017 Share Posted June 17, 2017 Hello, I would like to make my job easier and I was thinking about creating a script in Autoit. But I'm new and I'm still learning. So may i ask for help? A description of the first part over which I'm thinking is to load excel with the current cast of cells, which is dynamic, then load each row into the field and then load each column as a variable in each row. I'm not sure if my thinking is correct. Then I want to paste each variable into a form on the web. Then load the next line and re-enter the form. Do this for selected rows I set, for example when the type is for clothes or for all. Thank you for helping me! exapmle of my table: Spoiler ID type title color price description 1 stuff book green 100 new book 2 clothes t-shirt black 20 bla bla bla 3 stuff car black 250 bla bla bla 4 clothes shirt yellow 20 bla bla bla 5 clothes t-shirt black 30 bli bla blo 6 stuff 7 8 my code: #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> 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, "D:\PRODEJ\seznam.xlsx") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & "D:\PRODEJ\seznam.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf ;$Row = $oExcel.Application.ActivSheet.Row ; this dont work i dont know why ;ConsoleWrite("$Row") $nb_columns = $oExcel.ActiveSheet.UsedRange.Columns.Count ConsoleWrite("$nb_columns" & @CRLF) ; i dont see value of this variable in console $nb_rows = $oExcel.ActiveSheet.UsedRange.Rows.Count Local $array_data_excel[$nb_rows][$nb_columns] Local $i ;Local $j For $i = 1 To $nb_rows - 1 ; start from second row in excel ;For $j = 0 To $nb_columns-1 ;dont know how to use this for dynamical loading column $array_data_excel[$i][0] = $oExcel.Activesheet.Cells($i + 1, 1).Value ;getVar$jValue;here i want to get into variable this $array_data_excel[$i][1] = $oExcel.Activesheet.Cells($i + 1, 2).Value ;getVard$jValue;here i want to get into variable this $array_data_excel[$i][2] = $oExcel.Activesheet.Cells($i + 1, 3).Value ;getVar$jValue;here i want to get into variable this $array_data_excel[$i][3] = $oExcel.Activesheet.Cells($i + 1, 4).Value ;getVar$jValue;here i want to get into variable this $array_data_excel[$i][4] = $oExcel.Activesheet.Cells($i + 1, 5).Value ;getVar$jValue;here i want to get into variable this $array_data_excel[$i][5] = $oExcel.Activesheet.Cells($i + 1, 6).Value ;getVar$jValue;here i want to get into variable this ;Display for Testing _ArrayDisplay($array_data_excel) Next ;Display for Testing _ArrayDisplay($array_data_excel) _Excel_Close($oExcel, True) _Excel_BookClose($oWorkbook, True) Link to comment Share on other sites More sharing options...
benners Posted June 17, 2017 Share Posted June 17, 2017 If you just need an array of the full sheet you can use _Excel_RangeRead() to return the full array. The consolewrite doesn't show the value because you have enclosed the variable in double quotes. This just displays as text then. expandcollapse popup#include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> 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, "D:\test.xlsx") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & "D:\test.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf ;$Row = $oExcel.Application.ActivSheet.Row ; this dont work i dont know why ;ConsoleWrite("$Row") Local $as_RangeRead = _Excel_RangeRead($oWorkbook) ;~ $nb_columns = $oExcel.ActiveSheet.UsedRange.Columns.Count ;~ ConsoleWrite($nb_columns & @CRLF) ; i dont see value of this variable in console ;~ $nb_rows = $oExcel.ActiveSheet.UsedRange.Rows.Count ;~ Local $array_data_excel[$nb_rows][$nb_columns] ;~ Local $i ;~ ;Local $j ;~ For $i = 1 To $nb_rows - 1 ; start from second row in excel ;~ ;For $j = 0 To $nb_columns-1 ;dont know how to use this for dynamical loading column ;~ $array_data_excel[$i][0] = $oExcel.Activesheet.Cells($i + 1, 1).Value ;~ ;getVar$jValue;here i want to get into variable this ;~ $array_data_excel[$i][1] = $oExcel.Activesheet.Cells($i + 1, 2).Value ;~ ;getVard$jValue;here i want to get into variable this ;~ $array_data_excel[$i][2] = $oExcel.Activesheet.Cells($i + 1, 3).Value ;~ ;getVar$jValue;here i want to get into variable this ;~ $array_data_excel[$i][3] = $oExcel.Activesheet.Cells($i + 1, 4).Value ;~ ;getVar$jValue;here i want to get into variable this ;~ $array_data_excel[$i][4] = $oExcel.Activesheet.Cells($i + 1, 5).Value ;~ ;getVar$jValue;here i want to get into variable this ;~ $array_data_excel[$i][5] = $oExcel.Activesheet.Cells($i + 1, 6).Value ;~ ;getVar$jValue;here i want to get into variable this ;~ ;Display for Testing ;~ _ArrayDisplay($array_data_excel) ;~ Next ;Display for Testing _ArrayDisplay($as_RangeRead) ;~ _ArrayDisplay($array_data_excel) _Excel_BookClose($oWorkbook, True) _Excel_Close($oExcel, True) Link to comment Share on other sites More sharing options...
johnnny Posted June 18, 2017 Author Share Posted June 18, 2017 Thank you for your insert. But, i am not sure if i need array of all asheet in this point. I need work with every row in loop and all field in row get into different variables for next work with them. and this does not work for me as well: ConsoleWrite('nb_columns' & @CRLF) ; i dont see value of this variable in console Thank you Link to comment Share on other sites More sharing options...
benners Posted June 18, 2017 Share Posted June 18, 2017 2 hours ago, johnnny said: I need work with every row in loop and all field in row Sounds like the full sheet to me. 2 hours ago, johnnny said: and this does not work for me as well: ConsoleWrite('nb_columns' & @CRLF) ; i dont see value of this variable in console I know, I explained why in my first post. IMO it is better to call RangeRead once and then process the values you want from the array than use multiple calls for each row\column. The code is cleaner than nested for loops Based on the sheet in your post and the info provided (and without too much thinking), I would start with something like this, unless I'm not understanding your requirements fully. expandcollapse popup#include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> ; set constants for the columns that RangeRead returns ; if your final sheet is different from the one you added in your ; post these will need to be changed to represent the columns you want Global Enum _ $ID_COL, _ $TYPE_COL, _ $TITLE_COL, _ $COLOR_COL, _ $PRICE_COL, _ $DESCRIPTION_COL Local $oExcel = _Excel_Open() If @error Then Close("Error creating the Excel application object.", @error, @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, "D:\test.xlsx") ; replace with your file path If @error Then Close("Error opening workbook '" & "D:\test.xlsx'.", @error, @extended) ; read the full sheet to an array Local $as_RangeRead = _Excel_RangeRead($oWorkbook) If @error Then Close("Error reading range to an array", @error, @extended) ; example of getting the values from the array and desired column RangeReadArray_GetValues($as_RangeRead, $DESCRIPTION_COL) Close() ; example to get each field value Func RangeReadArray_GetValues(ByRef $as_Array, $i_Column) For $i = 1 To UBound($as_Array) - 1 MsgBox(0, 'Row: ' & $i + 1 & ' Column: ' & $i_Column + 1, 'Value: ' & $as_Array[$i][$i_Column]) Next EndFunc ;==>RangeReadArray_GetValues Func Close($s_ErrorMsg = '', $i_Error = 0, $i_Extended = 0) If $s_ErrorMsg Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", _ $s_ErrorMsg & @CRLF & _ "@error = " & $i_Error & ", @extended = " & $i_Extended) _Excel_BookClose($oWorkbook, True) _Excel_Close($oExcel, True) Exit EndFunc ;==>Close Link to comment Share on other sites More sharing options...
johnnny Posted June 18, 2017 Author Share Posted June 18, 2017 5 hours ago, benners said: Sounds like the full sheet to me. I need rows, cause i will work with rows. Sometimes will need only one row, sometimes range of rows. I want to have it adjustable. Quote I know, I explained why in my first post. i tryed double quotes, simple quotes. Nothing works for me. Quote IMO it is better to call RangeRead once and then process the values you want from the array than use multiple calls for each row\column. The code is cleaner than nested for loops agreed Quote Based on the sheet in your post and the info provided (and without too much thinking), I would start with something like this, unless I'm not understanding your requirements fully. i see, its look so better then my. I just wanted rows, not columns. Tryed to do for rows, but i am little confused and dont uderstand very well $DESCRIPTION_COL variable. I guess, that if i do something like this: ... i will get first column, but no row. RangeReadArray_GetValues($as_RangeRead, $DESCRIPTION_COL = 0) I will try describe my problem more... For example i have row in my excel and form on website where i want to put these fields. In one loop just get one row from excel and fill form on web from these variables. Or for second row or third row depence what i will need. In the future there will be about 50 rows, and i dont want to fill form manualy. So my first thougt was get excel values for each row into separate variables. Can you just please do this for rows and for two maybe? I can see the logical step better maybe. Thank you very much Link to comment Share on other sites More sharing options...
benners Posted June 18, 2017 Share Posted June 18, 2017 3 hours ago, johnnny said: i tryed double quotes, simple quotes. Nothing works for me To get the ConsoleWrite to work with the variable, you remove any quotes, single or double from around it or it just gets written as text and not the value that the variable holds. ConsoleWrite($nb_columns & @CRLF) Now as to the row selection. This works. It looks a lot because I added a gui so you can select the rows that are available in the array. I don't know how you were going to select the rows so just made a way up expandcollapse popup#include <Array.au3> #include <ButtonConstants.au3> #include <Excel.au3> #include <GUIConstantsEx.au3> #include <GuiListView.au3> #include <ListViewConstants.au3> #include <MsgBoxConstants.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> Opt("GUIOnEventMode", 1) Global $g_idListview Global $g_oExcel = _Excel_Open() If @error Then Close("Error creating the Excel application object.", @error, @extended) Global $g_oWorkbook = _Excel_BookOpen($g_oExcel, "D:\test.xlsx") ; replace with your file path If @error Then Close("Error opening workbook '" & "D:\test.xlsx'.", @error, @extended) ; read the full sheet to an array Global $g_as_RangeRead = _Excel_RangeRead($g_oWorkbook) If @error Then Close("Error reading range to an array", @error, @extended) Draw_GUI() ; draw a gui to allow row selection Func Draw_GUI() GUICreate("", 213, 265, 1011, 460) GUISetOnEvent($GUI_EVENT_CLOSE, 'Close_Clicked') $g_idListview = GUICtrlCreateListView("", 24, 40, 58, 206, $LVS_SMALLICON, BitOR($WS_EX_CLIENTEDGE, $LVS_EX_CHECKBOXES)) Listview_Load() ; load the listview with the row numbers GUICtrlCreateButton("OK", 104, 56, 75, 25) GUICtrlSetOnEvent(-1, 'OK_Clicked') GUICtrlCreateButton("Close", 104, 88, 75, 25) GUICtrlSetOnEvent(-1, 'Close_Clicked') GUICtrlCreateLabel("Select the ID rows", 10, 8, 129, 20, $SS_CENTER) GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif") GUISetState(@SW_SHOW) While 1 Sleep(100) WEnd EndFunc ;==>Draw_GUI Func Listview_Load() ; load the ID column into the listview For $i = 1 To UBound($g_as_RangeRead) - 1 GUICtrlCreateListViewItem($g_as_RangeRead[$i][0], $g_idListview) ; add an item to the listview for each row in the array Next EndFunc ;==>Listview_Load Func OK_Clicked() Local $s_Row = '' ; get the rows that you want to get the values for For $i = 1 To _GUICtrlListView_GetItemCount($g_idListview) If _GUICtrlListView_GetItemChecked($g_idListview, $i - 1) Then $s_Row &= _GUICtrlListView_GetItemText($g_idListview, $i - 1) & '|' Next $s_Row = StringTrimRight($s_Row, 1) ; remove the trailing delimiter ; get the array values for the selected rows RangeReadArray_GetValues(StringSplit($s_Row, '|')) ; send an array of the selected rows to the function EndFunc ;==>OK_Clicked Func RangeReadArray_GetValues($as_Row) Local $i_Row = 0 ; loop through the array getting the column values for the selected rows For $i = 1 To $as_Row[0] ; loop through the array of rows you selected $i_Row = $as_Row[$i] ; this is the current row you are reading it is the ID number NOT the Excel row number For $j = 1 To UBound($g_as_RangeRead, $UBOUND_COLUMNS) - 1 ; loop thropugh the rangeread array columns ignoring the ID column ; show the values. When you write your code for entering into the web page put it here. $g_as_RangeRead[$i][$j] is the column value MsgBox(0, 'Row: ' & $i_Row, $g_as_RangeRead[0][$j] & ': ' & $g_as_RangeRead[$i_Row][$j]) Next Sleep(20) Next EndFunc ;==>RangeReadArray_GetValues Func Close_Clicked() Close() EndFunc ;==>Close_Clicked Func Close($s_ErrorMsg = '', $i_Error = 0, $i_Extended = 0) GUIDelete() If $s_ErrorMsg Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", _ $s_ErrorMsg & @CRLF & _ "@error = " & $i_Error & ", @extended = " & $i_Extended) _Excel_BookClose($g_oWorkbook, True) _Excel_Close($g_oExcel, True) Exit EndFunc ;==>Close Link to comment Share on other sites More sharing options...
johnnny Posted June 18, 2017 Author Share Posted June 18, 2017 12 minutes ago, benners said: To get the ConsoleWrite to work with the variable, you remove any quotes, single or double from around it or it just gets written as text and not the value that the variable holds. ConsoleWrite($nb_columns & @CRLF) Now as to the row selection. This works. It looks a lot because I added a gui so you can select the rows that are available in the array. I don't know how you were going to select the rows so just made a way up expandcollapse popup#include <Array.au3> #include <ButtonConstants.au3> #include <Excel.au3> #include <GUIConstantsEx.au3> #include <GuiListView.au3> #include <ListViewConstants.au3> #include <MsgBoxConstants.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> Opt("GUIOnEventMode", 1) Global $g_idListview Global $g_oExcel = _Excel_Open() If @error Then Close("Error creating the Excel application object.", @error, @extended) Global $g_oWorkbook = _Excel_BookOpen($g_oExcel, "D:\test.xlsx") ; replace with your file path If @error Then Close("Error opening workbook '" & "D:\test.xlsx'.", @error, @extended) ; read the full sheet to an array Global $g_as_RangeRead = _Excel_RangeRead($g_oWorkbook) If @error Then Close("Error reading range to an array", @error, @extended) Draw_GUI() ; draw a gui to allow row selection Func Draw_GUI() GUICreate("", 213, 265, 1011, 460) GUISetOnEvent($GUI_EVENT_CLOSE, 'Close_Clicked') $g_idListview = GUICtrlCreateListView("", 24, 40, 58, 206, $LVS_SMALLICON, BitOR($WS_EX_CLIENTEDGE, $LVS_EX_CHECKBOXES)) Listview_Load() ; load the listview with the row numbers GUICtrlCreateButton("OK", 104, 56, 75, 25) GUICtrlSetOnEvent(-1, 'OK_Clicked') GUICtrlCreateButton("Close", 104, 88, 75, 25) GUICtrlSetOnEvent(-1, 'Close_Clicked') GUICtrlCreateLabel("Select the ID rows", 10, 8, 129, 20, $SS_CENTER) GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif") GUISetState(@SW_SHOW) While 1 Sleep(100) WEnd EndFunc ;==>Draw_GUI Func Listview_Load() ; load the ID column into the listview For $i = 1 To UBound($g_as_RangeRead) - 1 GUICtrlCreateListViewItem($g_as_RangeRead[$i][0], $g_idListview) ; add an item to the listview for each row in the array Next EndFunc ;==>Listview_Load Func OK_Clicked() Local $s_Row = '' ; get the rows that you want to get the values for For $i = 1 To _GUICtrlListView_GetItemCount($g_idListview) If _GUICtrlListView_GetItemChecked($g_idListview, $i - 1) Then $s_Row &= _GUICtrlListView_GetItemText($g_idListview, $i - 1) & '|' Next $s_Row = StringTrimRight($s_Row, 1) ; remove the trailing delimiter ; get the array values for the selected rows RangeReadArray_GetValues(StringSplit($s_Row, '|')) ; send an array of the selected rows to the function EndFunc ;==>OK_Clicked Func RangeReadArray_GetValues($as_Row) Local $i_Row = 0 ; loop through the array getting the column values for the selected rows For $i = 1 To $as_Row[0] ; loop through the array of rows you selected $i_Row = $as_Row[$i] ; this is the current row you are reading it is the ID number NOT the Excel row number For $j = 1 To UBound($g_as_RangeRead, $UBOUND_COLUMNS) - 1 ; loop thropugh the rangeread array columns ignoring the ID column ; show the values. When you write your code for entering into the web page put it here. $g_as_RangeRead[$i][$j] is the column value MsgBox(0, 'Row: ' & $i_Row, $g_as_RangeRead[0][$j] & ': ' & $g_as_RangeRead[$i_Row][$j]) Next Sleep(20) Next EndFunc ;==>RangeReadArray_GetValues Func Close_Clicked() Close() EndFunc ;==>Close_Clicked Func Close($s_ErrorMsg = '', $i_Error = 0, $i_Extended = 0) GUIDelete() If $s_ErrorMsg Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", _ $s_ErrorMsg & @CRLF & _ "@error = " & $i_Error & ", @extended = " & $i_Extended) _Excel_BookClose($g_oWorkbook, True) _Excel_Close($g_oExcel, True) Exit EndFunc ;==>Close You are awesome! Thank you for this moment. I will continue and let you know later. Nice rest of weekend Link to comment Share on other sites More sharing options...
benners Posted June 18, 2017 Share Posted June 18, 2017 No worries. Post back if there's any issues Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now