Microsoft Query in Excel

[fmc3604]

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.

 

[paron]

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

 

[fmc3604]

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

 

[paron]

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

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&quot.Range("a1&quot & "%')" _
& Chr(13) & "" & Chr(10) _
& "ORDER BY tPersons.LastName&quot


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