Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Extract Calculated Value from Excel Cell

Status
Not open for further replies.

JPimp

Technical User
Mar 27, 2007
79
US
I was trying to get the calculated value from an Excel cell using VB 6.0, but someone recommended I try this using Excel VBA, and then call the variable somehow from VB? I am clueless (well, not completely), when it comes to VB and VBA, so any pointers would be greatly appreciated!

Just call me Spunky
 
Hmm, it is possible it thinks it is an array as the data I am reading from usually sends its data in arrays, but in this case I am capturing what's "current" so to speak, but it is a time-based variable, so it could very well treat it as an array in Excel....if that were the case, can I even call to the cell, and what type would I declare Val as?

Thanks!

Just call me Spunky
 
Hi,

as PHV stated, avoid using names for variables that are also used as VBA commands. Use something like MyVal.

Declaring it as a variant is ok. The problem arises when you try to call it in a messagebox.

You could see what IsArray(MyVal) returns to check whether the variant has been instantiated as an array.

To get it in the messagebox, you can try something like Join(MyVal, " ") or whichever delimiter you want.

Cheers,

Roel
 
I did this:

Code:
sArray (myVal)
      
  myVal = WS.Range("A1").Value
        
Debug.Print (IsArray(myVal))

And the immediate window output was FALSE

So does that mean it is not an array?

Thanks

Just call me Spunky
 
The debug.print sahouldn't have failed even if it is an array

As per my prior post:

does your commandbutton have a "TakeFocusOnClick" property ?

Have you checked that WS has been set properly and is referencing the sheet?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Hi xlbo, no it does not have TakeFocusOnClick property, should it? I am wondering if maybe I have to copy the "value" of the cell A1 to A2 and then call A2 in the VB code instead of trying to read the original formula results of A1?

Just call me Spunky
 
That really shouldn't be an issue. If a cell has a value returned from a formula, you should be able to pick it up in code very easily

Can you post the latest code you have tried ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Code:
 Private Sub Command1_Click()
   Dim ExcelApp As Excel.Application
  Dim WS As Excel.Worksheet
  Dim i As Integer
  Dim stra As String

  Set ExcelApp = CreateObject("excel.application")
  ExcelApp.Workbooks.Open ("C:\Book1.xls")
  Set WS = ExcelApp.ActiveWorkbook.Sheets(1)
 
      
  myVal = WS.Range("A1").Value
       
          
  MsgBox "Value is: " & myVal





 ExcelApp.Quit
    Set WS = Nothing
    Set ExcelApp = Nothing


End Sub



Just call me Spunky
 
What about replacing this:
myVal = WS.Range("A1").Value
with this ?
myVal = WS.Range("A1").Text

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
What is .Sheets(1) in your workbook? This reference includes hidden sheets and other sheet types (as chartsheet, old dialogsheets etc.).

I'd follow some of above hints:

1. set proper references:
Dim WB as excel.workbook
'''
Set WB = ExcelApp.Workbooks.Open ("C:\Book1.xls")
Set WS = WB.WorkSheets(1)
This will remove any ActiveSomething from your code too.

2. test the references, either by Msgbox (WB.Name, WS.Name, WS.Range("A1")) or line by line code execution with 'Locals' window on.

I would also, while testing, set excel visible (xlapp.visible=true).

combo
 
ok, I did something different, I copied the value from A1 to A2 (paste special - value only), and now I can read the value in A2 using the VB code, however, it does not read the updated value, i.e. when Excel opens the file Book1.xls, I have a macro named Auto_Open, which automatically populates A1 with the most recent data, copies the value over to A2, but my VB code only reads the "old" saved value of A2 from Book1, so I assume that the VB code is not invoking the macro or something like that??

Kai-What?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top