Thursday, June 4, 2009

Back to basics -

The procedure for using SQL queries in Excel.
  • Open Enterprise Manager
  • right click on m2mviews, click New View
  • create the query
  • save it
  • Open Excel
  • Click Data, Import External Data, New Database query
    1. name it, something like m2mviews
    2. SQL Server
    3. click Connect, Click Options, choose m2mviews
    4. Click OK [ you only have to do this once... the data source will be there the next time. You can repeat this step for your data company if you want to use Excel to query the live data. ]
  • Back to your spreadsheet.
  • Click Data, Import External Data, choose the datasource you just created.
  • Choose the view you've saved.
    the query wizard will appear where you can choose fields and sort and filter, etc.

It's pretty self explanatory from there. you can right click in the data to refresh from the query, or edit the query or change the data properties to auto refresh upon opening the spreadsheet.

Posted by at

3 comments:

  1. UnknownJuly 16, 2009 at 12:17 PM

    I have an excel document that we give to our customers that lists bill to, ship to, sales order number, po number etc.. Right now they just manually add this info to the excel doc and then send it to the customer. I am having trouble modifying the excel document to pull in the data based on the sales order number. Do you have any advice? I was thinking about doing this with visual basic editor inside excel and connecting to the sql database, and then creating a box to select the SO #.

    Thanks for your help.

    ReplyDelete
  2. CatherineJuly 20, 2009 at 8:01 AM

    I would enter the following SQL query into enterprise manager using the above procedure. This will pull the info you've listed above. Depending on what your etc is, should get you started.

    SELECT somast.fsono, somast.fcustno, somast.fcustpono, somast.fsoldaddr, syaddr1.fccompany AS soldto, somast.fshptoaddr,
    syaddr1.fccompany AS shipto
    FROM m2mdata10.dbo.somast somast INNER JOIN
    m2mdata10.dbo.syaddr syaddr1 ON syaddr1.fcalias = 'SLCDPM' AND syaddr1.fcaddrtype = 'O' AND somast.fcustno = syaddr1.fcaliaskey AND
    somast.fsoldaddr = syaddr1.fcaddrkey INNER JOIN
    m2mdata10.dbo.syaddr syaddr2 ON syaddr2.fcalias = 'SLCDPM' AND syaddr2.fcaddrtype = 'S' AND somast.fcustno = syaddr2.fcaliaskey AND
    somast.fshptoaddr = syaddr2.fcaddrkey

    From there you should be able to connect to your SQL database using the DATA IMPORT EXTERNAL DATA option in excel. I'm not sure what your trouble is in importing external data, is it grayed out? in that case you'll need to install the query module which, worst case, will be on the cds that came with office. Post again if you get stuck.

    ReplyDelete
  3. UnknownJuly 22, 2009 at 5:45 AM

    Hi Catherine, thank you for your reply.

    I will give this a shot today. I am just getting started with ADO and excel visual basic editor. We have a similar excel document that M2M created for us that when opened a combo box appears that allows us to select a date range, and the vendor. Then it will show us late PO's for the vendor, etc..

    I am trying to do the same thing with this excel document that when opened will bring up a box asking us to enter the sales order number, and then when we click on load the data for that sales order will populate the bill to, ship to, po number, fields.

    Thank you for your help!

    ReplyDelete

Subscribe to: Post Comments (Atom)