DineshPawar

I need to read Excel cell value not formula

7 posts in this topic

#1 ·  Posted (edited)

Hello,

I am new for AutoIT, I need to automate some software , The input is given in Excel format with applied formula.

in my case I need to Read only value of cell which come after applying the formula.

But when I am read Excel cell from AutoIT, It read Formula than actual value , So how can read cell value. 

Any help I appreciate.(Sorry if I did any mistake in this question.)

 

Edited by DineshPawar

Share this post


Link to post
Share on other sites



#2 ·  Posted

@DineshPawar you say you're getting formula instead of value, which means you already have some code. Please post it so we can see what you're doing, rather than making us guess.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

#4 ·  Posted

Well, you have the last parameter set to 2, which returns the formula - not sure what you expect to get? If you want value, change that parameter to 1 as the help file shows.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

@JLogan3o13 if I change last parameter to 1 then it read cell ("A2"). and it fine with that cell because it not contain any formula.

But when I read cell("A3") then it read formula, beacuse cell A3 contain formula.

Edited by DineshPawar

Share this post


Link to post
Share on other sites

#6 ·  Posted

I get all the expected results:

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel2.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel2.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Read the formulas of a cell range (all used cells in column A)
; *****************************************************************************
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 1)
_ArrayDisplay($aResult)
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2)
_ArrayDisplay($aResult)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#7 ·  Posted

@JLogan3o13 and @water , thank for your help, I really appreciate you. That s solve my problem 

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

  • Similar Content

    • Viki
      By Viki
      This is my first time here so please dont bombard me that what a silly question I am asking!!
      I have 500 rows (A1:A500) in a spreadsheet and I just want to copy one by one row and then paste into another application and then press enter, loop should repeat this until finishes all 500 rows.
      I have looked at clipget(), clip(put() but dont know how to select next row in next turn. I also looked at Array to store but again no luck. Can some guide me please..
    • DineshPawar
      By DineshPawar
      0down votefavorite   In my current project, Excel macro open AutoIt script using a function Call Shell("location of autoit script").
      For proper functioning of AutoIt script it's need to be close parent Excel workbook, so this closing Excel action is written in AutoIt script itself.
      But as soon as AutoIt script close parent Excel workbook then script get pause and it do nothing.
      How open the AutoIt script from Excel workbook and after that parent Workbook get close?
    • AndroidZero
      By AndroidZero
      I want to animate transparent GIFs on my gui.
      I searched, read and tested a lot UDFs
      At the end none fits to me
      I wrote my own animated function, but its flickering sometimes just for a miliseconds but still doesnt looks good.

       
      Below is my code for Testing and also the GIF images you need for it.
      GUIChangeImage() is the Animation Function.
      CODE:
      ;************FOR GATHER HTML SOURCE CODE********************** #include <IE.au3> #include <InetConstants.au3> ;************FOR GUI*********** #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <WinAPIFiles.au3> ;**********FOR BUTTON ********** #include <ButtonConstants.au3> #include <StaticConstants.au3> ;**********FOR STRINGS ********** #include <StringConstants.au3> #include <String.au3> ;**********FOR COMBOBOX ********** #include <StringConstants.au3> #include <EditConstants.au3> #include <GuiEdit.au3> #include <GuiComboBox.au3> ;**********FOR FONTS ********** #include <FontConstants.au3> ;**********FOR GIF ANIMATION ********** #include <GIFAnimation.au3> ;**********FOR PROCESS ********** #include <Process.au3> #include <SendMessage.au3> #include <GDIPlus.au3> #include <WinAPIDiag.au3> Opt("GUIOnEventMode",1) Global Const $SC_DRAGMOVE = 0xF012 Global $hGUIAccountCreator Global $graphics_path = @ScriptDir & "\graphics" Global $fontSize_TextBody = 8.5, $fontName_TextBody = "", $fontWeight_TextBody = $FW_BOLD, $fontColor_TextBody = 0x5A2800 GUI_open_AccountCreator() Func GUI_open_AccountCreator() Global $hGUIAccountCreator = GUICreate("Tibia Account Creator - SubZero", 350, 400, -1, -1, $WS_POPUP, BitOR($WS_EX_LAYERED, $WS_EX_TOPMOST)) GUISetOnEvent($GUI_EVENT_CLOSE, "GUI_Close_AccountCreator") GUICtrlCreatePic($graphics_path & "\WindowAccountCreator.gif", 0, 0, 350, 400) GUICtrlSetState(-1, $GUI_DISABLE) ;********* GUI CONTROLS (LABELS,INPUTS,COMBOBOXES) ********************************************************************** GUICtrlCreateLabel(" Create New Account", 25,47,103,14,$SS_CENTERIMAGE) GUICtrlSetBkColor(-1, $GUI_BKCOLOR_TRANSPARENT) GUICtrlSetFont(-1,8,$fontWeight_TextBody,0,$fontName_TextBody,1) GUICtrlSetColor(-1,0xFFFFFF) GUICtrlCreateLabel("Account Name:",30,75,75,14) GUICtrlSetBkColor(-1, $GUI_BKCOLOR_TRANSPARENT) GUICtrlSetFont(-1,8,$fontWeight_TextBody,0,$fontName_TextBody,1) GUICtrlSetColor(-1,0x5A2800) Global $inptBox_Acc = GUICtrlCreateInput("",130,75,150,20) GUICtrlCreateLabel("Email Adress:",30,100,70,14) GUICtrlSetBkColor(-1, $GUI_BKCOLOR_TRANSPARENT) GUICtrlSetFont(-1,8,$fontWeight_TextBody,0,$fontName_TextBody,1) GUICtrlSetColor(-1,0x5A2800) Global $inptBox_Email = GUICtrlCreateInput("",130,100,150,20) GUICtrlCreateLabel("Password:",30,125,55,14) GUICtrlSetBkColor(-1, $GUI_BKCOLOR_TRANSPARENT) GUICtrlSetFont(-1,8,$fontWeight_TextBody,0,$fontName_TextBody,1) GUICtrlSetColor(-1,0x5A2800) Global $inptBox_Passwd = GUICtrlCreateInput("",130,125,150,20,$ES_PASSWORD) GUICtrlSetData($inptBox_Passwd, "") GUICtrlCreateLabel("Character Name:",30,170,83,14) GUICtrlSetBkColor(-1, $GUI_BKCOLOR_TRANSPARENT) GUICtrlSetFont(-1,8,$fontWeight_TextBody,0,$fontName_TextBody,1) GUICtrlSetColor(-1,0x5A2800) Global $inptBox_CharName = GUICtrlCreateInput("",130,170,150,20) GUICtrlCreateLabel("Sex:",30,195,23,14) GUICtrlSetBkColor(-1, $GUI_BKCOLOR_TRANSPARENT) GUICtrlSetFont(-1,$fontSize_TextBody,$fontWeight_TextBody,0,$fontName_TextBody,1) GUICtrlSetFont(-1,8,$fontWeight_TextBody,0,$fontName_TextBody,1) GUICtrlSetColor(-1,$fontColor_TextBody) Global $comboBox_sex = GUICtrlCreateCombo("",130,195,150,20,$CBS_DROPDOWNLIST) ;LoadSexIntoComboBox() GUICtrlCreateLabel("World Location:",30,240,77) GUICtrlSetBkColor(-1, $GUI_BKCOLOR_TRANSPARENT) GUICtrlSetFont(-1,$fontSize_TextBody,$fontWeight_TextBody,0,$fontName_TextBody,1) GUICtrlSetFont(-1,8,$fontWeight_TextBody,0,$fontName_TextBody,1) GUICtrlSetColor(-1,$fontColor_TextBody) Global $comboBox_world = GUICtrlCreateCombo("",130,240,150,20,BitOr($CBS_DROPDOWNLIST, $WS_VSCROLL)) ;LoadWorldsIntoComboBox() Global $label_status = GUICtrlCreateLabel("",20,360,200,30) GUICtrlSetBkColor(-1, $GUI_BKCOLOR_TRANSPARENT) ;********* GUI CONTROLS (LABELS,INPUTS,COMBOBOXES) ********************************************************************** ;********* CONTROL PICS ********************************************************************** Global $btn_randAccName = GUICtrlCreatePic($graphics_path & "\Die_Static.gif", 280, 75, 26, 26) GUICtrlSetTip($btn_randAccName, "Generate a random account name") GUICtrlSetCursor($btn_randAccName,0) ;GUICtrlSetOnEvent($btn_randAccName,"GenerateRandomAccName") GUICtrlSetOnEvent($btn_randAccName,"GUIChangeImage") Global $btn_x = GUICtrlCreatePic("", 315, 5, 40, 36) GUICtrlSetTip($btn_x, "Close") GUICtrlSetCursor($btn_x,0) GUICtrlSetOnEvent($btn_x,"GUI_Close_AccountCreator") Global $btn_donate = GUICtrlCreatePic("", 112, 313, 126, 47) GUICtrlSetTip($btn_donate, "Donate") GUICtrlSetCursor($btn_donate,0) ;GUICtrlSetOnEvent($btn_donate,"Donate") ;********* CONTROL PICS ********************************************************************** ;********* GDI+ DRAW ********************************************************************** _GDIPlus_Startup() Local $hImg = _GDIPlus_ImageLoadFromFile($graphics_path & "\X_Tic-Tac-Toe_Token.gif") Local $hHBMP = _GDIPlus_BitmapCreateHBITMAPFromBitmap($hImg) _WinAPI_DeleteObject(GUICtrlSendMsg($btn_x, 0x0172, $IMAGE_BITMAP, $hHBMP)) _GDIPlus_ImageDispose($hImg) Local $hImg = _GDIPlus_ImageLoadFromFile($graphics_path & "\Die_Static.gif") Local $hHBMP = _GDIPlus_BitmapCreateHBITMAPFromBitmap($hImg) _WinAPI_DeleteObject(GUICtrlSendMsg($btn_randAccName, 0x0172, $IMAGE_BITMAP, $hHBMP)) _GDIPlus_ImageDispose($hImg) Local $hImg = _GDIPlus_ImageLoadFromFile($graphics_path & "\btn_donateCC_LG.gif") Local $hHBMP = _GDIPlus_BitmapCreateHBITMAPFromBitmap($hImg) _WinAPI_DeleteObject(GUICtrlSendMsg($btn_donate, 0x0172, $IMAGE_BITMAP, $hHBMP)) _GDIPlus_ImageDispose($hImg) _GDIPlus_Shutdown() ;********* GDI+ DRAW ********************************************************************** _WinAPI_SetLayeredWindowAttributes($hGUIAccountCreator, 0x26f50b) GUISetIcon(@ScriptDir & "\TibiaInfo.ico") GUISetState(@SW_SHOW, $hGUIAccountCreator) WinSetOnTop($hGUIAccountCreator,"",1) GUIRegisterMsg($WM_LBUTTONDOWN, "_WM_LBUTTONDOWN") EndFunc Func GUIChangeImage() _GDIPlus_Startup() GUICtrlDelete($btn_randAccName) Local $btn_randAccName = GUICtrlCreatePic("", 280, 75, 26, 26) Local $hImg = _GDIPlus_ImageLoadFromFile($graphics_path & "\Die_Frame2.gif") Local $hHBMP = _GDIPlus_BitmapCreateHBITMAPFromBitmap($hImg) _WinAPI_DeleteObject(GUICtrlSendMsg($btn_randAccName, 0x0172, $IMAGE_BITMAP, $hHBMP)) _GDIPlus_ImageDispose($hImg) _WinAPI_SetLayeredWindowAttributes($hGUIAccountCreator, 0x26f50b) Sleep(100) GUICtrlDelete($btn_randAccName) Local $btn_randAccName = GUICtrlCreatePic("", 280, 75, 26, 26) Local $hImg = _GDIPlus_ImageLoadFromFile($graphics_path & "\Die_Frame3.gif") Local $hHBMP = _GDIPlus_BitmapCreateHBITMAPFromBitmap($hImg) _WinAPI_DeleteObject(GUICtrlSendMsg($btn_randAccName, 0x0172, $IMAGE_BITMAP, $hHBMP)) _GDIPlus_ImageDispose($hImg) _WinAPI_SetLayeredWindowAttributes($hGUIAccountCreator, 0x26f50b) Sleep(100) GUICtrlDelete($btn_randAccName) Local $btn_randAccName = GUICtrlCreatePic("", 280, 75, 26, 26) Local $hImg = _GDIPlus_ImageLoadFromFile($graphics_path & "\Die_Frame4.gif") Local $hHBMP = _GDIPlus_BitmapCreateHBITMAPFromBitmap($hImg) _WinAPI_DeleteObject(GUICtrlSendMsg($btn_randAccName, 0x0172, $IMAGE_BITMAP, $hHBMP)) _GDIPlus_ImageDispose($hImg) _WinAPI_SetLayeredWindowAttributes($hGUIAccountCreator, 0x26f50b) Sleep(100) GUICtrlDelete($btn_randAccName) Local $btn_randAccName = GUICtrlCreatePic("", 280, 75, 26, 26) Local $hImg = _GDIPlus_ImageLoadFromFile($graphics_path & "\Die_Frame5.gif") Local $hHBMP = _GDIPlus_BitmapCreateHBITMAPFromBitmap($hImg) _WinAPI_DeleteObject(GUICtrlSendMsg($btn_randAccName, 0x0172, $IMAGE_BITMAP, $hHBMP)) _GDIPlus_ImageDispose($hImg) _WinAPI_SetLayeredWindowAttributes($hGUIAccountCreator, 0x26f50b) Sleep(100) GUICtrlDelete($btn_randAccName) Local $btn_randAccName = GUICtrlCreatePic("", 280, 75, 26, 26) GUICtrlSetCursor($btn_randAccName,0) GUICtrlSetOnEvent($btn_randAccName,"GUIChangeImage") Local $hImg = _GDIPlus_ImageLoadFromFile($graphics_path & "\Die_Static.gif") Local $hHBMP = _GDIPlus_BitmapCreateHBITMAPFromBitmap($hImg) _WinAPI_DeleteObject(GUICtrlSendMsg($btn_randAccName, 0x0172, $IMAGE_BITMAP, $hHBMP)) _GDIPlus_ImageDispose($hImg) _WinAPI_SetLayeredWindowAttributes($hGUIAccountCreator, 0x26f50b) _GDIPlus_Shutdown() EndFunc Func GUI_Close_AccountCreator() Exit EndFunc Func _WM_LBUTTONDOWN($hWnd, $iMsg, $wParam, $lParam) _SendMessage($hGUIAccountCreator, $WM_SYSCOMMAND, $SC_DRAGMOVE, 0) EndFunc While 1 Sleep(10) WEnd  
      IMAGES:
       
       








    • ur
      By ur
      I am maintaining all the reusable code in a separate file as library.au3.
      In that file I have referenced some dependent files using fileinstall, so that they will be extracted when necessary.
       
      Problem is, if I use a function in the library.au3 in another script which doesn't require this dependent file, as I am including the whole file using include tag, it is embedding that file also.
      Is there any way to exclude that.
       
    • GAM
      By GAM
      Am trying to execute perl script from autoit script but its not running. Th command that I have given is...
      $rootDir = automation Run("cmd.exe /" & "C:\" & $rootDir & "\updatesource.pl") Can someone help please!