Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quick help - SQL statement in Excel Macro...
Code ------------------- Option Explicit Sub test() Dim inttest inttest = 0 Workbooks("efmimuster").Worksheets("Tabelle1").Cel ls(10, 1) = inttest End Sub ------------------- Hi, this is how far I got in Excel :o Now I would like to implement the following SQL statement: SELECT Count(tblmain.main_id) AS Anzahlvonmain_id FROM tblsbu INNER JOIN ((tblarea INNER JOIN tblcinco ON tblarea.area_i = tblcinco.area_id) INNER JOIN tblmain ON tblcinco.cinco_id tblmain.cinco_id) ON tblsbu.sbu_id = tblcinco.sbu_id GROUP BY tblsbu.sbu, tblarea.area, tblmain.status HAVING (((tblsbu.sbu)="Electronic Technologies") AN ((tblarea.area)="A-1") AND ((tblmain.status)="EF")); I think that I basically need to open an Access session, open th database, run somewhere the sql statement, close it all again. Fo that I believe to have to also implement some kind of Access library i excel. Never done this before though, maybe I don't have to open an Acces session? I read somewhere I can access tables without opening th database... Don't know, could someone pls just show me a standard routine to d this. Thanks a lot -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quick help - SQL statement in Excel Macro...
You can use either native Excel QueryTable... or do it the harder way and code your own ado routine Which is not so complicated as you might think (see http://erlandsendata.no) have you ever used /data /import external data / new db query? either save the query or create with macro recorder running and edit the code. keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool Florianbur wrote: Code: -------------------- Option Explicit Sub test() Dim inttest inttest = 0 Workbooks("efmimuster").Worksheets("Tabelle1").Cel ls(10, 1) = inttest End Sub -------------------- Hi, this is how far I got in Excel :o Now I would like to implement the following SQL statement: SELECT Count(tblmain.main_id) AS Anzahlvonmain_id FROM tblsbu INNER JOIN ((tblarea INNER JOIN tblcinco ON tblarea.area_id = tblcinco.area_id) INNER JOIN tblmain ON tblcinco.cinco_id = tblmain.cinco_id) ON tblsbu.sbu_id = tblcinco.sbu_id GROUP BY tblsbu.sbu, tblarea.area, tblmain.status HAVING (((tblsbu.sbu)="Electronic Technologies") AND ((tblarea.area)="A-1") AND ((tblmain.status)="EF")); I think that I basically need to open an Access session, open the database, run somewhere the sql statement, close it all again. For that I believe to have to also implement some kind of Access library in excel. Never done this before though, maybe I don't have to open an Access session? I read somewhere I can access tables without opening the database... Don't know, could someone pls just show me a standard routine to do this. Thanks a lot. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quick help - SQL statement in Excel Macro...
Florianbur wrote ...
I think that I basically need to open an Access session, open the database, run somewhere the sql statement, close it all again. For that I believe to have to also implement some kind of Access library in excel. You definitely do not want to automate the MS Access app. You need a data access (small 'a') tool. The popular choices for Excel users are MS Query (Data | Import External Data | New Database Query) for GUI tools i.e. has a SQL window you can paste your code into, and ADO if you prefer VBA code. Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Quick Macro help | Excel Discussion (Misc queries) | |||
Need Quick Macro Help! | Excel Discussion (Misc queries) | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Another Quick Simple VBA question (if...then statement) | Excel Programming | |||
Quick VBA code question (if...then statement) | Excel Programming |