A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

VBA in Excel



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2004, 11:16 AM
yaya1899
external usenet poster
 
Posts: n/a
Default 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  
Old June 1st, 2004, 12:04 PM
Jim Rech
external usenet poster
 
Posts: n/a
Default 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  
Old June 1st, 2004, 12:13 PM
CLR
external usenet poster
 
Posts: n/a
Default 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  
Old June 1st, 2004, 01:06 PM
yaya1899
external usenet poster
 
Posts: n/a
Default VBA in Excel

wow...so complex..can any1 giv som simple example?? pls....


---
Message posted from http://www.ExcelForum.com/

  #5  
Old June 1st, 2004, 09:42 PM
Ken Wright
external usenet poster
 
Posts: n/a
Default 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  
Old June 2nd, 2004, 02:20 AM
yaya1899
external usenet poster
 
Posts: n/a
Default 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  
Old June 2nd, 2004, 02:42 AM
CLR
external usenet poster
 
Posts: n/a
Default 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  
Old June 2nd, 2004, 03:48 AM
yaya1899
external usenet poster
 
Posts: n/a
Default 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  
Old June 2nd, 2004, 08:22 PM
Ken Wright
external usenet poster
 
Posts: n/a
Default 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  
Old June 2nd, 2004, 11:26 PM
CLR
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.