Sign in to follow this  
Followers 0
peterchua89

Help on Excel variable

9 posts in this topic

Hi all,

Good day, If anyone here can help me out with below coding? My objective is simple, just wanted the MsgBox to display those Global Variable "Text". 

I just want the $vResult in MsgBox to be displaying in Text from Global Variable.. (Eg, 1-Bedroom, 2-Bedroom, etc)  :sweating:

#include <IE.au3>
#include <Excel.au3>
#include <ExcelConstants.au3>
#include <Word.au3>
#include <WordConstants.au3>
#include <array.au3>
#include <MsgBoxConstants.au3>
#include <Process.au3>
#Include <Misc.au3>
#include <GUIConstants.au3>
#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>



; Create application object and open an example workbook
Local $oAppl = _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($oAppl, @DesktopDir & "\Propwall_Bot\Excel.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf

; *****************************************************************************
; Read the formulas of a cell range (all used cells in column A)
; *****************************************************************************
Global $vStudio = "Studio"
Global $v1Br = "1-Bedroom"
Global $v2Br = "2-Bedroom"
Global $v3Br = "3-Bedroom"
Global $v4Br = "4-Bedroom"
Global $v5Br = "5-Bedroom"
Global $v6Br = "6-Bedroom"
Global $v7Br = "6+ Bedroom"

Global $vResult = _Excel_RangeRead($oWorkbook, Default, "E3")
MsgBox($MB_SYSTEMMODAL, $vResult, $vResult)

If $vResult = 1 Then
    Global $v1Br = "1-Bedroom"
EndIf

Thanks for your kind help..  :D

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

;Without knowing what is in your spreadsheet I think you want something like this:
Switch $vResult
     case 1
          $text="1-Bedroom"
     case 2
          $text="2-Bedroom"
    case 3
          $text="3-Bedroom"
    case 4
          $text="4-Bedroom"
    case 5
          $text="5-Bedroom"
     case 6
          $text="6-Bedroom"
     case 7
          $text="7-Bedroom"
EndSwitch
MsgBox($MB_SYSTEMMODAL, $text, $text)

Edited by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

I'm a bit lost over whether you are wanting a VALUE or FORMULA returned and what $vResult might be?

Your code seems to be looking at a number being returned?


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites

Thanks @Jfish, just as I needed. 

@TheSaint, forgive me Im kind of new in coding..  :idea:

Thanks for the help guys.!  :ILA2:

Share this post


Link to post
Share on other sites

Thanks @Jfish, just as I needed. 

@TheSaint, forgive me Im kind of new in coding..  :idea:

Thanks for the help guys.!  :ILA2:

Nothing to forgive, though I'm sure we'd all appreciate knowing the answers to my questions.


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites

@TheSaint the ojbective I want is actually to read any active cell in an excel spreadsheet, it can be value 1, 2, 3, etc.

using this value I want it to return me into words like "1-Bedroom, 2-Bedroom, etc" then using this words as variable and replace to other Function.

Any suggestion you can give to refine the coding? Now Im almost completing my script but is full of mess, I guess you guys will laugh when you see my .au3 script.. lol.

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

@TheSaint -

I think some of the comments in the code may have been misleading to you.  My take on it was that they were inadvertently copied from an example.  This is from the help file verbatim (under _Excel_RangeRead):

; *****************************************************************************
; Read the formulas of a cell range (all used cells in column A)
; *****************************************************************************

Also worth noting he did not reference formula in the OP.  I took a lucky guess because it looked like @peterchua89 was trying to use conditional logic to pick between several different variables when he really just needed to assign the value to one variable. 

Edited by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

If you just want to read the value of a cell and then append "-Bedroom" I suggest the following code:

#include <Excel.au3>

Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oAppl, @DesktopDir & "\Propwall_Bot\Excel.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl, False)
    Exit
EndIf
Local $vResult = _Excel_RangeRead($oWorkbook, Default, "E3")
MsgBox($MB_SYSTEMMODAL, "Result", $vResult & "-Bedroom")

_Excel_Close($oAppl, False)

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

@TheSaint the ojbective I want is actually to read any active cell in an excel spreadsheet, it can be value 1, 2, 3, etc.

using this value I want it to return me into words like "1-Bedroom, 2-Bedroom, etc" then using this words as variable and replace to other Function.

Any suggestion you can give to refine the coding? Now Im almost completing my script but is full of mess, I guess you guys will laugh when you see my .au3 script.. lol.

No worries, I was just curious, especially over your possible use of numbers in your Excel document rather than the words you eventually get. It seemed to me, that a step may have been able to be saved, unless you were using formulas for those cells.

We won't laugh. A chuckle perhaps. We all started somewhere, and I can remember many milestones of enlightenment on my programming journey. So don't be ashamed to show us. All of us are still learning.

@Jfish - Even though my relatively recent (probably previous) Help file doesn't have that particular function, I did realize it was something that had been edited, but I like to be sure of all relevant aspects when dealing with beginners.

1 person likes this

AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

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