If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
VBA in Excel
Halo, im a newbie 2 both VBA and Excel, I was assigned to design a VBA
script which can post query and retrieve data from an excel sheet, is it possible to do so?..If yes, what is the 1st step should i follow?....and im a newbie 2 visual basic as well. Here is an example of my requirements... Lets said I post a query, Select all employee from emp1 where sex = female and age 20, and the VBA will retrieve the data from excel which match the query... I jus know this can b done by a Java program with a linking to access by ODBC.... pls...tis is urgent, any1 can help?? thankz.....(^_2) --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
VBA in Excel
There are numerous articles on running database queries from Excel in the MS
Knowledgebase. For example: http://support.microsoft.com/default...86&Product=xlw See links to other articles at the bottom of this one. -- Jim Rech Excel MVP "yaya1899 " wrote in message ... | Halo, im a newbie 2 both VBA and Excel, I was assigned to design a VBA | script which can post query and retrieve data from an excel sheet, is | it possible to do so?..If yes, what is the 1st step should i | follow?....and im a newbie 2 visual basic as well. | | Here is an example of my requirements... | Lets said I post a query, Select all employee from emp1 where sex = | female and age 20, and the VBA will retrieve the data from excel | which match the query... | | I jus know this can b done by a Java program with a linking to access | by ODBC.... | | pls...tis is urgent, any1 can help?? | | thankz.....(^_2) | | | --- | Message posted from http://www.ExcelForum.com/ | |
#3
|
|||
|
|||
VBA in Excel
Just be certain you have rights and an access path to get into the Database
you wish to query.........I spun my wheels a lot before I knew this........... Vaya con Dios, Chuck, CABGx3 "yaya1899 " wrote in message ... Halo, im a newbie 2 both VBA and Excel, I was assigned to design a VBA script which can post query and retrieve data from an excel sheet, is it possible to do so?..If yes, what is the 1st step should i follow?....and im a newbie 2 visual basic as well. Here is an example of my requirements... Lets said I post a query, Select all employee from emp1 where sex = female and age 20, and the VBA will retrieve the data from excel which match the query... I jus know this can b done by a Java program with a linking to access by ODBC.... pls...tis is urgent, any1 can help?? thankz.....(^_2) --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
VBA in Excel
wow...so complex..can any1 giv som simple example?? pls....
--- Message posted from http://www.ExcelForum.com/ |
#5
|
|||
|
|||
VBA in Excel
Do you just want to do this in Excel or do Access and Java come into this
somewhere. Assuming the Access and Java bit was a red herring, then what you outline can easily be done with no VBA and just using Data / Filter / Autofilter plus the 'custon' option from the dropdowns once you have applied the filter. Is there a real need for VBA or did they just not realise it can be done without. This all assumes your data is normalised and is in typical database format. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "yaya1899 " wrote in message ... Halo, im a newbie 2 both VBA and Excel, I was assigned to design a VBA script which can post query and retrieve data from an excel sheet, is it possible to do so?..If yes, what is the 1st step should i follow?....and im a newbie 2 visual basic as well. Here is an example of my requirements... Lets said I post a query, Select all employee from emp1 where sex = female and age 20, and the VBA will retrieve the data from excel which match the query... I jus know this can b done by a Java program with a linking to access by ODBC.... pls...tis is urgent, any1 can help?? thankz.....(^_2) --- Message posted from http://www.ExcelForum.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
#6
|
|||
|
|||
VBA in Excel
Nope, not in java n Access, jus VBA and Excel onli...i jus nid a very
very simple example...jus like the connection string, dbdriver.....n the sql....pls ... tq very very much~~~ --- Message posted from http://www.ExcelForum.com/ |
#7
|
|||
|
|||
VBA in Excel
Maybe just try doing Data Get External Data New Database
Query..........with the Macro recorder on and see if thats what you're looking for....... Otherwise, here's some code from one of my Queries....(don't ask me to explain it)g Dim inputstr Const sFileName As String = "BOMQuery" Worksheets("BOMQuery").Activate inputstr = Range("BOMNumber").Value With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=fs1 - MSS-Fourth Shift;ServerName=FS1.1583;ServerDSN=fs1 - MSS-Fourth Shift;UID=clr;PWD=chuck;ArrayFetchOn=1;ArrayBuffer Size=8" _ ), Array(";DBQ=M:\Mfgsys\System;CODEPAGE=1252;")), Destination:=Range("A1")) .CommandText = Array( _ "SELECT ITEM_MULTILEVELBILL.END_ITEM, ITEM_MULTILEVELBILL.LEVEL, ITEM_MULTILEVELBILL.PT_USE, ITEM_MULTILEVELBILL.SEQN, ITEM_MULTILEVELBILL.IN_REV, ITEM_MULTILEVELBILL.COM_TYP, ITEM_MULTILEVELBILL.COMPONEN" _ , _ "T, ITEM_MULTILEVELBILL.COMP_DESC, ITEM_MULTILEVELBILL.QUANTITY, ITEM_MULTILEVELBILL.COMP_UM, ITEM_MULTILEVELBILL.QTY_TYP, ITEM_MULTILEVELBILL.PARNT_DESC" & Chr(13) & "" & Chr(10) & "FROM ITEM_MULTILEVELBILL ITEM_MULTILEVELBILL" & Chr(13) & "" & Chr(10) & "WHERE (ITEM_MULTILEVELBILL.END_ITE" _ , "M='" & inputstr & "')") .name = "Query from FS - MSS-Fourth Shift_1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Range("Parnt_Desc").Value = Range("L2").Text End Sub "yaya1899 " wrote in message ... Nope, not in java n Access, jus VBA and Excel onli...i jus nid a very very simple example...jus like the connection string, dbdriver.....n the sql....pls ... tq very very much~~~ --- Message posted from http://www.ExcelForum.com/ |
#8
|
|||
|
|||
VBA in Excel
ops, stil cant work, syntax error....
thankz for ur kindness, CLR... actually i would like to hav an simple example like this.. (i) a simple excel file with some data (ii) a simple query in VBA which is linked to the excel and can retrieve data from excel thankz again~ --- Message posted from http://www.ExcelForum.com/ |
#9
|
|||
|
|||
VBA in Excel
I'm confused!! If you only have Excel in this picture, then where is the need
for the SQL? Give us some more detail about the data, where and how it is stored, and whether there is a mandated need for VBA or is it simply a perception that VBA is needed. Eg, my data is in a single Excel workbook, with a big table of data on a single worksheet, and in another sheet I would like to specify certain criteria and get back matching data. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "yaya1899 " wrote in message ... ops, stil cant work, syntax error.... thankz for ur kindness, CLR... actually i would like to hav an simple example like this.. (i) a simple excel file with some data (ii) a simple query in VBA which is linked to the excel and can retrieve data from excel thankz again~ --- Message posted from http://www.ExcelForum.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
#10
|
|||
|
|||
VBA in Excel
Right on Ken.............this sounds like exactly what I'm doing at work
with an Advanced Filter.......I just set it up according to the menus at Data Filter Advanced Filter...... and used a Recorded Macro (VBA if you will), to fire it off each time after I enter the new Criteria......it took a little work to set it up, but now it's a breeze..........just type in the criteria and push the button and away it goes.........searches my 1700 line database and returns exactly what the criteria specified. Vaya con Dios, Chuck, CABGx3 "Ken Wright" wrote in message ... I'm confused!! If you only have Excel in this picture, then where is the need for the SQL? Give us some more detail about the data, where and how it is stored, and whether there is a mandated need for VBA or is it simply a perception that VBA is needed. Eg, my data is in a single Excel workbook, with a big table of data on a single worksheet, and in another sheet I would like to specify certain criteria and get back matching data. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "yaya1899 " wrote in message ... ops, stil cant work, syntax error.... thankz for ur kindness, CLR... actually i would like to hav an simple example like this.. (i) a simple excel file with some data (ii) a simple query in VBA which is linked to the excel and can retrieve data from excel thankz again~ --- Message posted from http://www.ExcelForum.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
|
Thread Tools | |
Display Modes | |
|
|