How do I build an excel query? (something like SQL)
 

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.

How do I build an excel query? (something like SQL)
 

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

How do I build an excel query? (something like SQL)
 

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).

How do I build an excel query? (something like SQL)
 

Hello,
I founf the solution.
I can use the "advanced filerring" function.
Here is the code that I used:
Sheets("Data").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:= Sheets("Temp").Range("A1:B3"),
CopyToRange:=Range("A5"), Unique:=False


Information:
"Data" -- The data sheet that includes the table
"Temp" -- the destination sheet for the answer
"A1:B3" -- Contains the conditions of the filterring
"A5" -- the first cell to copy the data

Thanks for all the people who tried to help me :)

How do I build an excel query? (something like SQL)
 

tonyhabayeb wrote:
Hello,
I founf the solution.
I can use the "advanced filerring" function.
Here is the code that I used:
Sheets("Data").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:= Sheets("Temp").Range("A1:B3"),
CopyToRange:=Range("A5"), Unique:=False


Information:
"Data" -- The data sheet that includes the table
"Temp" -- the destination sheet for the answer
"A1:B3" -- Contains the conditions of the filterring
"A5" -- the first cell to copy the data

Thanks for all the people who tried to help me :)



if you need only so simple query you can use advanced filtering.
However it allows you to select, but no modifying data or more advanced
filtering