Reading integer values from a spreadsheet

V

veggiesaregood

I recently found out how to read values from a sheet in another closed
workbook using the GetValue function I have pasted below. It builds a
string and then calls ExecuteExcel4Macro to return the value from the
other sheet. In my application, all the values in the sheet being read
are integers and I am trying to store the values I read as integers so
I can do math operations on them. However, if I try to cast the return
value as an integer I get a type mismatch error. (example: temp =
Val(GetValue(.....)) returns an error).

Can someone please advise me on how I can access these values as
integers.

Thanks!

This is a minor adaptation I based on John Walkenbach's GetValue()
function so that works cross-platform:


Public Function GetValue(Path, File, Sheet, Ref) As Variant
'Based on John Walkenbach's GetValue function:
'http://www.j-walk.com/ss/excel /tips/tip82.htm
Const sTEMPLATE As String = "'&P[&F]&S'!&R"
Dim sSEP As String
Dim sArg As String


sSEP = Application.PathSeparator
If Right(Path, 1) <> sSEP Then Path = Path & sSEP
If Dir(Path & File) = "" Then
GetValue = "File Not Found"
Else
With Application
sArg = .Substitute(.Substitute(.Subst
itute(.Substitute( _
sTEMPLATE, "&R", Range(Ref).Address(True, True,
xlR1C1)), _
"&S", Sheet), "&F", File), "&P", Path)
End With
GetValue = ExecuteExcel4Macro(sArg)
End If
End Function


Note that it uses an XL4M command. It cannot be used from the
worksheet,
but works fine when called by a macro.
 
F

Fredrik Wahlgren

I recently found out how to read values from a sheet in another closed
workbook using the GetValue function I have pasted below. It builds a
string and then calls ExecuteExcel4Macro to return the value from the
other sheet. In my application, all the values in the sheet being read
are integers and I am trying to store the values I read as integers so
I can do math operations on them. However, if I try to cast the return
value as an integer I get a type mismatch error. (example: temp =
Val(GetValue(.....)) returns an error).

Can someone please advise me on how I can access these values as
integers.

Thanks!

This is a minor adaptation I based on John Walkenbach's GetValue()
function so that works cross-platform:


Public Function GetValue(Path, File, Sheet, Ref) As Variant
'Based on John Walkenbach's GetValue function:
'http://www.j-walk.com/ss/excel /tips/tip82.htm
Const sTEMPLATE As String = "'&P[&F]&S'!&R"
Dim sSEP As String
Dim sArg As String


sSEP = Application.PathSeparator
If Right(Path, 1) <> sSEP Then Path = Path & sSEP
If Dir(Path & File) = "" Then
GetValue = "File Not Found"
Else
With Application
sArg = .Substitute(.Substitute(.Subst
itute(.Substitute( _
sTEMPLATE, "&R", Range(Ref).Address(True, True,
xlR1C1)), _
"&S", Sheet), "&F", File), "&P", Path)
End With
GetValue = ExecuteExcel4Macro(sArg)
End If
End Function


Note that it uses an XL4M command. It cannot be used from the
worksheet,
but works fine when called by a macro.
Click to expand...

You can typecast by using the CInt function. I have made something similar
to this. the most obvious difference is the lline
GetValue = ExecuteExcel4Macro(sArg)

I used this instead
GetValue = Application.Evaluate(sArg)

Also, I don't think the worksheet you refer to can be closed

/Fredrik
 
B

Bob Greenblatt

I recently found out how to read values from a sheet in another closed
workbook using the GetValue function I have pasted below. It builds a
string and then calls ExecuteExcel4Macro to return the value from the
other sheet. In my application, all the values in the sheet being read
are integers and I am trying to store the values I read as integers so
I can do math operations on them. However, if I try to cast the return
value as an integer I get a type mismatch error. (example: temp =
Val(GetValue(.....)) returns an error).

Can someone please advise me on how I can access these values as
integers.

Thanks!

This is a minor adaptation I based on John Walkenbach's GetValue()
function so that works cross-platform:


Public Function GetValue(Path, File, Sheet, Ref) As Variant
'Based on John Walkenbach's GetValue function:
'http://www.j-walk.com/ss/excel /tips/tip82.htm
Const sTEMPLATE As String = "'&P[&F]&S'!&R"
Dim sSEP As String
Dim sArg As String


sSEP = Application.PathSeparator
If Right(Path, 1) <> sSEP Then Path = Path & sSEP
If Dir(Path & File) = "" Then
GetValue = "File Not Found"
Else
With Application
sArg = .Substitute(.Substitute(.Subst
itute(.Substitute( _
sTEMPLATE, "&R", Range(Ref).Address(True, True,
xlR1C1)), _
"&S", Sheet), "&F", File), "&P", Path)
End With
GetValue = ExecuteExcel4Macro(sArg)
End If
End Function


Note that it uses an XL4M command. It cannot be used from the
worksheet,
but works fine when called by a macro.
Click to expand...

What is getvalue returning? Try multiplying the result by 1.
 
V

veggiesaregood

GetValue returns a variant...I try using the CInt function to convert
it to an integer and it gives me an integer but it isn't the same
value. For instance, if GetValue reads a 0 and then I call CInt on the
result I get 2020.

Thanks again.


Bob said:
I recently found out how to read values from a sheet in another closed
workbook using the GetValue function I have pasted below. It builds a
string and then calls ExecuteExcel4Macro to return the value from the
other sheet. In my application, all the values in the sheet being read
are integers and I am trying to store the values I read as integers so
I can do math operations on them. However, if I try to cast the return
value as an integer I get a type mismatch error. (example: temp =
Val(GetValue(.....)) returns an error).

Can someone please advise me on how I can access these values as
integers.

Thanks!

This is a minor adaptation I based on John Walkenbach's GetValue()
function so that works cross-platform:


Public Function GetValue(Path, File, Sheet, Ref) As Variant
'Based on John Walkenbach's GetValue function:
'http://www.j-walk.com/ss/excel /tips/tip82.htm
Const sTEMPLATE As String = "'&P[&F]&S'!&R"
Dim sSEP As String
Dim sArg As String


sSEP = Application.PathSeparator
If Right(Path, 1) <> sSEP Then Path = Path & sSEP
If Dir(Path & File) = "" Then
GetValue = "File Not Found"
Else
With Application
sArg = .Substitute(.Substitute(.Subst
itute(.Substitute( _
sTEMPLATE, "&R", Range(Ref).Address(True, True,
xlR1C1)), _
"&S", Sheet), "&F", File), "&P", Path)
End With
GetValue = ExecuteExcel4Macro(sArg)
End If
End Function


Note that it uses an XL4M command. It cannot be used from the
worksheet,
but works fine when called by a macro.
Click to expand...

What is getvalue returning? Try multiplying the result by 1.
Click to expand...
 
F

Fredrik Wahlgren

I recently found out how to read values from a sheet in another closed
workbook using the GetValue function I have pasted below. It builds a
string and then calls ExecuteExcel4Macro to return the value from the
other sheet. In my application, all the values in the sheet being read
are integers and I am trying to store the values I read as integers so
I can do math operations on them. However, if I try to cast the return
value as an integer I get a type mismatch error. (example: temp =
Val(GetValue(.....)) returns an error).

Can someone please advise me on how I can access these values as
integers.

Thanks!

This is a minor adaptation I based on John Walkenbach's GetValue()
function so that works cross-platform:


Public Function GetValue(Path, File, Sheet, Ref) As Variant
'Based on John Walkenbach's GetValue function:
'http://www.j-walk.com/ss/excel /tips/tip82.htm
Const sTEMPLATE As String = "'&P[&F]&S'!&R"
Dim sSEP As String
Dim sArg As String


sSEP = Application.PathSeparator
If Right(Path, 1) <> sSEP Then Path = Path & sSEP
If Dir(Path & File) = "" Then
GetValue = "File Not Found"
Else
With Application
sArg = .Substitute(.Substitute(.Subst
itute(.Substitute( _
sTEMPLATE, "&R", Range(Ref).Address(True, True,
xlR1C1)), _
"&S", Sheet), "&F", File), "&P", Path)
End With
GetValue = ExecuteExcel4Macro(sArg)
End If
End Function


Note that it uses an XL4M command. It cannot be used from the
worksheet,
but works fine when called by a macro.
Click to expand...

You can typecast by using the CInt function. I have made something similar
to this. the most obvious difference is the lline
GetValue = ExecuteExcel4Macro(sArg)

I used this instead
GetValue = Application.Evaluate(sArg)

Also, I don't think the worksheet you refer to can be closed

/Fredrik
 
J

JE McGimpsey

Fredrik Wahlgren said:
Also, I don't think the worksheet you refer to can be closed
Click to expand...

A closed workbook is exactly *why* the XL4M command is used rather than
VBA.
 
J

JE McGimpsey

GetValue returns a variant...I try using the CInt function to convert
it to an integer and it gives me an integer but it isn't the same
value. For instance, if GetValue reads a 0 and then I call CInt on the
result I get 2020.
Click to expand...

How are you determining that GetValue = 0?

What is the actual value and type in the target workbook? Does GetValue
return it?
 
F

Fredrik Wahlgren

JE McGimpsey said:
A closed workbook is exactly *why* the XL4M command is used rather than
VBA.
Click to expand...

Now, that's interesting. I think I should try this on the macro that I
created for my friend. She complained about this.

/Fredrik
 
V

veggiesaregood

Yeah, I am using the XL4M command so that I can read from a closed
workbook. I guess I haven't determined for sure that GetValue = 0. I
know that the cell I am attempting to read from has a value of 0 stored
in it but I don't know of a way to check that this is the value being
stored in GetValue. Do you know of a way to check this being that
GetValue is a Variant? Do you think that the CInt function should be
returning me the correct value and I am just not getting the correct
value from GetValue?

Thanks again.
 
V

veggiesaregood

JE,

Thanks for your help. My mistake was that the values being read by
GetValue were '0' but they were strings, not integers. I used CStr
instead of CInt and then cast this to an integer and this seems to work.
 
F

Fredrik Wahlgren

JE,

Thanks for your help. My mistake was that the values being read by
GetValue were '0' but they were strings, not integers. I used CStr
instead of CInt and then cast this to an integer and this seems to work.
Click to expand...

When you test a program like this, it's generally a bad idea to pull values
from cells with possible values like 0, '0' or "". These are default values
for integer or string parameters,. It's much better to use 123, '123' or
"Whatever".

/Fredrik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ExecuteExcel4Macro 4
ExecuteExcel4Macro Error 2
GetValue from closed workbook 1
Closed workbook 1
Import data from another Worksheet 2
Return Value from Form 8
Import data from a closed workbook 2
Error retrieving data from closed workbook 5

Top