How to fix the coding?
 

Refer to the post under Worksheet

Working with the file E:\dir\file.xls, under the table sheet, cell C3 is 5,
and
cell C4 is 3.
When I run following codes, the MsgBox displays [file.xls]Table!C4 instead
of the value from this cell 3.
Could anyone give me any suggestions on how to fix it?
Thank everyone very much for any suggestions
Eric

Sub Test
x = TheValue("E:\dir", "file.xls", "Table", "C3")
y = TheValue("E:\dir", "file.xls", "Table", "C4")
If x = y Then
MsgBox ("The value was " & y)
End If
End Sub

How to fix the coding?
 

"TheValue" is a UDF? and the code is ...?

"Eric" wrote:

Refer to the post under Worksheet

Working with the file E:\dir\file.xls, under the table sheet, cell C3 is 5,
and
cell C4 is 3.
When I run following codes, the MsgBox displays [file.xls]Table!C4 instead
of the value from this cell 3.
Could anyone give me any suggestions on how to fix it?
Thank everyone very much for any suggestions
Eric

Sub Test
x = TheValue("E:\dir", "file.xls", "Table", "C3")
y = TheValue("E:\dir", "file.xls", "Table", "C4")
If x = y Then
MsgBox ("The value was " & y)
End If
End Sub

How to fix the coding?
 

Eric,

I think you have copied only part of the code, the function 'The Value' is
missing.

It looks like you are trying to read values from a closed(?) file so try
this:-

Sub human()
Application.DisplayAlerts = False
Path = "c:\" '<======<Change to suit
WorkbookName = "Book2.xls" '<======<Change to suit
Sheet = "Table" '<======<Change to suit
Addr1 = "C3"
Addr2 = "C4"
Worksheets.Add
Range("A1").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet & "'!"
& Addr1
Range("A2").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet & "'!"
& Addr2
x = Range("A1").Value
y = Range("A2").Value
ActiveSheet.Delete
If x = y Then
MsgBox ("The value was " & y)
End If
Application.DisplayAlerts = True
End Sub

Mike


"Eric" wrote:

Refer to the post under Worksheet

Working with the file E:\dir\file.xls, under the table sheet, cell C3 is 5,
and
cell C4 is 3.
When I run following codes, the MsgBox displays [file.xls]Table!C4 instead
of the value from this cell 3.
Could anyone give me any suggestions on how to fix it?
Thank everyone very much for any suggestions
Eric

Sub Test
x = TheValue("E:\dir", "file.xls", "Table", "C3")
y = TheValue("E:\dir", "file.xls", "Table", "C4")
If x = y Then
MsgBox ("The value was " & y)
End If
End Sub

How to fix the coding?
 

There is the code for TheValue
Does anyone have any suggestions?
Thank everyone for any suggestions
Eric

Sub Test
x = TheValue("E:\dir", "file.xls", "Table", "C3")
y = TheValue("E:\dir", "file.xls", "Table", "C4")
If x = y Then
MsgBox ("The value was " & y)
End If
End Sub

Function TheValue(Path, WorkbookName, Sheet, Addr) As String
TheValue = "[" & WorkbookName & "]" & Sheet & "'!" & Addr
End Function


"Toppers" wrote:

"TheValue" is a UDF? and the code is ...?

"Eric" wrote:

Refer to the post under Worksheet

Working with the file E:\dir\file.xls, under the table sheet, cell C3 is 5,
and
cell C4 is 3.
When I run following codes, the MsgBox displays [file.xls]Table!C4 instead
of the value from this cell 3.
Could anyone give me any suggestions on how to fix it?
Thank everyone very much for any suggestions
Eric

Sub Test
x = TheValue("E:\dir", "file.xls", "Table", "C3")
y = TheValue("E:\dir", "file.xls", "Table", "C4")
If x = y Then
MsgBox ("The value was " & y)
End If
End Sub

How to fix the coding?
 

Thank everyone for suggestions
I try not to read any value from a closed file, so when I open
E:\dir\file.xls, all required values are already collected under worksheet
Table C3 & C4
Do you have any suggestions?
Thank everyone for any suggestions
Eric

Sub Test
x = TheValue("E:\dir", "file.xls", "Table", "C3")
y = TheValue("E:\dir", "file.xls", "Table", "C4")
If x = y Then
MsgBox ("The value was " & y)
End If

Function TheValue(Path, WorkbookName, Sheet, Addr) As String
TheValue = "[" & WorkbookName & "]" & Sheet & "'!" & Addr
End Function

End Sub


"Mike H" wrote:

Eric,

I think you have copied only part of the code, the function 'The Value' is
missing.

It looks like you are trying to read values from a closed(?) file so try
this:-

Sub human()
Application.DisplayAlerts = False
Path = "c:\" '<======<Change to suit
WorkbookName = "Book2.xls" '<======<Change to suit
Sheet = "Table" '<======<Change to suit
Addr1 = "C3"
Addr2 = "C4"
Worksheets.Add
Range("A1").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet & "'!"
& Addr1
Range("A2").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet & "'!"
& Addr2
x = Range("A1").Value
y = Range("A2").Value
ActiveSheet.Delete
If x = y Then
MsgBox ("The value was " & y)
End If
Application.DisplayAlerts = True
End Sub

Mike


"Eric" wrote:

Refer to the post under Worksheet

Working with the file E:\dir\file.xls, under the table sheet, cell C3 is 5,
and
cell C4 is 3.
When I run following codes, the MsgBox displays [file.xls]Table!C4 instead
of the value from this cell 3.
Could anyone give me any suggestions on how to fix it?
Thank everyone very much for any suggestions
Eric

Sub Test
x = TheValue("E:\dir", "file.xls", "Table", "C3")
y = TheValue("E:\dir", "file.xls", "Table", "C4")
If x = y Then
MsgBox ("The value was " & y)
End If
End Sub