Jump to content

Search the Community

Showing results for tags 'excel'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • General
    • Announcements and Site News
    • Administration
  • AutoIt v3
    • AutoIt Help and Support
    • AutoIt Technical Discussion
    • AutoIt Example Scripts
  • Scripting and Development
    • Developer General Discussion
    • Language Specific Discussion
  • IT Administration
    • Operating System Deployment
    • Windows Client
    • Windows Server
    • Office


  • AutoIt Team
    • Beta
    • MVP
  • AutoIt
    • Automation
    • Databases and web connections
    • Data compression
    • Encryption and hash
    • Games
    • GUI Additions
    • Hardware
    • Information gathering
    • Internet protocol suite
    • Maths
    • Media
    • PDF
    • Security
    • Social Media and other Website API
    • Windows
  • Scripting and Development
  • IT Administration
    • Operating System Deployment
    • Windows Client
    • Windows Server
    • Office


  • Forum
  • AutoIt


  • Community Calendar

Found 183 results

  1. Hello, I used the various "_Excel_*()" funktions to open workbooks, read and write cells. I was looking through the helpfile for the _EXCEL_* funktions, if there is a direkt way to set background color or text color -- if there is one, then I missed it? Searching the forum I found this posting demonstrating how to do this task using ... ObjCreate("Excel.Application") Is there a way to set the background color etc. with the native "_EXCEL_*" as well? Regards, Rudi.
  2. Hi guys, Wondering, is there a better way, likely to use 'for...next' to add a letter to each range, by moving right -> along a range of columns in excel. I currently use this, but its clunky. If $run = 1 Then $range = "B6:B41" If $run = 2 Then $range = "C6:C41" If $run = 3 Then $range = "D6:D41" If $run = 4 Then $range = "E6:E41" If $run = 5 Then $range = "F6:F41" If $run = 6 Then $range = "G6:G41" If $run = 7 Then $range = "H6:H41" If $run = 8 Then $range = "I6:I41" If $run = 9 Then $range = "J6:J41" If $run = 10 Then $range = "K6:K41" If $run = 11 Then $range = "L6:L41" If $run = 12 Then $range = "M6:M41" If $run = 13 Then $range = "N6:N41" If $run = 14 Then $range = "O6:O41" If $run = 15 Then $range = "P6:P41" If $run = 16 Then $range = "Q6:Q41" If $run = 17 Then $range = "R6:R41" If $run = 18 Then $range = "S6:S41" If $run = 19 Then $range = "T6:T41" If $run = 20 Then $range = "U6:U41" If $run = 21 Then $range = "V6:V41" If $run = 22 Then $range = "W6:W41" If $run = 23 Then $range = "X6:X41" If $run = 24 Then $range = "Y6:Y41" If $run = 25 Then $range = "Z6:Z41" If $run = 26 Then $range = "AA6:AA41" If $run = 27 Then $range = "AB6:AB41" If $run = 28 Then $range = "AC6:AC41" If $run = 29 Then $range = "AD6:AD41" If $run = 30 Then $range = "AE6:AE41" If $run = 31 Then $range = "AF6:AF41" If $run = 32 Then $range = "AG6:AG41" If $run = 33 Then $range = "AH6:AH41" If $run = 34 Then $range = "AI6:AI41" If $run = 35 Then $range = "AJ6:AJ41" If $run = 36 Then $range = "AK6:AK41" If $run = 37 Then $range = "AL6:AL41" If $run = 38 Then $range = "AM6:AM41" If $run = 39 Then $range = "AN6:AN41" If $run = 40 Then $range = "AO6:AO41" If $run = 41 Then $range = "AP6:AP41" If $run = 42 Then $range = "AQ6:AQ41" If $run = 43 Then $range = "AR6:AR41" If $run = 44 Then $range = "AS6:AS41" If $run = 45 Then $range = "AT6:AT41" If $run = 46 Then $range = "AU6:AU41" If $run = 47 Then $range = "AV6:AV41" If $run = 48 Then $range = "AW6:AW41" If $run = 49 Then $range = "AX6:AX41" If $run = 50 Then $range = "AY6:AY41" Normally, if it was going down the rows, i'd use this: For $i = 0 To UBound($iRowCount) - 1 $row = $i + 1 $range = "B"&$row&":B"&$row+1 Next so something like this, but i don't know how to code sequential columns: For $i = 0 To UBound($iColCount) - 1 $col = $i + 1 $range = $col&"1:"&$col&"40" Next If I don't make sense, let me know. Any help would be great. thanks
  3. I have a script that takes a large excel file, pulls out and reorganizes certain information I need, and spits out a trimmed down csv file which I uses to upload the information on my website. Some of this information contains characters with accents or em dashes. By default it would create a csv file in ANSI which I then uploaded but had to tell my website import system it was windows-1252 in order for it to look correct. This was all working fine except now I need to add in a non-breaking space and non-breaking hyphen into parts of my output. At first I tried using ChrW(0xA0) and ChrW(0x2011) as replacements. A quick test in the console looked correct, however opening the csv output in notepad++ showed the space correctly but a ? for the hyphen and the file was still encoded as ANSI. I tried to view it as UTF-8 instead but this just made the space appear as xAO and also other characters appeared that way like my em dashes appeared as x97 and another symbol as xA7 etc. If I instead do a convert to UTF-8 from notepad++ then those problems go away except the hyphen still displays as ?. I then noticed on the page I linked for the non-breaking hyphen it lists the UTF-8 hex as 0xE2 0x80 0x91 (e28091). I was unsure how to enter this in autoit but several things i tried all failed to get the hyphen inserted. I need a way to get both the space and hyphen added correctly as either ANSI or UTF-8, but if it is UTF-8 then I need a way to convert all of the other data I extracted from the excel file. I've included a test excel file with a single line and test script to create a csv demonstrating the problem. test.xlsx test.au3
  4. I try to activate my opened excel file using this code : #include <Excel.au3> $oExcel = _Excel_Open() $sCaption = $oExcel.Caption WinSetState($sCaption, "", @SW_MAXIMIZE) But when i edit cell in my excel file above code not working because it open new excel sheet.
  5. Hi I have jus started using auto-it . Please correct me if I'm wrong. I need to read data from an input in text box and search in excel file and return value in next column of matched cell on GUI. I have written below code but i cannot use variable which has data stored. it works only when search string is hard coded. Please help out. Example() Func Example() Local $GuiMain = GUICreate("EXCEL TEST", 399, 180) ;creates main GUI ;~ Local $idOK = GUISetOnEvent($GUI_EVENT_CLOSE, "Close") Local $iWidthCell = 70 Local $idLabel = GUICtrlCreateLabel("PART NUMBER", 10, 30, $iWidthCell,50) Local $RUN_1 = GUICtrlCreateButton("OK", 70, 70, 85, 25) Local $Input_1 = GUICtrlCreateInput("PART NUMBER", 100, 20, 120, 20) Local $sMenutext = GUICtrlRead($Input_1, 1) GUISetState(@SW_SHOW, $GuiMain) While 1 $MSG = GUIGetMsg() Select Case $MSG = $GUI_EVENT_CLOSE Exit Case $MSG = $RUN_1 Local $oAppl = _Excel_Open() Local $sFilePath1 = "D:\Anu_WorkFolder\Components.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath1, Default, Default, True) Local $aResult = _Excel_RangeFind($oWorkbook, $sMenutext , Default, Default, $xlWhole)
  6. How to Activate Opened Excel Windows Using Class not Tittle, Because Some time opened defferent excel that have different name. I Tried with Winactivate ("[CLASS:XLMAIN]") but not working
  7. Excel Links Mapper

    Here is another one from my archives that filled a specific need. Here is the back story if you are interested. Keep in mind that I wrote this script over 3 years ago, so it may not compile or run directly without some minor tweaks. It also requires the use of GraphViz to build the graph. #cs ---------------------------------------------------------------------------- Project Name: ExcelLinksMapper Description: Analyse an Excel file's links and map them out. Creation Date: 9/26/2014 AutoIt Version: Author: willichan Requires: Graphviz (http://graphviz.org/) #ce ---------------------------------------------------------------------------- Opt("MustDeclareVars", 1) ;0=no, 1=require pre-declare Opt("TrayAutoPause", 0) ;0=no pause, 1=Pause Opt("TrayMenuMode", 0) ;0=append, 1=no default menu, 2=no automatic check, 4=menuitemID not return Opt("TrayIconHide", 0) ;0=show, 1=hide tray icon Global Const $MyName=StringLeft(@ScriptName, StringInStr(@ScriptName,".", 0, -1)-1) ;get just the name portion of the script/exe name Global Const $MyMutex=$MyName & "-82243BEBC30533A3" ;name the mutex for this app Global $SQLloaded = False Global $sDbName = @ScriptDir & "\db2gv.db" ConsoleWrite($sDbName & @crlf) If _MutexExists($MyMutex) Then Exit #include <SQLite.au3> #include <SQLite.dll.au3> #include <file.au3> #include <array.au3> #include <excel.au3> _ConfigInitialize() _Main() Func _ConfigInitialize() OnAutoItExitRegister("_ConfigDestroy") ;initializers here Global $sSQliteDll = _SQLite_Startup() If @error Then MsgBox(0, "SQLite Error", "could not load the DLL") Global $sSQLiteDB = _SQLite_Open($sDbName) If $sSQLiteDB = 0 Then MsgBox(0, "SQLite Error", "could not open the database") $SQLloaded =True __CreateTables() EndFunc ;==>_ConfigInitialize Func _ConfigDestroy() ;destructors here If $SQLloaded Then _SQLite_Close() _SQLite_Shutdown() EndIf EndFunc ;==>_ConfigDestroy Func _MutexExists($sOccurenceName) Local $ERROR_ALREADY_EXISTS = 183, $handle, $lastError $sOccurenceName = StringReplace($sOccurenceName, "\", "") $handle = DllCall("kernel32.dll", "int", "CreateMutex", "int", 0, "long", 1, "str", $sOccurenceName) $lastError = DllCall("kernel32.dll", "int", "GetLastError") Return $lastError[0] = $ERROR_ALREADY_EXISTS EndFunc ;==>_MutexExists Func __CreateTables() _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS nodes;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS nodes( name TEXT PRIMARY KEY, fileexists INTEGER);") _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS links;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS links( id INTEGER PRIMARY KEY, name1 TEXT, name2 TEXT, weight INTEGER);") EndFunc Func _Main() Local $sInfile, $vResult, $iErrLoop $sInfile = FileOpenDialog("Source File", @WorkingDir, "Excel files (*.xl*)", 1 + 2) If Not FileExists($sInfile) Then MsgBox(0, "Excel Links Mapper Error", "Unable to locate source file") Exit EndIf $vResult = $SQLITE_IOERR $iErrLoop = 5 While $vResult = $SQLITE_IOERR $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sInfile) & ", 1);") If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then ConsoleWrite($iErrLoop & " tries" & @CRLF & $sInfile & @CRLF) $vResult = $SQLITE_OK EndIf WEnd _GetExcelLinks($sInfile) Global $hOutfile = FileOpen(@ScriptDir & "\" & $MyName & ".gv", 2) If $hOutfile = -1 Then MsgBox(0, $MyName & " ERROR", "Unable to upen file for output") Exit EndIf _WriteHeader() _WriteNodes() _WriteLinks() _WriteFooter() FileClose($hOutfile) _GenerateGraph() ShellExecute(@ScriptDir & '\ExcelLinksMapper.png') EndFunc ;==>_Main Func _GetExcelLinks($strFileName) Local $hQuery, $aCount, $iErrLoop, $vResult ConsoleWrite($strFileName & @CRLF) Local $iLoop, $iExists Local $aLinks Local Const $xlExcelLinks = 1 Local $oExcel = _Excel_Open() Local $ret = _Excel_BookOpen_NoUpdate($oExcel, $strFileName, True, True) Local $err = @error If $err Then If Not IsObj($oExcel) Then ConsoleWrite($ret & " - " & $err & @CRLF) Exit EndIf EndIf $aLinks = $oExcel.ActiveWorkbook.LinkSources($xlExcelLinks) _Excel_BookClose($oExcel, False) _Excel_Close($oExcel, False, True) If IsArray($aLinks) Then If UBound($aLinks) > 0 Then For $iLoop = 0 To UBound($aLinks) - 1 If $aLinks[$iLoop] <> $strFileName Then $iExists = FileExists($aLinks[$iLoop]) ConsoleWrite("DEBUG - Calling WriteNode()") __WriteNode($aLinks[$iLoop], $iExists) ConsoleWrite("DEBUG - Calling WriteLink()") __WriteLink($strFileName, $aLinks[$iLoop]) If $iExists And ($aLinks[$iLoop] <> $strFileName) Then _GetExcelLinks($aLinks[$iLoop]) EndIf Next EndIf EndIf EndFunc ;==>_GetExcelLinks Func __WriteNode($sName, $iExists) Local $iErrLoop = 5 ;Number of attempts to make Local $vResult Do ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Exec(INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");) - create node entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");") If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until $vResult = $SQLITE_OK EndFunc ;==>__WriteNode Func __WriteLink($sName1, $sName2) Local $iErrLoop = 5 ;Number of attempts to make Local $vResult, $hQuery, $vCount ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Query(SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";) - lookup link entry" & @CRLF) _SQLite_Query($sSQLiteDB, "SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";", $hQuery) ConsoleWrite("DEBUG - _SQLite_FetchData()" & @CRLF) _SQLite_FetchData($hQuery, $vCount) If UBound($vCount) > 1 Then _ArrayDisplay($vCount) If $SQLITE_OK And UBound($vCount) > 1 Then $vCount = $vCount[1] + 1 Else $vCount = 1 EndIf Do If $vCount = 1 Then ConsoleWrite("DEBUG - _SQLite_Exec() - create link entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "INSERT INTO links ('name1', 'name2', 'weight') VALUES (" & _SQLite_FastEscape($sName1) & ", " & _SQLite_FastEscape($sName2) & ", " & $vCount & ");") Else ConsoleWrite("DEBUG - _SQLite_Exec() - update link entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "UPDATE links SET 'weight'=" & $vCount & " WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2=" & _SQLite_FastEscape($sName2) & ";") EndIf If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until $vResult = $SQLITE_OK EndFunc ;==>__WriteLink ; #FUNCTION# ==================================================================================================================== ; Author ........: SEO <locodarwin at yahoo dot com> ; Modified.......: litlmike, water, GMK, willichan ; =============================================================================================================================== Func _Excel_BookOpen_NoUpdate($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default) If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0) If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) If $bReadOnly = Default Then $bReadOnly = False If $bVisible = Default Then $bVisible = True ;; changing the second parameter on the following line to a 0 tells Excel not to update any links. Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, 0, $bReadOnly, Default, $sPassword, $sWritePassword) If @error Then Return SetError(3, @error, 0) $oExcel.Windows($oWorkbook.Name).Visible = $bVisible ; If a read-write workbook was opened read-only then return an error If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(4, 0, $oWorkbook) Return $oWorkbook EndFunc ;==>_Excel_BookOpen_NoUpdate Func _GenerateGraph() RunWait(@ScriptDir & '\GraphViz238\bin\dot.exe -Tpng "' & @ScriptDir & '\' & $MyName & '.gv" -o "' & @ScriptDir & '\ExcelLinksMapper.png"') EndFunc ;==>_GenerateGraph Func _WriteHeader() __OutLine(0, "digraph main {") EndFunc ;==>_WriteHeader Func _WriteNodes() Local $aResult, $iRows, $iColumns, $iRval Local $iLoop, $sStyle __OutLine(1, "// Nodes") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name, fileexists FROM nodes;", $aResult, $iRows, $iColumns) If @error Then ConsoleWrite("_WriteNodes()" & @CRLF & "_SQLite_GetTable") If $iRval = $SQLITE_OK Then For $iLoop = 1 To $iRows If $aResult[($iLoop * 2) + 2] > 0 Then $sStyle = "normal" Else $sStyle = "missing" EndIf __OutNode($aResult[1 + (2 * $iLoop)], $sStyle) ;; Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg() & @CRLF & "in _WriteNotes() calling _SQLite_GetTable()") Exit EndIf EndFunc ;==>_WriteNodes Func __OutNode($sName, $sStyle = Default) If $sStyle = Default Then $sStyle = "Normal" Switch StringLower($sStyle) Case "missing" __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=red,fontcolor=red,shape=octagon];') Case Else ;"normal", Default __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=black,fontcolor=black,shape=box];') EndSwitch EndFunc ;==>__OutNode Func _WriteLinks() Local $aResult, $iRows, $iColumns, $iRval Local $iLoop, $sStyle, $aCount __OutLine(1, "// Links") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name1, name2 FROM links ORDER BY name1 ASC, name2 ASC;", $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then For $iLoop = 1 To $iRows __OutLink($aResult[1 + (2 * $iLoop)], $aResult[2 + (2 * $iLoop)]) Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) Exit EndIf EndFunc ;==>_WriteLinks Func __OutLink($sName1, $sName2, $iWeight = 1) __OutLine(1, __MakeName($sName1) & ' -> ' & __MakeName($sName2) & ';') EndFunc ;==>__OutLink Func _WriteFooter() __OutLine(0, "}") EndFunc ;==>_WriteFooter Func __OutLine($iTabs, $sText) Local $iLoop If $iTabs > 0 Then For $iLoop = 1 To $iTabs FileWrite($hOutfile, " ") ;ConsoleWrite(" ") Next EndIf FileWriteLine($hOutfile, $sText) EndFunc ;==>__OutLine Func __MakeName($sText) Local $sNewName = StringReplace($sText, "\", " ") $sNewName = StringReplace($sNewName, "/", " ") $sNewName = StringReplace($sNewName, "'", " ") $sNewName = StringReplace($sNewName, '"', " ") $sNewName = StringReplace($sNewName, ':', " ") $sNewName = StringReplace($sNewName, '.', " ") $sNewName = StringReplace($sNewName, '-', " ") $sNewName = StringReplace($sNewName, '$', " ") $sNewName = StringStripWS($sNewName, 8) Return StringLower($sNewName) EndFunc ;==>__MakeName
  8. Hello, at first: i'm new here, so please forgive me my mistakes, and show them to me, just that i can learn to do better in the future. Now to my Problem: i have an excel sheet, where i just need some columns for further actions, but i have no idea how to add single columns to a new array. I found the following code(the one i just added as a file) from 'water' in this forum, but i wont get how i could add multiple columns into a new array. The biggest problem in my situation is that i dont know the count of the rows i need for the array, i just got a fix number of rows, which is 4. Hope you can help me, and sry again for this 'unlucky illustration'. btw: how can i add code shown as code here, instead of posting it as a attached file?. autoit-select-column.au3 defects.xlsx
  9. Hi, i have an error: ==> The requested action with this object has failed.: $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count $iRowCount = .Range(^ ERROR My code is: $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) Sleep(1000) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count Sleep(1000) _Excel_RangeWrite($oWorkbook_1, $oWorkbook_1.ActiveSheet,$aFileList[$i][2] , "AB3:AB"&$iRowCount) I have added some sleep because the application was crashing more often before, so i thought to slow down the code execution. But i didn't solve the issue. Has anyone an idea of what the problem might be? Thanks in advance.
  10. Hi, i have a problem with the deletion of an empty row in Excel. My code: If $vRow_2 = "" Then _Excel_RangeDelete($oWorkbook_1.ActiveSheet,"2", $xlShiftUp,1) EndIf I want to delete the second row. $vRow_2 is an empty cell, "A2". After running the code, the second row is not deleted. I have tried also: If $vRow_2 = Null Then _Excel_RangeDelete($oWorkbook_1.ActiveSheet,"2", $xlShiftUp,1) EndIf But it doesn't work. Any suggestion? Thanks in advance.
  11. I'm trying to insert the following formula in cell A2 using my script: =if(A1=""; "YES"; "NO") To my understanding, the line of code should be similar to this: _Excel_RangeWrite($MasterFile, Default, "=if(A1=""; "YES"; "NO")", "A2") However, it does not work, probably due to the multiple quotation marks that confuse the script :C How do I avoid this problem?
  12. Hi everybody, i have a question about Excel, i have to create several charts one below the other dynamically. I have thought to use: $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count And then to use it in this way: $Graph_position = "=Test1!A"&$iRowCount+2&":K"&$iRowCount+24 But it doesn't work with charts. Does anyone have a suggestion?
  13. Hi all, i have an empty csv file, i have a non formatted text file. What do i want to do? I want to automate the process "get external data" in Excel, i want to import the data from the text file and basically create a csv file with a specific character encoding. Is it possible with AutoIT?
  14. I want to create a excel file from my script if it does not exist. _ExcelBookOpen throws error=2 if file does not exist, after this error i want to create new file at this point. can i use _FileCreate()? _Logger($sLogPath, "{INFO}------: Opening Excel File: " & $sExcelPath& "") While 1 Local $oExcelTestResult = _ExcelBookOpen($sExcelPath) If @error = 2 Then If not _FileCreate($sResExcelPath) Then MsgBox(0, "Error", "Error In Opening REsult Excel File: Error: " & String(@error)) _Logger($sLogPath, "{ERROR}------: Result Excel File does not exist.. tried to create new but :ERROR : " & String(@error) & "") ExitLoop Else _Logger($sLogPath, "{INFO}------: Result Excel File does not exist.. **Created New**: ") EndIf Else ExitLoop EndIf WEnd
  15. Hi everybody, i have to store an entire row of a Excel workbook into an array. The row index is stored in a variable. How can i do it? Thanks in advance for your support.
  16. Hi everybody, i have to write a value into an excel column. I know where it starts from, but i don't know what the end is, last non-empty cell. How can i get the number of last non-empty cell? Thanks in advance. Regards
  17. Hi All, I have excel file like this and i want to cut cell/text from excel to other software. I have to cut the cell of B column one by one and past into other software If Winexists("No Data Found") then restore cuted cell and goto next/down side cell How to do it ?
  18. Hey Community, cause im too new in the Auto it world i will try it with the your help. hopefully. I woud like to know how i can handle my Problem. ---- I have a Excel Data with 362 random numbers. For Example: 1166642335374 1172899897343 ..... this numbers are a part of the filepath ...example D:\Projekte\1166_64233_5374 as u can see its the first number of the Excel data. After the first 4 numbers it shoud make a "_" than another 5 "_" This is my first question. How can i handle this to make it Shell execute. -------- Second question: If i am in the path. For Example: D:\Projekte\1166_64233_5374 the code shoud search for specific PDF Files. They are named like: 0050569E364B1ED79B900F73E62660EC.pdf the first 15 letters are always the same 0050569E364B1ED when he found this data he has to copy it on a Folder on the Desktop. (There can also be 2 or 3 pdfs in one Folder with this letters) ---- Please give me some help :-)
  19. I am not sure why I am getting the this error on my second pass of the code. 1 - $oWorkbook is not an object or not a workbook object Any help or advice on my code appreciated. #include <Excel.au3> #include <Array.au3> #include <MsgBoxConstants.au3> Global $sExcelFile1 = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xlsm)") Global $sExcelFile2 = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "Excel Sheet (*.xlsx;*.xls)|All (*.*)") Global $vRow = 2 If FileExists($sExcelFile2) Then Global $oExcel2 = _Excel_Open () $oExcel2 = _Excel_BookOpen($oExcel2,$sExcelFile2) EndIF If FileExists($sExcelFile1) Then Global $oExcel1 = _Excel_Open () $oExcel1 = _Excel_BookOpen($oExcel1,$sExcelFile1,Default,Default,"2007") EndIF $oRead = _Excel_RangeRead ($oExcel2,"Untitled","A2",3) $oFind = _Excel_RangeFind ($oExcel1,$oRead,"E4:FD92",Default,$xlWhole) $Clip = _ArrayToClip($oFind,"",0,0,"",2,2) Send("{ScrollLock Off}") $hWnd = WinWait("[CLASS:XLMAIN]") ControlSend($hWnd, "", "", ("^g")) WinWait("[CLASS:bosa_sdm_XL9]") ; Go To ControlSend($hWnd, "", "", ("^v")) ControlSend($hWnd, "", "", ("{Enter}")) ControlSend($hWnD, "", "", "{Down " & $vRow & "}") Do $oTime = _Excel_RangeRead ($oExcel2,"Untitled","B2",3) If @error Then Exit MsgBox(0, "Error", "Error" & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox(0,"Test",$oTime) IF $oTime = "7:10:00 AM" Then $oCalls1 = _Excel_RangeRead ($oExcel2,Default,"C" & $vRow,3) $oCalls2 = _Excel_RangeRead ($oExcel2,Default,"D" & $vRow,3) ControlSend($hWnd, "", "", $oCalls1) ControlSend($hWnd, "", "", ("{RIGHT}")) ControlSend($hWnd, "", "", $oCalls2) $vRow = $vRow + 1 ContinueLoop Else $vRow = $vRow + 1 EndIf Until $vRow = 4 1.xlsm 2.xlsx
  20. My Main goal is to copy the info from sheet 2 and put it in the correct date and time location on sheet 1. 1.xlsm - Password 2007 2.xlsx 1. I do not understand why I am unable to find the date. I am reading the value and searching for the value. But, it never turns anything up. 2. What would be the best way (function) to go about selecting the second cell underneath the date after I have found it? So Ideally, I would want to match the date. Once it finds the date go 2 cells down insert the data from sheet 2. I believe I have a good idea on how to loop the script. The only thing I am not sure on is how to take the cell I find and get the cell location (exp. A2) to go the cell that I find and select 2 cells down and go to A4. In other words I don't know how to put the cell I find into a variable that I can use to position where I start to input data. Any help is appreciated as always. #include <Excel.au3> #include <Array.au3> MsgBox(0, "", "Open Excel 1") Global $sExcelFile1 = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xlsm)") MsgBox(0, "", "Open Excel 2") Global $sExcelFile2 = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "Excel Sheet (*.xlsx;*.xls)|All (*.*)") If FileExists($sExcelFile2) Then Global $oExcel2 = _Excel_Open () $oExcel2 = _Excel_BookOpen($oExcel2,$sExcelFile2) EndIF If FileExists($sExcelFile1) Then Global $oExcel1 = _Excel_Open () $oExcel1 = _Excel_BookOpen($oExcel1,$sExcelFile1,Default,Default,"2007") EndIF $oRead = _Excel_RangeRead ($oExcel2,Default,"A2") $oRead1 = _Excel_RangeRead($oExcel1,Default,"BY4") MsgBox (0,"Test",$oRead) MsgBox (0,"Test",$oRead1) $oFind = _Excel_RangeFind ($oExcel1,$oRead,"E4:FD4") If @error Then MsgBox(4096, "", "No File(s) chosen") Else Local $aSalesResult = _Excel_RangeFind($oExcel1, $oRead, Default, Default, $xlWhole) _Arraydisplay($aSalesResult, "RangeFind", 2, 0, "", "|", "Col|Sheet|Name|Cell|Value|Formula|Comment") EndIf
  21. I get this error, Whenever I try to find a date. Does anyone have any idea why? I saw some post from 2015, However I would imagine this is fixed by now. @water >"J:\Temporary Files\XXXXXXXXX\AutoIt\AutoIt\AutoIt\SciTe\..\autoit3.exe" /ErrorStdOut "C:\Users\XXXXXX\Desktop\Call Report Automation.au3"     "J:\Temporary Files\XXXXXXXX\AutoIt\AutoIt\AutoIt\Include\Excel.au3" (656) : ==> The requested action with this object has failed.: $aResult[$iIndex][1] = $oMatch.Name.Name $aResult[$iIndex][1] = $oMatch^ ERROR >Exit code: 1    Time: 5.791 #include <Excel.au3> Global $sExcelFile1 = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xlsm)") Global $sExcelFile2 = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "Excel Sheet (*.xlsx;*.xls)|All (*.*)") If FileExists($sExcelFile2) Then Global $oExcel2 = _Excel_Open () $oExcel2 = _Excel_BookOpen($oExcel2,$sExcelFile2) EndIF If FileExists($sExcelFile1) Then Global $oExcel1 = _Excel_Open () $oExcel1 = _Excel_BookOpen($oExcel1,$sExcelFile1,Default,Default,"2007") EndIF $oRead = _Excel_RangeRead ($oExcel2,Default,"A2",3) _Excel_RangeWrite ($oExcel1,"Calls Handled",$oRead,"BY7") Sleep (1000) _Excel_RangeFind ($oExcel1,$oRead,"E4:FD92") MsgBox (0,"Test",$oRead) UPDATE: If I take out this line it works. By works I mean I don't get the error. :/ However, I need it. I am just confused. _Excel_RangeWrite ($oExcel1,"Calls Handled",$oRead,"BY7") UPDATE 2: I also get this error when trying to use the helpfile examples. I have version \AutoIt\AutoIt\AutoIt\Include\Excel.au3" (670) : ==> The requested action with this object has failed.: $oSheet = $oWorkbook.Sheets($iIndexSheets) $oSheet = $oWorkbook^ ERROR >Exit code: 1 Time: 0.8931
  22. ExcelChart



    Extensive library to control and manipulate Microsoft Excel charts. Written by GreenCan and water. Theads: General Help & Support - Example Scripts BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort KNOWN BUGS (last changed: 2017-07-21) None. The COM error handling related bugs have been fixed.
  23. Hi, Wondering what I'm doing wrong. as per my script, I activate an external program, copy data from it and I want to paste it into excel, after I clear the current sheet that is there. It is some sort of an array as when I manually paste it into excel it expands across rows and down columns (number of which varies). This repeats for Xno. of program instances, and consequently I would want it to cycle through x sheets. So far I have the following, noting that I dont have the sheet clearing thing detailed yet, but I get the following error: "Error copying cells. @error = 4, @extended = -2147352567" func Primary() For $i=0 to ubound($term)-1 WinActivate($term[$i]) sleep(500) MouseClick("left",$OptX,$opty) Clipput("") sleep(500) send("!a") sleep(100) Do sleep(100) until Clipget() ;activates sheet no. specified by $i+ 1, removes data from sheet and pastes new data, cycles to the next sheet ;function to clear sheet _Excel_RangeCopyPaste($oWorkbook1.Worksheets($i+1), Default , "A1") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 2", "Error copying cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 2", "Range 'A1' from workbook _Excel3.xls successfully copied to 'G15'.") Next EndFunc Any help would be great.
  24. Hello everyone , I have spotted a weird problem with the Excel COM API. It doesn't read a spreadsheet with a good amount of lines in a cell... Enough talk, let's get into code: #include <Array.au3> #include <Excel.au3> Global $oExcel = _Excel_Open(False, False, False, False, True) Global Const $sSpreadsheet = @ScriptDir & '\Test.xlsx' Global $oSpreadsheet = _Excel_BookOpen($oExcel, $sSpreadsheet, True, False) Global $aSpreadsheet = _Excel_RangeRead($oSpreadsheet) If @error Then MsgBox(0, "Error", 'Error Code: ' & @error & @CRLF & 'Extended Code: ' & @extended) _Excel_Close($oExcel, False) _ArrayDisplay($aSpreadsheet) (Download Test.xlsx) I get this error when I run the script: If you open the spreadsheet, you can see that I have dummy text in the B2 cell. If you replace the contents of the cell B2 with something like Test4, it will work perfectly fine: Is this some kind of undocumented line limit for Excel? Thanks in Advance! TD
  25. Hi guys, I was trying to automate a webpage after getting input from excel. The script worked fine for like 5 iterations but then stopped all of a sudden, I dont have the exact error msg now, but it was pointing to this in the IE UDF:- Return SetError($_IESTATUS_Success, $oTemp.GetElementsByTagName($sTagName).length, When i looked up the forum there was a similar question which said that I have to fix KB 3008923 update by uninstalling it, but I am unable to do that. here is the link for download:- ( i guess ) https://support.microsoft.com/en-us/help/3024777/error-0x800706f7-occurs-after-you-install-kb-3004394-and-then-run-windows-update-in-windows-7-and-windows-server-2008-r2 Does anybody know the proper steps to solve this?? Plus I wont be able to send the code here because of privacy policy n all by the company. Desperately looking for a reply.....

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.