uberfist Posted November 16, 2013 Share Posted November 16, 2013 Hello. New to the forum, and autoit in general. I have a working script, but I have a problem with it. When I copy a cell from excel, it strips the leading zeros. The problem is that the recieving application needs those zeros to find the product. If I manually copy and paste the cells, the Zero's show up.. Help? Bob Link to comment Share on other sites More sharing options...
kylomas Posted November 16, 2013 Share Posted November 16, 2013 uberfist, Can you post the script or a reproducer and an example of the Excel file? kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
uberfist Posted November 16, 2013 Author Share Posted November 16, 2013 (edited) Well, after some diggind, and scratching of the head, i managed to do this: $a=_ExcelReadCell($oExcel,1) if StringLen($a) < 10 Then $b=StringFormat ("%010s\n",$a) else $b=$a EndIf the stringformat function is WAY confusing. Maybe i just need more monster BTW, Here is the code I have so far. The rest is just sending the correct keys and timing to get asset manager to update properly.: expandcollapse popup#include <Excel.au3> Local $sFilePath1 = @ScriptDir & "\inventory_macro.xlsx" ;This file should already exist Local $oExcel = _ExcelBookOpen($sFilePath1) ;----- Open error Handle -------- If @error = 1 Then MsgBox(0, "Error!", "Unable to Create the Excel Object") Exit ElseIf @error = 2 Then MsgBox(0, "Error!", "File does not exist - Shame on you!") Exit EndIf ;---------------------------------- WinActivate("HP Asset Manager") ;switches to Asset Manager send("+{tab}") ;Puts cursor in search field sleep(1000) For $i=1 to 1000 $a=_ExcelReadCell($oExcel,$i,1) If $a="" Then ExitLoop ; if StringLen($a) < 10 Then $b=StringFormat ("%010s\n",$a) else $b=$a EndIf send($b) send("{enter 2}") sleep(5000) send("{tab 9}") send("{ctrl j} WinActivate("HP Asset Manager") ;switches to Asset Manager send("+{tab}") ;Puts cursor in search field sleep(1000) Next ;_ExcelBookClose($oExcel) Edited November 16, 2013 by uberfist Link to comment Share on other sites More sharing options...
mikell Posted November 16, 2013 Share Posted November 16, 2013 You can format the cell to 'text' so the number will be treated as a string For $i=1 to 1000 $oExcel.ActiveSheet.Cells($i,1).NumberFormat = "@" $a=_ExcelReadCell($oExcel,$i,1) or use the _ExcelNumberFormat() func from Excel.au3 with param $sFormat = "@" Link to comment Share on other sites More sharing options...
water Posted November 16, 2013 Share Posted November 16, 2013 The leading zeros are missing because they are not part of the value but are set by the cells format. So before writing the values to the cell, add the leading zeros and write the value as a string plus format the cell as "Text". My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki 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