Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Kansas, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

    I

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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=<font color=red>" & strNumb & "</font color=red>)"

    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

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    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:
    <pre>& "WHERE (VN10.VEND_NUMB=<font color=red>'</font color=red>" & strNumb & "<font color=red>'</font color=red>)"</pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    Rory, thanks for spotting that.

    Did not read the original closely enough.

    Andrew

  5. #5
    Lounger
    Join Date
    Feb 2003
    Location
    Kansas, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  7. #7
    Lounger
    Join Date
    Feb 2003
    Location
    Kansas, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •