witek wrote:
tonyhabayeb wrote:
Hello,
I have an excel sheet that contains 10,000 rows and 15 columns.
I want to build a function or VB code that returns for me the rows that
match a specefic condition to another worksheet (in the same file)
I don't know what to search excactly.
I want to do something like SQL query. (Select * from "worksheet"
where....)
I will be glad to hear suggestions or any websittes that have
explination for this thing.


http://support.microsoft.com/kb/257819/EN-US/



Dim Connection As ADODB.Connection
Dim ConnectionString As String

ConnectionString = _

"Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & _

";Extended Properties=Excel 8.0;"



Dim SQL As String



SQL = "INSERT INTO [SheetName$] VALUES('1', '2')"
Set Connection = New ADODB.Connection
Call Connection.Open(ConnectionString)
Call Connection.Execute(SQL, , CommandTypeEnum.adCmdText Or
ExecuteOptionEnum.adExecuteNoRecords)
Connection.Close
Set Connection = Nothing
End Sub


1.
SheetName has additional $ sign at the end when used in FROM clause.

2. Due to bug in oledb you can't refer to the same Workbook as in this
example.
Data must be in closed file.

http://support.microsoft.com/default...;en-us;Q319998

Thnaks,
I'll try to run it and change the query to SELECT.
but, is there anything simplier that I can do?
Something that updates the other sheets 'online' when the main data
sheet is changed. (because I change the sheet of the meta data every
month).