Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MS Query (xl2000)

    Can someone please point me at a web-based tutorial (at the 'idiots' guide' level) to this bit of Office? I have a load of xl workbooks all drawing different bits of data from one jumbo list and I think that the 'Get External Data' tool in xl might be a good way of saving me work, ie an edit to the jumbo list will be (or can be made to be) reflected in all the workbooks using this data.
    The ones I've found seem to refer only to Access.
    Thanks, everyone.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: MS Query (xl2000)

    I found several that seemed pretty good just googling on:
    "get external data" excel tutorial "web query"

    Here is a good example.

    You might browse thru those and try other search variants.
    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (xl2000)

    Thanks, Steve, I explained myself badly.
    What I'm looking for is a tutorial on the internet about how best to exploit the 'get external data' trick to refresh excel, where the 'external data' is merely in one or more other files on the same computer (or at worst, elsewhere on the LAN.)
    Real 'idiots' guide' level - walking before running, etc!
    This one
    http://www.its.niu.edu/its/helpdesk/docume..._in_excel.shtml
    states the possibilities without appropriate alerts, guidance concerning the options, etc.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: MS Query (xl2000)

    If you explain what you are trying to do it might be easier to help. If you are looking for general info, I would still point to a google search. Who better than you to find what is appropriate than yourself.

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (xl2000)

    I think that he has data in multiple Excel workbooks. He wants to use MSQuery (instead of links) to get data from those workbooks into another workbook.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (xl2000)

    That's correct, Legare - thanks for mindreading me!
    I'm uneager to use links as some of the workbooks 'refreshed' with data from the 'external' source need to be sent to other people and I'd rather send just one updated copy of a single file than all the linked files it's getting gen from.
    Is this the sort of case where the get external data route makes sense?
    BTW, this message probably says it's from itsimagain. I'm the same grateful lounger as jrose. So doubly grateful for all the skill and patience available here.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (xl2000)

    I understand your question, but unfortunately I can't find anything relevant with Google and I don't know enough about MSQuery to help directly. I hope someone else will jump in.
    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (xl2000)

    I think Get External Data will only work if your source data is organized as a database (i.e. records and fields).

    When you link to other spreadsheets, you don't need to send all of the source spreadsheets when you want to distribute your own work. You do have to instruct people to not attempt to update links when they open your spreadsheet. All of the linked values should stay as they were when you saved your file.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: MS Query (xl2000)

    One option that might save you some time:

    Create a "Link" sheet. In this sheet create all the formulas that are linked to another source.

    In your other formulas instead of pointing to the other workbooks, point to the relevant location in the "link sheet".

    Now all you need do is update the links, then copy - paste special - values the link sheet onto itself, save as a new name and send this updated file to the other users.

    You haven't had to affect any other formulas in your spreadsheet, just the ones in the link sheet, and the links have been "removed" by the paste special - values.

    Steve

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

    Re: MS Query (xl2000)

    You are correct in assuming the 'Get External Data' tool is the way to go, and is quite easy easy to use once you have your source data organised, as you can use the wizard to create the import query.

    As Chipshot suggested above, your data needs to be organised as a

  11. #11
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (xl2000)

    Many thanks to all four of you for some very useful leads. Sorry about the delayed thanks.
    Steve's trick to 'freeze' links is imaginative and simple - great trick, Steve!
    Andrew's patient exegesis on MS Query was exactly what I needed to 'break the ice'.
    With help from Legare and Chipshot too, I now have more than one way of frying this particular set of eggs, and I'm very grateful.

Posting Permissions

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