read/write from closed file???

For a macro to do this, it wwill need to open the file, extact the data then close the file - which is not a problem.

The destination for this data however is very important. The cell/cells that currently have the formula in it, what sheet is it on and what are the cells.

If you can provide a sample of the workbook it may be helpful.

There are no formulas in worksheet. Everything is calculated with code.

You know.....

If Target.column=2 And Target.Row > 15 then

Dim fn As WorksheetFunction, LookupVal As Variant, sum As Variant
Set fn = WorksheetFunction

bla, bla, bla


end if

etc...



with your suggestion it would be something like:

If Target.column=2 And Target.Row > 15 then

Dim fn As WorksheetFunction, LookupVal As Variant, sum As Variant
Set fn = WorksheetFunction

ChDir "J:\PONUDE\2002"
Workbooks.Open FileName:="J:\PONUDE\2003\Ponude.xls"

Target.Offset(0, 1) = fn.VLookup(Target.Offset(0, 0), Windows("Ponude.xls").Sheets("Šifre").Range("A:P"), 5, False)

Windows("Ponude.xls").Close


end if

etc...


But problem is that specific file with database is big, and it has more than 2 MB, so it needs long time to open it!!

Is there a way to avoyd opening that file, and getting much more performace with application?

VBA requires the workbook to be opened. A formula does not, so with the vba code you seem to have right now with the vlookup formula being inserted into the cell by vb, the workbook does not need to be opened. If you are trying to do this because you want a value in the cell rather that the formul, i suggest copy and pastespecial as value.

VBA does NOT require the Data Workbook to be open . You can use ODBC , DOA etc in conjuction with VBA to extract information from other Excel sheets , MySQL db's , Access etc.
Currently I'm using DOA to extract information from both Access and MySQL databases .

Bellow I will quote just one example:

Retrieving Data from a Named Range in Excel using DAO

Article contributed by Ibby

You can retrieve data from a Named Range in an Excel spreadsheet, without
opening the spreadsheet, by using DAO. The advantages of this, compared to
automating Excel, is that it is much faster and does not require Excel to be
installed on the user's machine.

First, you need to set a reference in your project to the “Microsoft DAO 3.51
(or 3.6) Object Library”. To define a range in Excel, select the cells to be
included in the range, then go Insert | Name | Define and type a name for the range.

The following example retrieves all the data from the first Field (column) in a
range named “myDatabase” in an Excel file called “Book1.xls” located in folder “C:\Test\”.

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

Set db = OpenDatabase("C:\Test\Book1.xls", False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `myDatabase`")

While Not rs.EOF
MsgBox rs.Fields(0).Value
rs.MoveNext
Wend

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

ANOTHER example of getting data from UNOPENED workbooks....(WITHOUT ODBC OR DOA)

This code will get data from a1:A30 from the closed wookbook "Book1.xls" that's located in the Path "C:"

Sub test()
GetValuesFromAClosedWorkbook "C:", "Book1.xls", _
"Sheet1", "A1:A30"
End Sub

Sub GetValuesFromAClosedWorkbook(fPath As String, _
fName As String, sName, cellRange As String)
With ActiveSheet.Range(cellRange)
.FormulaArray = "='" & fPath & "\[" & fName & "]" _
& sName & "'!" & cellRange
.Value = .Value
End With
End Sub

Sorry about that guys

I had asked that question a long time ago, i guess whoever answered that one didn't have grey matter to Nomrod's Standards ray: and have not seen any examples since. I knew you could use vba to insert the formula in a cell, but none of that other stuff.

Eyes are definately wide open to this now.

thnx Nimrod.

what should I put as "myDatabase" in line


Set rs = db.OpenRecordset("SELECT * FROM `myDatabase`")

name of sheet where database or something else?

Anybody??

In sheet2 of the open workbook (WB), set-up your path, file, sheet and range of the closed WB in column B:

Do not put apostrophes around any of the items in column B.
Do include the last back slash in the path (as shown above).

In the open workbook, put the following macro in a standard module and see the Notes below.
Notes:
The macro assumes that the values that you wish to lookup in the closed WB are listed in, sheet1, range A3 downwards, of the open WB (amend the macro to suit).

The macro will place the values from column 5 and column 6 of the closed WB in sheet1, columns B and C, row 3 of the open WB.

Regards

Mike