Sign in to follow this  
Followers 0
jfcby

Read Excel Worksheet Cell...

4 posts in this topic

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


Determined -- Devoted -- Delivered Make your mind up -- to seriously apply yourself -- accomplishing the desired results. **** A soft answer turneth away wrath: but grievous words stir up anger. Proverbs 15:1 KJB ****

Share this post


Link to post
Share on other sites



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)

Share this post


Link to post
Share on other sites

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.

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


Determined -- Devoted -- Delivered Make your mind up -- to seriously apply yourself -- accomplishing the desired results. **** A soft answer turneth away wrath: but grievous words stir up anger. Proverbs 15:1 KJB ****

Share this post


Link to post
Share on other sites

@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

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  
Followers 0