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


Forums

  • 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

Categories

  • 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

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


Location


WWW


Interests

Found 256 results

  1. Hey, as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing. Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_RangeCopyPaste($oExcel.ActiveSheet, "V4:W43", "X4", False, $xlPasteValues, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.") As I understood it the option $xlPasteValues should do the trick but it is still copying the inserted formulas. I attached a test code and excel file. Thanks for the help Simon Copy Values Test.zip
  2. An "improper" "unusual" use of Excel the script allows the creation of artistic images by simply coloring the background of the individual cells of the Excel workbook. Although definitely useless ... I find it quite funny though have a good time many thanks to @UEZ , @Malkey , @water p.s. I think the pixelite + color to array process can be simplified, but I used the two ready-made functions provided by UEZ and Malkey. I thank both of you (credits and links in listing) p.p.s. strange behaviour: while excell is filling cells, if you move the mouse pointer off the excell window, the fill speed increases ... (?) ; =============================================================================================================================== ; Name ..........: Excel in art ; Description ...: This script allows the creation of artistic images in Excel from a choosed picture. ; The picture is done by simply coloring the background of the individual cells of an Excel workbook. ; Although definitely useless, I find it quite funny though ; ; Return values .: An artistic image in an Excel workbook ; Author ........: Addiego Gianni (chimp) ; Modified ......: ; Remarks .......: Many thanks to UEZ, Malkey and Water ; Related .......: ; Link ..........: ; Example .......: ; =============================================================================================================================== #include <GDIPlus.au3> #include <Excel.au3> _PixelsToCells(50) ; <-- Parameter 50 is the cumber of horizontal Excel cells to be filled MsgBox(64, "All done", "Excel art is ready") Func _PixelsToCells($iHcells = 50) ; Check application object Local $oExcel = _Excel_Open() If Not IsObj($oExcel) Then MsgBox(16, "Error", "Sorry, You need to have 'Excel' intalled") ; Choose Image File Local $sPath = FileOpenDialog("Choose Image File", @ScriptDir & "", "Images (*.gif;*.png;*.jpg;*.bmp)| All (*.*)") If $sPath = '' Then Exit MsgBox(16, "Error", "Sorry, no image was chosen") ; Create a new Excel workbook $oWorkbook = _Excel_BookNew($oExcel, 1) ; initialize GDI+ _GDIPlus_Startup() Local $hBmp = _GDIPlus_BitmapCreateFromFile($sPath) Local $iWidth = _GDIPlus_ImageGetWidth($hBmp) ; get image width ; Local $iHeight = _GDIPlus_ImageGetHeight($hBmp) ; not needed here Local $iStep = $iWidth / $iHcells ; calculate the pixelation factor Local $hBitmap_new = _GDIPlus_PixelateBitmap($hBmp, $iStep) ; pixelate the image Local $aPixelColors = _FileImageToArray($hBitmap_new) ; get pixel colors ; reduce Excel columns width Local $xx = 1, $yy = 1 For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Columns($xx).ColumnWidth = 1 $xx += 1 Next ; reduce Excel rows height For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep $oWorkbook.Sheets(1).Rows($yy).RowHeight = 9 $yy += 1 Next $xx = 1 $yy = 1 For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Range(_Excel_ColumnToLetter($xx) & $yy).Interior.Color = Number("0x" & $aPixelColors[$iRow][$iCol]) $xx += 1 Next $yy += 1 $xx = 1 Next _GDIPlus_BitmapDispose($hBmp) _GDIPlus_Shutdown() EndFunc ;==>_PixelsToCells ; by UEZ ; https://www.autoitscript.com/forum/topic/167707-imagepixelate/?do=findComment&comment=1227509 Func _GDIPlus_PixelateBitmap($hBitmap, $iPixelate, $bSmooth = 1) Local $iWidth = _GDIPlus_ImageGetWidth($hBitmap), $iHeight = _GDIPlus_ImageGetHeight($hBitmap) Local $iNewW = Round($iWidth / $iPixelate, 0), $iNewH = Round($iHeight / $iPixelate, 0) Local $hBitmap_scaled = _GDIPlus_BitmapCreateFromScan0($iNewW, $iNewH) Local $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_scaled) Local $iInterpolation = 5 If $bSmooth Then $iInterpolation = $GDIP_INTERPOLATIONMODE_BILINEAR _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $iInterpolation) _GDIPlus_GraphicsDrawImageRect($hCtxt, $hBitmap, 0, 0, $iNewW, $iNewH) _GDIPlus_GraphicsDispose($hCtxt) Local $hBitmap_pixelated = _GDIPlus_BitmapCreateFromScan0($iWidth, $iHeight) $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_pixelated) _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $GDIP_INTERPOLATIONMODE_NearestNeighbor) _GDIPlus_GraphicsDrawImageRectRect($hCtxt, $hBitmap_scaled, 0, 0, $iNewW, $iNewH, -$iPixelate, -$iPixelate, $iWidth + 2 * $iPixelate, $iHeight + 2 * $iPixelate) _GDIPlus_GraphicsDispose($hCtxt) Return $hBitmap_pixelated EndFunc ;==>_GDIPlus_PixelateBitmap ; by Malkey ; https://www.autoitscript.com/forum/topic/112540-is-there-a-function-for-reading-images-into-2d-arrays/?do=findComment&comment=788472 Func _FileImageToArray($hImage) Local $Reslt, $stride, $format, $Scan0, $iIW, $iIH ; , $hImage Local $v_Buffer, $width, $height ; _GDIPlus_Startup() ; $hImage = _GDIPlus_ImageLoadFromFile($sFileName) $iIW = _GDIPlus_ImageGetWidth($hImage) $iIH = _GDIPlus_ImageGetHeight($hImage) ProgressOn("Progress Bar", "Filling a " & $iIW & " x " & $iIH & " size array.", "0 percent") $Reslt = _GDIPlus_BitmapLockBits($hImage, 0, 0, $iIW, $iIH, $GDIP_ILMREAD, $GDIP_PXF32ARGB) ;Get the returned values of _GDIPlus_BitmapLockBits () $width = DllStructGetData($Reslt, "width") $height = DllStructGetData($Reslt, "height") $stride = DllStructGetData($Reslt, "stride") $format = DllStructGetData($Reslt, "format") $Scan0 = DllStructGetData($Reslt, "Scan0") Local $aArray[$height][$width] For $j = 0 To $iIH - 1 For $i = 0 To $iIW - 1 $v_Buffer = DllStructCreate("dword", $Scan0 + ($j * $stride) + ($i * 4)) $aArray[$j][$i] = StringRegExpReplace(Hex(DllStructGetData($v_Buffer, 1), 6), "(.{2})(.{2})(.{2})", "\3\2\1") ; To RGB format Next ProgressSet(Int(100 * $j / ($iIH)), Int(100 * $j / ($iIH)) & " percent") Next _GDIPlus_BitmapUnlockBits($hImage, $Reslt) ProgressOff() _GDIPlus_ImageDispose($hImage) Return $aArray EndFunc ;==>_FileImageToArray
  3. Hi guys Need little help with filtering. I'm trying to filter specific weeks from power pivot table using this code recorded with Excel VBA : #include <Excel.au3> Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\....\Orders.xlsb") $oWorkbook.PivotTables("PivotTable1").PivotFields( _ "[Report 2].[Week].[Week]").VisibleItemsList = Array( _ "[Report 2].[Week].&[10]", "[Report 2].[Week].&[11]", _ "[Report 2].[Week].&[12]", "[Report 2].[Week].&[13]", _ "[Report 2].[Week].&[14]", "[Report 2].[Week].&[15]") But get error error: Array(): undefined function. Not sure how to resolve this.
  4. I am trying this code to create multiple workbooks eachone shall be copy of one worksheet from a workbook having multiple sheets ( keeing the name same) SavingWorksheets.au3
  5. Hi guys, i have simple report in PowerPivot that shows Orders (Values) by Regions (Row) and Weeks (Columns). In Filter field is WeekDAYS (Monday,Tuesday,Wednesday,Thursday etc ) how to filter WeekDAYS Filed on WEEKDAYYesterday with autoit ? my junky try #include <Date.au3> #include <Excel.au3> Local $sWEEKDAYYesterday = _DateDayOfWeek(@WDAY-1) Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\.......\Orders.xlsb") Sleep (5000) $oExcel.ActiveWorkbook.RefreshAll Sleep (5000) $oExcel.Application.Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday) Error result $oExcel.Application.Sheets("PivotTable1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday) $oExcel.Application^ ERROR
  6. I have a strange behavior in an Autoit program. The program works perfectly in the original environment I created the program for - for Windows 7 and Office 2010. My workplace is migrating to Windows 10 with Office 2016. When I run this program in that new environment, the code actually executes without any errors, however, the excel process which was opened to read a spreadsheet/workbook does not close while the program is running. If you exit the program, the excel process ends at that point... I could ignore this behavior - one little excel process hanging out there is not going to kill anything, however - it just bugs me ! While troubleshooting the issue, I placed a number of error checks after the excel close - and the close actually reports that it is successful. I tried a few varieties of closing the excel process, and none of the methods tried seemed to actually work as well at the one in my code - the only downside being that it has to wait until the program finishes to actually close. Any ideas on why an excel close would hang until program exit ? Local $oExcel = _Excel_Open(False, False, False, False, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, $sUserRoleMenuXLS, False, true ) If @error Then     MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error opening workbook '" & $sUserRoleMenuXLS & @CRLF & "@error = " & @error & ", @extended = " & @extended)     _Excel_Close($oExcel, False, False)  Exit EndIf ;  this section will find the user id in the first 3 columns of the user/menu spreadsheet, and if it finds it - returns the value stored in the cell 2 locations to the right... With $oExcel.ActiveSheet.Range("A:C").Find (@UserName)     $Match = .Find (@UserName)     If (Not IsObj($Match)) or (stringlen($Match.Offset(0, 2).value) = 0)    Then         MsgBox($MB_SYSTEMMODAL, 'UIPLauncher Error', 'No Menu assigned for user - Contact Support.') ; Display a warning if the script isn't compiled or the file doesn't exist.         ;_Excel_BookClose($oWorkbook, False)         _Excel_Close($oExcel, false, false)         Exit     Else         Local  $cResult = $Match.Offset(0, 2).value     EndIf EndWith $oWorkbook.Saved = True _Excel_BookClose($oWorkbook, False) _Excel_Close($oExcel, false, false) ;close does not seem to work on windows 10 and office 2016
  7. $aTdslist = _Excel_RangeRead($oWorkbook1, Default, "B1:B" & $LastRow) $aTDSLOC = _Excel_RangeRead($oWorkbook1, Default, "H1:H" & $LastRow) MsgBox(0, "TAKSY OUTPUT LAST ROW", $LastRow) _ArrayTrim($aTDSLOC, 1, 1) _Excel_RangeWrite($oWorkbook1, $oWorkbook1.ActiveSheet, $aTDSLOC, "H2") $oWorkbook1.Sheets("DS_List").Activate $mLastRow = $oWorkbook1.ActiveSheet.UsedRange.Rows.Count $aMdslist = _Excel_RangeRead($oWorkbook1, Default, "B1:B" & $mLastRow) MsgBox(0, "MASTER OUTPUT LAST ROW", $mLastRow) _Excel_RangeReplace($oWorkbook1, Default, "F2:F" & $mLastRow, "N/A", "n/a") ;check the requiremetn $aMLOC = _Excel_RangeRead($oWorkbook1, Default, "F1:F" & $mLastRow) ;LOC NUMBER NEEDS TO BE REPLACED WITH.. _ArrayDisplay($aTDSLOC) _ArrayDisplay($aMLOC) _ArrayDisplay($aMdslist) ;DATA COMPARISION BETWEEN TAKSY LIST WITH LOC FROM SAP TO MASTER LIST WITH LOC. $oWorkbook1.Sheets("OUTPUT").Activate For $i = 1 To $LastRow - 1 For $j = 1 To $mLastRow - 1 If $aTdslist[$i] == $aMdslist[$j] And $aTDSLOC[$i] <> $aMLOC[$j] Then $oExcel.ActiveSheet.Range("H" & $i + 2).Interior.ColorIndex = 3 EndIf If $aTdslist[$i] == $aMdslist[$j] And $aMLOC[$j] == "n/a" And _Excel_RangeRead($oWorkbook1, Default, "I" & ($i + 2)) == "NO KIT" Then $oExcel.ActiveSheet.Range("H" & $i + 2).Interior.ColorIndex = 2 _Excel_RangeWrite($oWorkbook1, $oWorkbook1.ActiveSheet, "*", "H" & ($i + 2)) EndIf Next Next MsgBox($MB_SYSTEMMODAL, "Status", "Loc extraction, comparision and consolidation is Done", 2) DEAR TEAM, I am facing issue w.r.t subscript errors kindly help me in solving... 456.xlsx
  8. $oWorkbook = _Excel_BookAttach($sWorkbook) $oWorkbook.sheets("Sheet1").activate Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "") _Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, $sMSN) can anyone help me here, excel filer is not working with the above code.. attached excel input: 12 Thank you in advance!! to check.xlsx
  9. Hi, I am using excel as input media for my program. The excel file (i tried with .xls, .xlsx and .xlsm format) has inputs which the autoit script reads during the run and performs few calculations. Some times (not always), after the run, when i try to open the excel file manually, the file doesnt open at all in excel. see the screenshot attached. However, if the execute the autoit script, the scripts still reads the existing data from that excel and performs the calcs. I copied the excel file to another computer and there too, it doesnt open. So, after this, i cannot edit the excel forever (if i need to change any inputs). It is only this particular file that got affected. other excel files works normal. What could be the problem here. please help as this is a new challenge for me during my program development.
  10. I recently >posted a question in the general help and support about how the best way to find the last non-empty cell in a workbook. @Water was kind enough to help me find several solutions. During that thread we also posted a snippet that I find to be very useful. It was slightly off topic from the OP so I thought I would post it here so it has a home in case anyone needs it. That said, this is one way to delete all blank rows in a spreadsheet: $oWorkbook.ActiveSheet.Columns("A:A").SpecialCells($xlCellTypeBlanks).EntireRow.Delete The $xlCellTypeBlanks is a constant in the Excel constants include so you don't need to pre-define it. "A:A" is the range. So this would look for any blank rows in Col A and delete those rows from the workbook. Hope somebody finds it useful.
  11. #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> ; Create application object and open an example workbook 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 & "\trans.xlsx") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\trans.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf ; ***************************************************************************** ; Read data from a single cell on the active sheet of the specified workbook ; ***************************************************************************** Local $sResult = _Excel_RangeRead($oWorkbook, Default, "A1") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Data successfully read." & @CRLF & "Value of cell A1: " & $sResult) Hi, all. Ok, here is the deal. I have simple excel file called trans.xlsx. It's located in the directory of script. In general i don't care where to store it. What i do need is to open excel file and copy one by one numbers from cells. I've tried different ways, examples. But i only get error, says: error = 3, extended = 1. I saw different posts from different years. I even tried to use simple example from manual file. But always get error. In general my goal get numbers one by one and post it to let's say search filed in my PC one by one. Or to notepad (but one by one, in kind of loop). I've learned how to copy or show in message box some info from other apps. But with excel i'm stuck. I'm able to open needed window based on "title" of excel. But i don't succeed of copying info from cells. Would be appreciate for any help. So, in this code i'm trying at least to read from cell A1. Doesn't matter what Sheet. I use Windows 10, Excel for Office 365. Thank you in advance.
  12. Hi, I've been trying to automate modification of an excel file and the last thing I am stuck on is deleting all the rows where the value of Column 13 is 0. I believe the error is due to me not fully understanding the syntax so this is where I'm stuck: Func Hotkey2() Global $aUsedRange = _Excel_RangeRead($oWorkbook, 1) _ArrayDisplay($aUsedRange) For $iRow = UBound($aUsedRange) - 1 to 3 Step -1 If $aUsedRange[$iRow][13] = 0 Then _Excel_RangeDelete($oWorkbook.Worksheets(1), $aUsedRange[$iRow] & ":" & $aUsedRange[$iRow], default, 1) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 2", "Error deleting rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf Next EndFunc While my script properly locates the row which contains value 0 in Column 13, I am not sure how to set it to the corresponding row in the excel workbook? My above experiment gives me $vRange error and I've been toying around with it to no avail. The only way I get the Script to delete a row is by actually specifying "4:4" or "6:8" etc. Where am I going wrong? Thanks!
  13. Dear all, i am unable to open a xml file to excel in the "xml table format" Please help me out in where i am missing Local $strFileToOpen = _WinAPI_OpenFileDlg('Select xml file', @WorkingDir, 'All Files(*.*)', 1, '', '', BitOR($OFN_PATHMUSTEXIST, $OFN_FILEMUSTEXIST, $OFN_HIDEREADONLY)) Global $xlXmlLoadImportToList = 2 ; Places the contents of the XML data file in an XML table $oExcel = _Excel_Open() $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList) If $strFileToOpen <> False Then     Local $oWorkbook1 = _Excel_BookOpen($oExcel, $strFileToOpen) EndIf Error i am getting is: ......\81e_Compare_v1.au3" (46) : ==> The requested action with this object has failed.: $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList) $oWorkbook1=$oExcel.Workbooks^ ERROR >Exit code: 1 Time: 7.338
  14. Dear all, I am unable to get the right result after applying the filter to the excel. please let me know on the same. issue: After applying the filter the output $lastRow11 not giving the right output of complete visible rows. (its breaking at row skips) ;DATA EXTRACTION FROM LOC EXCEL ;============================================================================= $oWorkbook = _Excel_BookAttach($sWorkbook) Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "") ;~ Local $LastRow1 = ($oWorkbook.ACTIVESHEET.Range("A1").SpecialCells($xlCellTypeLastCell).Row) $LastRow1 = $oWorkbook.ActiveSheet.UsedRange.Rows.Count MsgBox(0, "lastrow1", $LastRow1) _Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, "*" & $sMSN & "*") Local $oLocDS = $oWorkbook.ActiveSheet.Range("S1:S" & $LastRow1).SpecialCells($xlCellTypeVisible) Local $LastRow11 = $oLocDS.rows.count ;error output MsgBox(0, "lastrow11", $LastRow11) Local $aLocDS1 = _Excel_RangeRead($oWorkbook, Default, $oLocDS) Local $oLocNr = $oWorkbook.ActiveSheet.Range("A1:A" & $LastRow1).SpecialCells($xlCellTypeVisible) Local $aLocNr1 = _Excel_RangeRead($oWorkbook, Default, $oLocNr) _ArrayDisplay($aLocDS1) _ArrayDisplay($aLocNr1) _ArrayTrim($aLocDS1, 6, 1) _ArrayTrim($aLocNr1, 6, 1) _ArrayTrim($aLocNr1, 6, 0) _ArrayDisplay($aLocDS1) _ArrayDisplay($aLocNr1)
  15. I am unable to execute the below script, my requirement is to copy the content from active excel sheet and to display the same. Please let me know where i am missing! #include <Excel.au3> #include <MsgBoxConstants.au3> #include <Array.au3> #include <StringConstants.au3> Local $oExcel = _Excel_Open() $LastRow2 = $oExcel.UsedRange.Rows.Count $Tissue = _Excel_RangeRead($oExcel, Default, "E1:E" & $LastRow2) $TshNr = _Excel_RangeRead($oExcel, Default, "F1:F" & $LastRow2) _ArrayDisplay($Tissue) _ArrayDisplay($TshNr)
  16. This is the "General Help and Support" thread for the ExcelChart UDF. The UDF itself can be downloaded So if you have any questions, suggestions or errors please post here.
  17. Hey there! 😃 I am having a problem with the _Excel_RangeFind. I am trying to search for a value in a particular cell range. The script copies the value from the internet. Copying and saving as a variable is working fine, but as soon as it should find the value in excel, nothing happens. ( I am not getting an error) #include <Excel.au3> Func Excel() Send("{CTRLDOWN}") Send("{c}") Send("{CTRLUP}") Local $sName = ClipGet() ;Text Local $sShortName = StringTrimRight ( $sName, 1) ;delete one letter Local $bOpenWorkBook = False, $oExcel = _Excel_Open() Local $sFilePath = "C:\Users\Acer\OneDrive\xyz.xlsx" Local $oWorkbook $oWorkbook = _Excel_BookAttach($sFilePath) If @error Then $oWorkbook = _Excel_BookOpen($oExcel, $sFilePath) $bOpenWorkBook = True EndIf sleep(15000) Send("{LWINDown}") Send ("{up}") ;maximize window Send("{LWINup}") sleep(1000) _Excel_RangeFind ($oWorkbook, $sShortName, "A3:A56") EndFunc Is anyone familiar with this problem or am I just missing some basic stuff? Thanks for help!
  18. Hey guys I'm new to autoit, so this could be a simple question. I'm trying to read the value from the currently selected cell in Excel. I read on the forum and tired to find videos, but I couldn't quite get to it. This is what I have got so far: Local $oExcel_1 = _Excel_Open() Local $var = "C:\Users\Acer\xy" Local $oWorkbook = _Excel_BookOpen($oExcel_1,$var) Local $_read1 = _Excel_RangeRead($oWorkbook, Default.Application.ActiveCell.Address) Whatever I try, I either get an error or it only reads "0". Thank you very much for any helpful thoughts!
  19. Hello , I am new to autoIT, I am wondering if someone could guide me in the correct path for this program. Attached are the steps for the program as well as what I currently have. Any help is greatly appreciated . Thank You. * workbooks\sheets will be organized on per level basis , 1st sheet lvl 1 ,2nd sheet lvl 2 etc or workbook 1 = level 1, workbook 2 = level 2 etc. ; = comments and reminders #include <MsgBoxConstants.au3> #include <EditConstants.au3> #include<excel.au3> #include<Array.au3> Global Const $PatientLookupX = 320 Global Const $PatientLookupY = 64 ; down 1 and enter Global Const $PatientTextBoxX = 410 Global Const $PatientTextBoxY = 217 ; click , Ctrl + V , Enter Global Const $PHMhubX = 512 Global Const $PHMhubY = 613 ;click down 1 enter button Global Const $HealthRiskAssesmetX = 40 Global Const $HealthRiskAssesmetY = 162 Global Const $AddnewAssesmentX = 168 Global Const $AddnewAssesmentY = 98 Global Const $SelectAssesmentX = 342 Global Const $SelectAssesmentY = 98 ; Down 7 and enter Risk score new Global Const $EmptyAnswerBarX = 465 Global Const $EmptyAnswerBarY = 145 Global Const $LowriskpreventionX = 716 Global Const $LowriskpreventionY = 324 Global Const $MediumriskPreventionX = 716 Global Const $MediumriskPreventionY = 352 Global Const $HighriskPreventionX = 716 Global Const $HighriskPreventionY = 377 Global Const $CatatrosphicPreventionX = 714 Global Const $CatatrosphicPreventionY = 399 Global Const $ClosebuttonX = 1167 Global Const $ClosebuttonY = 666 Global Const $SaveRiskButtonX = 1161 Global Const $SaveRiskButtonY = 692 Global Const $ExitCPScreenX = 1339 Global Const $ExitCPScreenY = 8 Global Const $ExitpatientHubX = 1000 Global Const $ExitpatientHubY = 79 Global Const $sleepMod = 2 Global Const $sleepVal = 5000*$sleepMod Global Const $sleepLow = 200*$sleepMod Global Const $sleepMed = 1000*$sleepMod Global Const $sleepHigh = 3500*$sleepMod ;Function Open excel , read account number in column A ;------------------------------------------------------------------------------------------------------------------------------------------------------- HotKeySet("{ESC}","stopbaby") Func _WinWaitActivate($title,$text,$timeout=0) $hWnd = WinWait($title,$text,$timeout) If Not WinActive($title,$text) Then WinActivate($title,$text) WinWaitActive($title,$text,$timeout) EndFunc $i=0 While $i <=2 $i = $i+1 Local $Open_excel = _Excel_Open() Local $File_path = "D:\AutoIT\Risk_Test.xlsx" Local $Open_workbook = _Excel_BookOpen($Open_excel,$File_path) WinActivate($Open_workbook) Local $Read_account_number = _Excel_RangeRead($Open_workbook,default,"A" &$i) _Excel_Close($Open_excel,False) WEnd ;--------------------------------------------------------------------------------------------------------------------------------------------------- Func NavtoSearch() WinActivate(eClinicalWorks (Garcia,Erick) Sleep($sleepMed) MouseClick("",693,77) Send("!p") ; shortcut for patient menu Send("{DOWN}") ; down 1 send ("{Enter}") ; patient lookup Sleep($sleepMed) ;paste account number How would I do this??? Send("{Enter}") ;Once patient is found, + enter = takes you to patient hub Sleep($sleepMed) Next NavtoPHMHub() ;------------------------------------------------------------------------------------------------------------------------------------------------------- Func NavtoPHMHub() MouseClick("",$PHMhubX,$PHMhubY) Sleep($sleepLow) Send("{DOWN}") Send("{ENTER}") ;takes you to Care Plan HUB Next NavtoRiskScore() ;------------------------------------------------------------------------------------------------------------------------------------------------------- Func NavtoRiskScore() MouseClick("",$HealthRiskAssesmetX,$HealthRiskAssesmetY) ;Clicks on HealthRisk assesment Sleep($sleepLow) MouseClick("",$AddnewAssesmentX,$AddnewAssesmentY) ; Click addnew assesment Sleep($sleepLow) MouseClick("",$SelectAssesmentX,$SelectAssesmentY) ;click select assesment tab Sleep($sleepLow) Send("{DOWN 7}") Send("{ENTER}") Sleep($sleepLow) MouseClick("",$EmptyAnswerBarX,$EmptyAnswerBarY) ;Click on Empty answer bar Sleep($sleepLow) MouseClick("",$LowriskpreventionX,$LowriskpreventionY) ; selects Risk Score, Change for other types 1-6 Next NavtoNextPatient() Func NavtoNextPatient MouseClick("",$ClosebuttonX,$ClosebuttonY) MouseClick("",$SaveRiskButtonX,$SaveRiskButtonY) MouseClick("",$ExitCPScreenX,$ExitCPScreenY) MouseClick("",$ExitpatientHubX,$ExitpatientHubY) EndFunc ;Function should loop back to excel sheet, copy next account number, activate eclinicalworks, and repeat the steps ;--------------------------------------------------------------------------------------------------------------------------------------------------------- Func stopbaby() exit EndFunc Best Regards, Morphice steps for program.docx
  20. Quick question, when working with Excel, does each workbook require its own Excel instance, or can multiple workbooks be opened off of the same Excel instance? I tested both ways, on the surface they both seemed to work, just thought I'd ask here to make sure before proceeding any further. Thanks! Disclaimer: The following is pseudo code $excel_instance1 = _Excel_Open() $workbook1 = _Excel_BookOpen($excel_instance1, @ScriptDir & "\book1.xlsx") $workbook2 = _Excel_BookOpen($excel_instance1, @ScriptDir & "\book2.xlsx") Or $excel_instance1 = _Excel_Open() $workbook1 = _Excel_BookOpen($excel_instance1, @ScriptDir & "\book1.xlsx") $excel_instance2 = _Excel_Open() $workbook2 = _Excel_BookOpen($excel_instance2, @ScriptDir & "\book2.xlsx")
  21. Hello, I face an Excel related error right after doing an _Excel_BookOpen. This is an EXCEL worksheet with filters defined, quite simple sheet. Once a week at Friday there is a scheduled task running on the file server "printing" the content to a PDF file for documentation puposes. As this is running 100% unattended it's a show stopper, if such dialog boxes show up. Already when I did that script a month ago I faced the issue, that to dialog boxes showed up telling something about a "name conflict", once for "_FilterDatabase", and a 2nd time for "PrintingArea" (maybe _PrintingArea). When some new name is entered, the script is going on with the PDF creation. But the autoit script is hanging with the _Excel_BookOpen, so I would need to start a 2nd. script to look for such bogus name conflict message boxes. While trying do track down what's going on in detail, the issue vanished again, I answerd the two boxes with "xxxx" and "yyyy" for new fiel names, the file seems to have been saved by me myself without intention (or automatically by EXCEL.AU3?) several other postings point into the direction of "this is an Excel Bug", seems to be not strictly Autoit related. Facts: Windows 7 Pro x64 Office 2010 SP2 32bit Localization = German (Win & Office) Autoit v3.3.14.5 Excel Workbook with three sheets without any Macros: "TBx Projektliste.xlsx", just 82 kByte Export-Excel-to-PDF-Projektstatus.au3 Any suggestions howto take care, that these "name conflicts" cannot occure?
  22. Hello everyone, I am working on a project which requires reading a few values from Excel, the catch is that I need it to be very fast... unfortunatley I found out that read operations using the supplied Excel UDF are very slow, more than 150 ms for each operation on average Here is my testing setup that I made: #include <Excel.au3> #include <MsgBoxConstants.au3> Global $iTotalTime = 0 Test() Func Test() Local $oExcel = _Excel_Open() Local $oBook = _Excel_BookAttach("Test.xlsx", "FileName", $oExcel) Local $sSheet = "Sheet1" If @error Then Return MsgBox($MB_ICONERROR, "Excel Failed", "Failed to attach to Excel") Local $iNum For $iRow = 1 To 6 Time() Local $iNum = Number(_Excel_RangeRead($oBook, $sSheet, "A" & $iRow)) If ($iNum = 1) Then ConsoleWrite("Row " & $iRow & " is 1 and value of column B is " & _Excel_RangeRead($oBook, $sSheet, "B" & $iRow)) Else ConsoleWrite("Row " & $iRow & " is not 1") EndIf ConsoleWrite(". Reading took: ") Time() Next ConsoleWrite("The whole operation took " & $iTotalTime & " milliseconds." & @CRLF) EndFunc Func Time() Local Static $hTimer Local Static $bRunning = False If $bRunning Then Local $iTime = Round(TimerDiff($hTimer), 2) $iTotalTime += $iTime ConsoleWrite($iTime & @CRLF) Else $hTimer = TimerInit() EndIf $bRunning = Not $bRunning EndFunc And Test.xlsx in CSV format: 1,-1 -1,1 1,-1 1,1 -1,-1 1,1 Here is the actual xlsx but it should expire in a week: https://we.tl/t-EVkxGp1kc6 And finally output from my script: Row 1 is 1 and value of column B is -1. Reading took: 276.06 Row 2 is not 1. Reading took: 163.36 Row 3 is 1 and value of column B is -1. Reading took: 302.58 Row 4 is 1 and value of column B is 1. Reading took: 294.65 Row 5 is not 1. Reading took: 152.33 Row 6 is 1 and value of column B is 1. Reading took: 284.92 The whole operation took 1473.9 milliseconds. Taking ~1.5 seconds for reading 6 rows of data is bad for my script, which needs to run as fast as possible . It would be nice if I can bring this down to 100 ms somehow, I am not very experienced working with MS office so I thought about asking you folks for help and advice on how I can optimize my script to squeeze out every bit of performance that I can get from this script Thanks for the help in advance!
  23. I have been using some AutoIt scripts to manipulate Excel for a few weeks now. Today they stopped working. I have rebooted the PC and I'm not aware of any other significant changes. I can start and use Excel conventionally without a problem, but any attempt to create an excel object from AutoIt fails. E.g #include <MsgBoxConstants.au3> #include <Excel.au3> Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Open Example 1", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Produces: @error = 1 @extended = -2147221005 I can't find that COM error listed anywhere... Anyone have any ideas?
  24. Hello all, Preface: Column A is full of courses. Column B is full of usernames. If course (A1) exists, we check to see if username (B1) is enrolled. If user is found, the user is un-enrolled. Then Loop. I am working on a script that loops through an Excel file and pastes the content from A1 into a textbox in IE, does some stuff, then pastes the content from B1 into a different textbox. Then it loops around until all used rows in columns A and B have been accounted for. For some reason, column A loops properly but column B doesn't. B1 is pasted over and over again. So, as it loops, B1 is constantly being pasted, first accompanying A1's loop, then A2, and so on. The OpenExcel() func opens the Excel file the user specifies in an earlier function. It's supposed to gather the entire used range of columns A and B. The SearchCourse() func only uses column A, pasting its content into a Search tool in IE. This seems to be working fine. SearchResult() puts "Course Not Found" into column C if the search fails. If the search is successful, however, we move onto... EnterCourse(). This simply gets us to the place where column B's content comes into play. UnenrollNow(). Here, we paste the variable $_userName into a textbox. I don't know why it's always B1 Func OpenExcel() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True) $oExcel.Sheets("CopyCourses").Activate ;~ Get all used cells in columns A and B $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B")) ;~ Create the $aSearchResult array ReDim $aSearchResult[UBound($aSearchItems)] ;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1) For $i = 0 To UBound($aSearchItems) - 1 ;~ Column 0 ConsoleWrite($aSearchItems[$i][0] & @CRLF) ;~ Column 1 ConsoleWrite($aSearchItems[$i][1] & @CRLF) $aSearchResult[$i] = SearchCourse($aSearchItems[$i][0], $aSearchItems[$i][1]) Next _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") ;~ When the loop is complete, run the Finished function. Finished() EndIf EndFunc ;==>OpenExcel Func SearchCourse($_sSearchResult, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else _IENavigate($oIE, $urlBBCourseSearch) _IELoadWait($oIE) Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch") Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText") _IEFormElementSetValue($oSearchString, $_sSearchResult) _IEFormSubmit($oForm) _IELoadWait($oIE) Local $oBBTable = _IETableGetCollection($oIE, 2) $aBBTableData = _IETableWriteToArray($oBBTable) Return SearchResult() EndIf EndFunc ;==>SearchCourse Func SearchResult() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $sResult $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0) If $iSearchIndex = -1 Then $sResult = "Course Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Else EnterCourse() $sResult = "UnEnrolled!" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") EndIf Return $sResult EndIf EndFunc ;==>SearchResult Func EnterCourse() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $clickFail = "DATE CREATED" _IELinkClickByIndex($oIE, 34) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $clickFail) <> 0 Then _IELinkClickByIndex($oIE, 35) _IELoadWait($oIE) EndIf $oLink = _IEGetObjById($oIE, "controlpanel.users.and.groups_groupExpanderLink") _IEAction($oLink, "click") Sleep(500) _IELinkClickByText($oIE, "Users") _IELoadWait($oIE) $aSearchResult[$i] = UnenrollNow($aSearchItems[$i][0], $aSearchItems[$i][1]) EndIf EndFunc ;==>EnterCourse Func UnenrollNow($_sourceCourseId, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $UserError = "No users found" Local $sResult $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchKeyString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchOperatorString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') ;Paste whats copied from column B into the Username text box. Local $oForm = _IEGetObjByName($oIE, "userManagerSearchForm") Local $oSearchString = _IEFormElementGetObjByName($oForm, "userInfoSearchText") ;PROBLEM HERE... _IEFormElementSetValue($oSearchString, $_userName) ;^^^^^^ WHY IS $_userName ALWAYS B1 ??? Sleep(1000) _IEFormSubmit($oForm) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $UserError) <> 0 Then $sResult = "User Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult Else MsgBox(0, "Unenrollment READY!", "We're ready to unenroll foreal") $sResult = "DUN" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult EndIf EndIf EndFunc ;==>UnenrollNow Please let me know if any further information is needed. If you see other problems or redundancies in my code, please let me know. Thank you!
  25. Hi, I'm trying to write an formula to the excel, but it actually does not write anything into the cell, any ideas? $formula1="=MID(B"&$utakmice&",SEARCH("-",B"&$utakmice&")-1,1)" _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $formula1, "C"&$smth)
×
×
  • Create New...