×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-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

*Eng-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 Query in Excel

Microsoft Query in Excel

Microsoft Query in Excel

(OP)
I run a lot of Microsoft Query's from within Excel to get data out of our AS-400 using an ODBC connection. Is there a way take the primary search data from an Excel cell and use it in the Micrsoft Query and return to Excel with the new results.

RE: Microsoft Query in Excel

This might get a quicker response on Tek-Tips, this site's sister. It's more IS oriented.

Let me make sure I understand the question -- you want to make a parameter query based on parameters entered into a cell or cells in the spreadsheet, and then return the results to another page on the spreadsheet?

Is it important to use MSQuery instead of reading the resultset right into a spreadsheet?

Ron

RE: Microsoft Query in Excel

(OP)
The answer to your first question is yes. That is aexactly what I want to do. Regarding your second question - the multiple files (databases) used in the AS-400 are to large for Excel

RE: Microsoft Query in Excel

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 db happens to be in SQL Server dialect of SQL, where '%' is the wild-card character. Yours will need to be in AS400 dialect, so it may be different in that spot.

I hope that helps. It worked for me, and I learned something, but it's not perfectly easy or anything.

Ron

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources