Import SQL Query to Excel Question

[Dave E]

I am trying to import data from a SQL database into an Excel spreadsheet and
need some help. I want my query to take a value already within the
spreadsheet (a Serial Number within a particular column) and bring back
certain data associated with that serial number from the database and insert
it into the spreadsheet on the same line as the serial number. I have tried
to do this through Microsoft Query within Excel but I don't seem to have the
hang of it. First, is this possible? Second, is this the best method of
doing this? Any help is much appreciated.

Feel free to respond directly to me at david....@sap.com. Thanks!

[Dick Kusleika]

Dave

I don't think you can do that directly with MS Query. An exception would be
if your list of serial numbers was sorted, then you could sort the resulting
query and they would match up. That's probably not the case however.

I can think of two ways to do this. You could insert your MS Query
QueryTable on another sheet and use VLOOKUP functions to pull the
information over to the sheet with the serial numbers.

Another way would be to retrieve the query via ADO and for each cell that
contains a serial number, loop through the resulting recordset until it
finds a match and write the fields to the spreadsheet.

Post back if either of those methods sound good and you need help
implementing them.

--
Dick Kusleika
MVP - Excel

Post all replies to the newsgroup.

"Dave E" <david....@sap.com> wrote in message
news:atqlqb$q1j$1...@news1.wdf.sap-ag.de...

[Dave Edwards]

Dick,

Thanks for the reply and sorry for not getting back to you sooner! I
would be interested in getting more details about both methods you
described. I am unfamiliar with both as I am very new to this so sorry
for making you teach me!!

Thanks for the help Dick and I look forward to your reply!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dick Kusleika]

Dave

The first method would probably be the easiest. The only limitation is if
the QueryTable had more than 65,000 rows you wouldn't be able to import it.
Hopefully you are well under that.

Create a new sheet (e.g. Sheet2) and use Data - Get External Data to bring
the SQL Server table (or query) into Excel. You can use Microsoft Query to
limit what's brought over, but you won't need to sort it for this method.

If the resulting QueryTable had, for example, two columns, serial number and
name, then you can use a VLOOKUP formula on sheet1 to bring over the name.
It might look like this with the serial numbers in column A on sheet1

B2: =VLOOKUP(A2,Sheet2!ExternalData1,2,FALSE)

This takes the value in Sheet1!A2 and looks in the first column of the range
named ExternalData1 on Sheet2. (You may need to change that range name, or
you can hardcode it like Sheet2!A1:B300). When a match is found, it returns
the value from the second column. The FALSE argument tells it to find an
exact match and it will return #N/A if no match is found.

You should then be able to copy that formula down column B as far as you
need and it would return the name for each serial number is column A.

You can bring over more information by repeating the above formula and
changing the third argument (from 2 to 3, 4, 5...) to whichever column
contains the information you want.

--
Dick Kusleika
MVP - Excel

Post all replies to the newsgroup.

"Dave Edwards" <david....@sap.com> wrote in message
news:u9cofxmsCHA.1636@TK2MSFTNGP12...