16 posts in this topic
set Excel tab color from variable (SOLVED)
Hopefully someone familiar with Excel COM in AutoIt will say "Oh, that's easy, you just..."
Or if someone knows how to set the tab color with the Excel UDF, either way.
specifying the hex directly works...
$oExcelDoc.WorkSheets($Driver).Tab.Color=0x00FF00 declaring the variable directly works...
$Acolor=0x00FF00 $oExcelDoc.WorkSheets($Driver).Tab.Color=$Acolor BUT... if my variable is set by output from functions or read from ini file (which is what i am trying to do) it doesn't apply the color...
$Acolor=IniRead ( "Colors.ini", "A", "Color", "" ) $oExcelDoc.WorkSheets($Driver).Tab.Color=$Acolor
I have quadruple checked that the ini is being read correctly and googled til my eyes popped out and have now gone mad.
How to append data (random) in Excel?
Hi everybody, I'm newbie to learn AutoIt, need some help... The process/ work that I'd like to automate is just for testing purpose for my job.
Searching high and low, I found no script example for writing any random data into excel file.
create combo box dynamically
I'm trying to use GUICtrlCreateCombo but I need it to get the list values from an excel spreadsheet column - that makes it dynamic. My method is to prompt for the Module name (this gives me the application to run the automation test) this also gives me the name of the folder that stores all the spreadsheets.
1.I then get the list of spreadsheet names and use that as input for another list (this can be select 1 or many or all - don't know which control to use...)
2.On selection I then go to the spreadsheets selected and display columns C, E from TestController tab in a further combo box to select 1 or many or all.
3. keep doing point 2 above until all selected in 1 are complete. I'm wondering if I just display as many combo boxes as items selected in point 1 or loop thru them somehow.
Very complex for my little brain - any advice to simplify is greatly appreciated
[SOLVED] _Excel_BookOpen how to connect to already opened Excel Application, without knowing the filepath
Hello dear users!
I have a bit of a problem with my excel script I try to make. My goal is that my script would read through an excelfile's columns and rows, identifiying key words, then I would like to reorganize the rows and colums in the a specified "way", "format" (we get different kind of excels in and i wish to produce a uniform excels from them, the data is the same, so I know that there is an XY column somewhere, I just have to find it, and put it in the right place)
Now I can read values from cells (or range of cells) but only if I know the exact file name and path.
Can I make autoit to somehow attach to the already opened excel? This way I would be able to make the script, so the user just open the excel which he/she wants to format, clicks on go, and viola.
Instead now I'm thinking adding 2 textboxes to my GUI so the user can write down the filename and file path, which isnt that ideal.
My original idea, was to make the script find the "borders" of the filled out excel form, copy it away to the right, delete the original data, reorganize it the way it should be (to the area which it cleared), and thats it. The user can then quickly check if everything is okey, and he/she can delete the copied original data set if its needed.
So in short my problem is, how to connect my script to an excel thats presumaby already opened, without knowing the filename and path?
Thank you for your help! (and sorry if there is already a post about this I havent found it)
_ExcelRangeFind error in line 656
Good day everyone!
I'm having a problem with my underconstruction excel script (again... , sorry haven't used autoIT for a while, pretty sure this is a basic error as well)
If $xls2 = 1 Then $ExcelName = $aWorkBooks $ExcelPath = $aWorkBooks MsgBox($MB_SYSTEMMODAL, "Excel", " Excel file path: " & $ExcelPath) MsgBox($MB_SYSTEMMODAL, "Excel", " Excel file name: " & $ExcelName) EndIf If $xls3 = 1 Then $ExcelName = $aWorkBooks $ExcelPath = $aWorkBooks ;MsgBox($MB_SYSTEMMODAL, "Excel", " Excel file path: " & $ExcelPath) EndIf $ExcelObject = _Excel_BookAttach($ExcelName, "filename") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 2", "Error attaching to '" & $ExcelObject & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 2", "Search by 'filename':" & @CRLF & @CRLF & "Successfully attached to Workbook '" & $ExcelObject & "'." & @CRLF & @CRLF & "Value of cell A2: " & $ExcelObject.Activesheet.Range("A2").Value) $knkod = _Excel_RangeFind($ExcelObject, "knkod") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value 'knkod' (partial match)." & @CRLF & "Data successfully searched.") _ArrayDisplay($knkod, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment") I ask my users, to only run one excel instances while starting my script (so I can develope a simple a script I can, and then upgrade it once I code like i was able to, once again...), thats why I dont use UBound but give an array of 4 for possible running excel detection. If its needed I can easly add to array size for now.
I check for given file extensions and also have an error+exit if I found more then 1 running excel. Of course I know that my code is pretty childish for now.
I use Excel_BookAttach to attach to the excel object, the script succesfully run, and gives back the value of A2 cell (code basicly copy pasted from help file) but then I get an error in the console that reads:
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
"C:\ToolBox\AutoIT\AutoIt3\Include\Excel.au3" (656) : ==> The requested action with this object has failed.:
$aResult[$iIndex] = $oMatch.Name.Name
$aResult[$iIndex] = $oMatch^ ERROR
->17:53:47 AutoIt3.exe ended.rc:1
+>17:53:47 AutoIt3Wrapper Finished.
>Exit code: 1 Time: 24.24
(sorry if i should have wrote this some other way)
I suspect that I screw something up with the workbook/worksheet handling or that $ExcelObject isn't an object somehow. I'm not sure, but IsObj return 1 if i check $excelobject
Edit: Maybe there is some kind of problem with the name of the excel? It has "á" in it's name, could that be a problem?
Thank you for your insight! and sorry for the elementary questions