Read Excel Worksheet Cell...

[jfcby]

Hello,

I've created a program that reads data from an Excel worksheet to another program. But, AutoIT3 is reading the Excel Worksheet cell that is formatted as date with data like 3/15/2008 as 39522.

How can I get my AutoIT3 program to read the Excel Worksheet cell data like 3/15/2008 instead of 39522?

Thank you for your help,

jfcby

[amokoura]

use the .Text property instead of .Value.

The .Text returns the data as it's visible to the user.

(Or was it .Text.. some property there was for sure)

[jfcby]

use the .Text property instead of .Value.

The .Text returns the data as it's visible to the user.

(Or was it .Text.. some property there was for sure)

Hello,

Thank you for your response!

With the script I'm using where do I change the .value to .text because my script uses:

_ExcelReadCell($oExcel1, $i, 4) and I cannot find any .Value.

expandcollapse popup

Dim $sCellValue1, $sCellValue2, $sCellValue3, $sCellValue4, $sCellValue6, $sCellValue7
     Dim $msg3
    ;Insert Next Part
      _WinAPI_MoveWindow($GUI, $mpX, $mpY, $wwdth, $whght);moves window to upper right corener
      WinSetOnTop($AppTitle, "", 1);window stays on top
     GUICtrlCreateLabel("Insert Next Part", 100, 355, 125, 25)    
     $btn_Next = GUICtrlCreateButton("Next", 235, 350, 75, 25);Left, Down, Width, Height
     GUICtrlCreateLabel("ReInsert Previous Part", 100, 385, 125, 25)      
     $btn_Previous = GUICtrlCreateButton("Previous", 235, 380, 75, 25)   
    ;MsgBox(0, "", "$nrowM = " & $nrowM & " - " & "$lrowM = " & $lrowM) 
    ;MsgBox(0, "", "The Cell Value is: " &  @CRLF & $sCellValue1);& @CRLF & $sCellValue5 & @CRLF & $sCellValue6, )
     $sCellValue1 = StringTrimLeft(_ExcelReadCell($oExcel1, 2, 3), 7);bldgno
     For $i = $nrowM To $lrowM;Loop  
     $sCellValue2 = _ExcelReadCell($oExcel1, $i, 4);serialno 
     $sCellValue3 = _ExcelReadCell($oExcel1, $i, 2);PM Temp ID
     $sCellValue4 = _ExcelReadCell($oExcel1, $i, 7);PM Last Date 
     $sCellValue6 = _ExcelReadCell($oExcel1, $i, 5);Equipment/FF Inv Part
     $sCellValue7 = _ExcelReadCell($oExcel1, $i, 1);Load Equipment Button(LEB) or Equipment Button(EB)
     Do
     $msg3 = GUIGetMsg()
     Select
      Case $msg3 = $GUI_EVENT_CLOSE
        Exit
      Case $msg3 = $btn_Next or $msg3 = $btn_Previous     
       If $msg3 = $btn_Previous Then 
        $i = $i - 2 
        _GUICtrlButton_Enable($btn_Previous, False);Disables Previous Button
       Else
      ;Call Function to Activate/maximize FF Inventory Management - \\Remote window
       winFFInv()
       Sleep(2000) 
    ;Search for PM Template
        Send("{ALT}" & "{DOWN 2}" & "{ENTER 2}");Menu: File > Query/Search
        Send("{ALT}" & "{DOWN}" & "{ENTER}");Menu: File > Reset/(New Search)
        Sleep(2000)
        MouseClick("left", 105, 131);Click in Template ID Field
        Sleep(2000)
        Send($sCellValue3 & "{ENTER}");Type PM Temp ID #        
        Sleep(2000)
        Send("{ALT}" & "{DOWN 3}" & "{ENTER}");Menu: File > Find
        
    ;Insert Data Into PM Template
        Sleep(2000)
        Send("{ALT}" & "{DOWN 7}" & "{ENTER}");Menu; File > Update
        
       If $sCellValue7 = "LEB" Then
        Sleep(2000)
    ;Load Equipment Button Option
        MouseClick("left", 620, 330);Click Load Equipment Button
        Sleep(2000)
        MouseClick("left", 160, 225);Click Equipment Field       
        Sleep(2000)
        Send($sCellValue6 & "{ENTER}");Equipment/FF Inv Part       
        Sleep(2000)
        Send($sCellValue4 & "{ENTER 3}");Type Last PM Date
        Sleep(2000)
        Send($sCellValue1 & "{ENTER 7}");Type Property
        Sleep(2000)
       Else;if $sCellValue7 = "EB" Then 
    ;Equipment Button Option
        Sleep(2000)
        MouseClick("left", 615, 365);Click Equipment Button
        Send("{ALT}" & "{DOWN 6}" & "{ENTER}");Menu: File > Insert
        Sleep(2000)
        MouseClick("left", 75, 290);Click Equipment Field
        Sleep(2000)
        Send($sCellValue6 & "{ENTER}");Equipment/FF Inv Part
        Sleep(2000)
        Send($sCellValue1 & "-" & $sCellValue2 & "{ENTER}");Serialno
        Sleep(2000)
        Send($sCellValue4);PM Last Date
       Endif    
        
        _GUICtrlButton_Enable($btn_Previous);Disables Previous Button

Thank you for your help,

jfcby

[DjDeep00]

@jfcby....Add this function in your script. (Modified the original function from the include library)

Func _ExcelReadCell($oExcel, $sRangeOrRow, $iColumn = 1)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Text
    Else
        Return $oExcel.Activesheet.Range($sRangeOrRow).Text
    EndIf
EndFunc;==>_ExcelReadCell