read/write from closed file???

[ddragas]

I've got database over 30.000 codes (rows).
When I make formula like "=VLOOKUP(B5;'C:\Documents and Settings\Dejan\Desktop\[Ponude.xls]Šifre'!$A$2:$L$32199;5)" it works, but I need VBA code to make the same thing like formula does.

Something like in access and visual basic.

thank you

Best wishes

 

[ddragas]

Thnx Mike for help, but I don't want any formula in worksheet, because user of application is complete beginer in excel, and worksheet needs to be unlocked.
For that reasen I need something like Nimrod sugesed me, but now I need help in that direction.


Here is code that I'm using now:

Application.ScreenUpdating = False

Path = Range("b1")
data = Range("b2")
ime = Range("b3")

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = OpenDatabase(Path & data, False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM [Šifre$]" & _
"WHERE [Šifra] like 'k9'") ' HERE IT SHOULD FIND THE SAME VALUE LIKE I' WROTE IN RANGE("K9")

'While Not rs.EOF
Range("l9") = rs.Fields(1).Value
rs.MoveNext
'Wend

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

Thnx folks

 

[Ekim]

…I don't want any formula in worksheet, because user of application is complete beginer in excel, and worksheet needs to be unlocked

.
See this part of my macro – all formulas are converted to values i.e. the users will not see the formulas:

For i = 1 To 2 
    rng.Offset(0, i).Formula = rng.Offset(0, i).Value 
    Next

BTW, it does not matter if the source file is protected – the macro will still work. If you want to protect the destination file, then in the destination file, unlock the cells that will house the looked up values (select the cells, then go to the Format menu |Cells |Protection and remove the tick from the locked checkbox, then apply protection).

Regards

Mike