VBA in Excel

[yaya1899]

Halo, im a newbie 2 both VBA and Excel, I was assigned to design a VB
script which can post query and retrieve data from an excel sheet, i
it possible to do so?..If yes, what is the 1st step should
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 exce
which match the query...

I jus know this can b done by a Java program with a linking to acces
by ODBC....

pls...tis is urgent, any1 can help??

thankz.....(^_2

 

[Jim Rech]

There are numerous articles on running database queries from Excel in the MS
Knowledgebase. For example:

http://support.microsoft.com/default.aspx?scid=kb;en-us;321686&Product=xlw

See links to other articles at the bottom of this one.

--
Jim Rech
Excel MVP

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

 

[CLR]

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]

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

 

[Ken Wright]

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.

 

[yaya1899]

Nope, not in java n Access, jus VBA and Excel onli...i jus nid a ver
very simple example...jus like the connection string, dbdriver.....
the sql....pls ...

tq very very much~~

 

[CLR]

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(Array( _
"ODBC;DSN=fs1 - MSS-Fourth
Shift;ServerName=FS1.1583;ServerDSN=fs1 - MSS-Fourth
Shift;UID=clr;PWD=chuck;ArrayFetchOn=1;ArrayBufferSize=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]

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

 

[Ken Wright]

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.

 

[CLR]

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

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]

Hm....actually i would like 2 write a VBA which can query multiple exce
flies ( one at a time )...n linking by ODBC....hm...any idea bou
tat?....thankz again CLR....(^_9

 

[CLR]

BOY!.........talk about a moving target <G>..................

We just did this at work........It's NOT simple to work out all the details,
and I can't supply a step-by-step about how to do it.........but your files
that are to be interrogated must be structured to this end, ie: data laid
out in similar manner, or range named, because you're going after specific
cells to return to your new database.........it's best if they are all in
the same directory........for example, our files were all WorkOrder forms,
and we wanted to extract specific cells to return to our database,
........what we didn't know, was that some had ChangeOrders inserted at the
top and this changed the cell references from what we wanted.........so
additional Queries had to be made to retrieve data from those
files.........."if it ain't one thing, it's another"...........and then
some of the WorkOrders were on older forms and were not compatible at all
with our Query..........


Here's some code I recorded just to show you the basic structure of what I'm
talking about........There are more elegant solutions of course, but this
gets the basic idea..........you would of course change the filenames and
sheetnames and ranges to suit..........

Sub Getfiles()

Workbooks.Open Filename:="C:\Filename1.xls"
Sheets("Sheet1").Select
Range("A1:A18").Select
Selection.Copy
Windows("Book2").Activate
Range("A1").Select
ActiveSheet.Paste
Windows("Filename1").Activate
ActiveWindow.Close

Workbooks.Open Filename:="C:\Filename2.xls"
Sheets("Sheet1").Select
Range("A1:A18").Select
Selection.Copy
Windows("Book2").Activate
Range("B1").Select
ActiveSheet.Paste
Windows("Filename2").Activate
ActiveWindow.Close

Workbooks.Open Filename:="C:\_Filename3.xls"
Sheets("Sheet1").Select
Range("A1:A18").Select
Selection.Copy
Windows("Book2").Activate
Range("C1").Select
ActiveSheet.Paste
Windows("_Filename3.xls").Activate
ActiveWindow.Close

End Sub


hth
Vaya con Dios,
Chuck, CABGx3

 

[yaya1899]

1st, i would like 2 thank u, CLR.....
i hav tried out the code given, ....i hav changed the file name n
range, but a <<runtime error "1004" , method "open" of obj "workbooks"
failed >> msg appeared during the execution...
n i found tat the excel ODBC driver will not support DELETE, UPDATE, or
ALTER TABLE statements from http://tinyurl.com/2umvm
so..wat can i do now??

 

[CLR]

I dunno...........the code worked for me.........I didn't create it, I just
recorded it..........so, maybe if you just did ........Tools > Macro >
Record macro .......and then just went through the steps of opening a couple
workbooks and copying a range of cells and pasting it back to your main
workbook and then closing the other one and repeating it a few times with
other workbooks before you finally do....... Tools > Macro > Stop
Recording..........thats all I did, so maybe the same process will work for
you in your environment with your filenames and ranges...........hope so
anyway.........only other thing might be to post your modified code and we
could take a look at it and maybe find a typeo or something...........

hth
Vaya con Dios,
Chuck, CABGx3