Jump to content
Sign in to follow this  
uberfist

leading Zeros after copy

Recommended Posts

uberfist

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

Share this post


Link to post
Share on other sites
kylomas

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

Share this post


Link to post
Share on other sites
uberfist

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.:

#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 by uberfist

Share this post


Link to post
Share on other sites
mikell

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 = "@"

Share this post


Link to post
Share on other sites
water

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 (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×