is it possible to get an object without opening the book
 

hi,

is it possible to get an object without opening the book
i mean ,for instance how to get a cell value without opening the book in vba

thanks

is it possible to get an object without opening the book
 

no


"EXCEL$B!!(BNEWS" wrote in message
...
hi,

is it possible to get an object without opening the book
i mean ,for instance how to get a cell value without opening the book in
vba

thanks

is it possible to get an object without opening the book
 

Public Sub GetData()
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String
Dim ary

sFilename = "c:\Mytest\Volker1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM [Sales$]"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, 0, 1, 1

If Not oRS.EOF Then
ary = oRS.getrows
MsgBox "Cell A2: " & ary(0, 0)
MsgBox "Cell D4: " & ary(3, 2)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing

End Sub




--

HTH

RP
(remove nothere from the email address if mailing direct)


"EXCEL$B!!(BNEWS" wrote in message
...
hi,

is it possible to get an object without opening the book
i mean ,for instance how to get a cell value without opening the book in
vba

thanks

is it possible to get an object without opening the book
 

Another method

Sub test()
MsgBox GetValue("C:\test", "A1.xls", "Sheet1", "A1")
End Sub

Function GetValue(path, file, sheet, range_ref)
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(range_ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function


--
http://www.vba.com.tw/plog/


"EXCEL　NEWS" wrote:

hi,

is it possible to get an object without opening the book
i mean ,for instance how to get a cell value without opening the book in vba

thanks

is it possible to get an object without opening the book
 

You and John Walkenbach must be of like mind:

http://www.j-walk.com/ss/excel/tips/tip82.htm
A VBA Function to Get a Value From a Closed File

Nonetheless, this would be a real slow way to get a single value.

Faster would be to build a link in the cell and retrieve the value returned.

Function GetValue(path, file, sheet, range_ref)
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(range_ref).Range("A1").Address(, , xlR1C1)
with Activesheet.Range("B9")
.Formula = "=" & arg
GetValue = .Value
.ClearContents
end With
End Function

--
Regards,
Tom Ogilvy

"chijanzen" wrote in message
...

Another method

Sub test()
MsgBox GetValue("C:\test", "A1.xls", "Sheet1", "A1")
End Sub

Function GetValue(path, file, sheet, range_ref)
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(range_ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function


--
http://www.vba.com.tw/plog/


"EXCEL?NEWS" wrote:

hi,

is it possible to get an object without opening the book
i mean ,for instance how to get a cell value without opening the book in
vba

thanks

is it possible to get an object without opening the book
 

Tom Ogilvy skrev:

You and John Walkenbach must be of like mind:

http://www.j-walk.com/ss/excel/tips/tip82.htm
A VBA Function to Get a Value From a Closed File


Heh, good observation :)

What WAS strange was the actual differences (or difference, mayhaps).

/impslayer, aka Birger Johansson, not contributing at all