Help on Excel variable

[peterchua89]

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) 

expandcollapse popup

#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.. 

[Jfish]

;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 November 15, 2014 by Jfish

[TheSaint]

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?

[peterchua89]

Thanks @Jfish, just as I needed. 

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

Thanks for the help guys.! 

[TheSaint]

Thanks @Jfish, just as I needed. 

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

Thanks for the help guys.! 

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

[peterchua89]

@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.

[Jfish]

@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 November 15, 2014 by Jfish

[water]

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)

[TheSaint]

@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.