MS Query (xl2000)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote