Dragging in external data

I'm looking to make use of the external data queries in a series of spreadsheets. I am in control of the data that the books will drag in which means I can format it in the simplest way possible rather than relying on someone else. I will have a number of variables (say 300) and these will be updated monthly, keeping the historic data. What would you all sugest as the simplest way to do this (the data must be held on a web server) to achieve what I am hoping to do? It would also be bosus if it could be read fairly easily into a web page as well.

Thanks as always.

you can use ADO to connect directly to the db and pull in the data via CopyFromRecordset.
or
use data tab, Get External data, From text, (to import CSV)
or
parse xml code to get web data

Thanks for the reply. What sort of database would you suggest for this particular job? I think that is likely the best way for me to go. WOuld it be possible for it to basically be Excel and use get external data from that (rather than direct linking to a spreadsheet which I do not think would work in this case)

Based on the information you've given us it's impossible to give you any sort of recommendation. If you want advice, you need to tell us more about your project for example, what are these "variables"? How will they be updated? Do you need to keep their historic values as they change? Why not just put them in the workbook you want to use them in?

At the moment I keep the static key details in a VBA module. And then this is copied to each of the (currently) 50 spreadsheets that use the same system. It is the simplest way rather than updating each spreadsheet with a list of the variables (upwards of 100 for each year).
These details are held in arrays for each year. So there are a set of figures for 2017, 2018 etc. Each year (at least) 2 things happen. Firstly I add an extra year (or update the last year depending) and then likely add some new variables. Just now a custom function finds the relevant number based on a text code - so it grabs 10000 when I ask the function for the code "DA" for 2017.

The massive headache is actually updating all of these spraadsheets each time - or ratehr uploading them all again to our online server. If I could find a way to link from wherever the file is downloaded straight to external data that uses all of the current information held centrally that woudl be great. If it was all kept on our own internal servers it would clearly be easy without any need for the use of get external data. But the fact is that the variables would be better held centrally to query.

Does that shed any light at all on what I am working towards?

A bit. I'd need to see some examples of these arrays, but I suspect you should be using a database to store this, not excel. You could then query the database to get the info you need.

As to the database, access is probably the easiest if you have it installed.

The "classic" way to do it would be a simple table in an excel sheet. Years across the top, variable names down the side, data in the middle. I used a series of arrays in VBA for ease of updating tbh, but it's still a PITA!
So an array would look like:

Please Login or Register  to view this content.

The coe then works by picking the right one in the chain.
It's pretty unreadable and a bit of a nightmare to update. SO I thought about maybe using a simple query to say a postgresql database to pick up the relevant tables. I only need to do it as the workbooks linking in could be downloaded anywhere but the central repository of variables will be held on a server somewhere.

Was wondering what the easiet way to do this would be - seeing as there are a fair number of options from what I can work out.

So I started with postrgresql then had an issue so tried out MySQL instead. SAme problem. It seems Excel needs some sort of connector - problem is that the only windows download available seems to be a 32 bit one. ANy ideas?

You want either SQL server or Access. Stick with MS technologies, they do not require external connectors.