Import mailing addr from SQL into Excel 2000 (Excel 2000 / SQL 2000)

Topic

New Reply

Old hardware dog trying to learn new tricks, any help would be appreciated.

I

Reply | Quote

Replies

I don’t use SQL 2K, but as Excel sends the SQL as a string perhaps you could use something like (using VBA) :

strNumb = Range(“A1”).Value
strSQL = “SELECT VN10.VEND_NUMB, VN10.NAME, VN10.ADDR1,” _
& “VN10.ADDR2, VN10.CITY, VN10.STATE, VN10.ZIP” _
& “FROM CRCTEST.dbo.VN10 VN10” _
& “WHERE (VN10.VEND_NUMB=” & strNumb & “)”

This example takes the value in A1 and includes it as the VEND_NUMB in the SQL string as shoen in red.

You could use the TRANSPOSE function to convert the data returned from you query into a column of data., ,e.g. if your data is returned to cells A2:G2 (7 items) you could select seven adjacent cells in a single column and enter trh following formula :

=TRANSPOSE(A2:G2)

but to enter the formula use Ctrl-Shift Enter keys together rather than just Enter, as thsi is an array formula. If entered correctly, it should be enclosed in brace (curly) brackets.

Andrew C

Reply | Quote

Andrew,
Since the Vend_Numb appears to be a string in the original post, it will need to be enclosed within quotes in the SQL – i.e. the last line needs to be:

& "WHERE (VN10.VEND_NUMB='" & strNumb & "')"

Reply | Quote

Rory, thanks for spotting that.

Did not read the original closely enough.

Andrew

Reply | Quote

Thanks folks! Looks like I’m going to need to learn more than a few lines in MS Query.
Greg

Reply | Quote

In MS Query you are able to have user defined parameters. I’ll assume you already have a query set up that returns the address, click anywhere in that data and right click and choose Edit Query. It will come up with the Query Wizard or it will say you can’t use the wizard and take you directly to MS Query.
If you get the Wizard go through each step and remove any conditions you may have in regards to the Vendor Number. When you get to the last step, don’t press finish…instead of “Returning data to Excel”, select “View Data or edit query in Micorsoft Query” and then press finish. This take you to Microsoft Query.
In the Tool Bar turn “off” the Auto Query Button (it looks like an exclamation point with arrows on either side of it) and turn “on” the Show/Hide Criteria button (it looks like a pair of glasses with a triangle over it). In the middle of the screen you’ll see Criteria Field, click that and choose “VN10.VEND_NUM”. Below that in the value field type in “[ENTER VENDOR NUMBER]” (excluding quotation marks and must have square brackets).
When you close MS Query and each time you run this query a dialog box appears asking you to “ENTER VENDOR NUMBER”. Enter the number you want and it will pull up just the information for that vendor. It still brings it up across the columns though, you’ll have to edit that some other way.

Hope this helps, any problems don’t hesitate to let me know.
Stats

Reply | Quote

Sorry I didn’t get back sooner. We’ve decided to use a “forms” program instead of Excel which will bring up other challenges, we’re still going to try and query the database so many of the suggestions everyone has made may still help me out.
Thanks, Greg

Reply | Quote