leading Zeros after copy

[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

[kylomas]

uberfist,

Can you post the script or a reproducer and an example of the Excel file?

kylomas

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

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

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

[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".