getting #N/A as value

[sathishesb]

HI guys,

When i tried to read some range from a closed excel sheet using the below code,

[vba]
Sheet2.Range("A1:H8") = "= 'C:\My Documents\OzGrid\" & "[Book1.xls]Sheet2'!RC"[/vba]

i am getting the text values as #N/A, while i am getting the numbers correctly.

Help me what i need to do to overcome the #N/A error

[Aflatoon]

Are you certain that those values are not actually the saved values in the source worksheet?

[sathishesb]

yes, i could able to get the number in the stored sheet also i am getting zero's for the cells which doesn't have any value, but the problem is if there is any text present in the stored sheet then i am getting #N/A.

[Aflatoon]

Is the text in the source sheet a literal value typed in the cell?

[sathishesb]

No, it's a auto generated sheet from a tool.

[Aflatoon]

That doesn't really address the point of my question - do the cells contain formulas or actual values?

[sathishesb]

sorry

It has the actual values only.
"No formulas"

[Aflatoon]

I am afraid I do not know then. I have never heard of that behaviour before and cannot replicate it, unless the source cells actually contain that error.

[sathishesb]

one thing i want to tell you,

If i am going to open that source excel and close after saving it, the code works fine.

is there any format issue???

[Aflatoon]

Then your output program is doing something odd. I can't imagine what if there are no formulas involved.

[Kenneth Hobs]

From your first post, that only works IF both workbooks are open. To read one cell from a closed workbook use:
[VBA]Sub t()
MsgBox GetValue("x:\test", "test.xlsx", "Sheet1", "A1")
End Sub

'=GetValue("c:\files", "budget.xls", "Sheet1", "A1")
Private Function GetValue(path, file, sheet, ref)
' path = "d:\files"
' file = "budget.xls"
' sheet = "Sheet1"
' ref = "A1:R30"

Dim arg As String

If Right(path, 1) <> "\" Then path = path & "\"

If Dir(path & file) = "" Then
GetValue = "file not found"
Exit Function
End If

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("a1").Address(, , xlR1C1)

GetValue = ExecuteExcel4Macro(arg)
End Function[/VBA]

[Aflatoon]

Kenneth,
The original code works perfectly with a closed source file since it just puts a straight link formula into each cell.

[Kenneth Hobs]

I don't understand what RC is. Should it not be RC1 or something like that or is RC a local name in the sheet?
[VBA]Sheet2.Range("A1:H8") = "= 'C:\My Documents\OzGrid\" & "[Book1.xls]Sheet2'!RC" [/VBA]

[Aflatoon]

RC is R1C1 notation for the same row and column as the formula is entered into. (it is effectively R[0]C[0] as a relative reference).

[frank_m]

No, it's a auto generated sheet from a tool.

what you refer to as a tool, sounds like it may be automating excel to generate and save the values into the source workbook. In which case perhaps the automated instance of excel is not getting shutdown properly due to faulty code in the tool.

What happens if you manually enter some text into a test workbook in some of the same cells with the same text values, then save and close that. Then run your code with the test workbook as the source?