PDA

View Full Version : getting #N/A as value



sathishesb
07-28-2011, 03:04 AM
HI guys,

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


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

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 :banghead:

Aflatoon
07-28-2011, 03:19 AM
Are you certain that those values are not actually the saved values in the source worksheet?

sathishesb
07-28-2011, 03:31 AM
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.:dunno

Aflatoon
07-28-2011, 03:38 AM
Is the text in the source sheet a literal value typed in the cell?

sathishesb
07-28-2011, 03:45 AM
No, it's a auto generated sheet from a tool.

Aflatoon
07-28-2011, 03:50 AM
That doesn't really address the point of my question - do the cells contain formulas or actual values?

sathishesb
07-28-2011, 03:56 AM
sorry

It has the actual values only.
"No formulas"

Aflatoon
07-28-2011, 04:02 AM
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
07-28-2011, 04:07 AM
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
07-28-2011, 05:28 AM
Then your output program is doing something odd. I can't imagine what if there are no formulas involved.

Kenneth Hobs
07-28-2011, 05:36 AM
From your first post, that only works IF both workbooks are open. To read one cell from a closed workbook use:
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

Aflatoon
07-28-2011, 05:41 AM
Kenneth,
The original code works perfectly with a closed source file since it just puts a straight link formula into each cell.

Kenneth Hobs
07-28-2011, 05:56 AM
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?
Sheet2.Range("A1:H8") = "= 'C:\My Documents\OzGrid\" & "[Book1.xls]Sheet2'!RC"

Aflatoon
07-28-2011, 05:59 AM
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
07-28-2011, 08:35 AM
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?