Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel PC Build | Excel Discussion (Misc queries) | |||
Build a query from a web-based database or a PDF file. | Excel Discussion (Misc queries) | |||
is there a build vs buy template for excel? | Excel Discussion (Misc queries) | |||
build POS system using MS Excel | Excel Programming | |||
How to build build a macro that automatically imports | Excel Programming |