×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Microsoft: Office FAQ

Best of Excel

A query that takes parameters from a cell in the spreadsheet by paron
Posted: 30 Dec 02

OK, the simplest way I was able to find:

Record a macro of running a query on your database. "Tools";"Macro";"Record New Macro"

Not really important what query you run, it's just to get the wizard to do the heavy lifting of writing the code to connect to a db, run a query, and put the results into a spreadsheet.

Now, edit the macro. The wizard's macro will probably be about 20 lines or so long. You will be looking for a line that says something like:

.CommandText = Array( _
"SELECT tPersons.FirstName, tPersons.PersonID," _
& "tPersons.LastName, tPersons.MiddleName" _
& Chr(13) & "" & Chr(10) _
& "FROM Contacts.dbo.tPersons tPersons" _
& Chr(13) & "" & Chr(10) _
& "WHERE (tPersons.LastName Like 'p%')" _
& Chr(13) & "" & Chr(10) _
& "ORDER BY tPersons.LastName")

I added the space-underscore-ampersand line extenders to make it easier to see on this forum. The robot that writes the macro in Excel hasn't heard of pretty-printing; it's one long line.

Here's how you modify it to look in a cell of another worksheet for its criterion:

.CommandText = Array( _
"SELECT tPersons.FirstName, tPersons.PersonID," _
& "tPersons.LastName, tPersons.MiddleName" _
& Chr(13) & "" & Chr(10) _
& "FROM Contacts.dbo.tPersons tPersons" _
& Chr(13) & "" & Chr(10) _
& "WHERE (tPersons.LastName Like '" _
& Sheets("Sheet2").Range("a1") & "%')" _
& Chr(13) & "" & Chr(10) _
& "ORDER BY tPersons.LastName")


See the change right after the "WHERE" ?

My query happens to be in SQL Server dialect of SQL, where '%' is the wild-card character. Yours will need to be in the proper dialect for your db, so it may be different in that spot.

Ron

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close