Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TugboatEng on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!
Microsoft Query in Excel
- Thread starter fmc3604
- Start date
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
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
- Thread starter
- #3
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
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
- Status