+ Reply to Thread
Results 1 to 9 of 9

Dragging in external data

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-11-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2016 (among others!)
    Posts
    331

    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.

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,189

    Re: Dragging in external data

    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

  3. #3
    Forum Contributor
    Join Date
    03-11-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2016 (among others!)
    Posts
    331

    Re: Dragging in external 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)

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: Dragging in external data

    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?

  5. #5
    Forum Contributor
    Join Date
    03-11-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2016 (among others!)
    Posts
    331

    Re: Dragging in external data

    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?

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: Dragging in external data

    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.

  7. #7
    Forum Contributor
    Join Date
    03-11-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2016 (among others!)
    Posts
    331

    Re: Dragging in external data

    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:

    rates(2012) = Array("2012/2013", 8105, 100000, 0.5, 2710, 0.1, 31660, 0.2, 115630, 0.4, 0.5, 0.1, 0.1, 0.325, 0.425, 0, 0, 0, 0, 0, 0, 0)
    rates(2013) = Array("2013/2014", 9440, 100000, 0.5, 2790, 0.1, 29220, 0.2, 117990, 0.4, 0.45, 0.1, 0.1, 0.325, 0.375, 0, 0, 0, 0, 0, 0, 0)
    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.

  8. #8
    Forum Contributor
    Join Date
    03-11-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2016 (among others!)
    Posts
    331

    Re: Dragging in external data

    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?

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: Dragging in external data

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. dragging sheets like dragging cells.
    By cyclop2509 in forum Excel General
    Replies: 2
    Last Post: 03-02-2017, 05:33 AM
  2. Data not dragging down properly
    By eeps24 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-03-2016, 02:46 PM
  3. External Data from Microsoft Query - Excel File - Causes external file to open.
    By lee1000d in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-25-2015, 02:25 PM
  4. Skipping Every 5th Row and Dragging Down To Get New Data
    By seannydj in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-31-2012, 10:00 PM
  5. data dragging
    By jaydeep in forum Excel General
    Replies: 1
    Last Post: 01-20-2012, 02:03 PM
  6. Dragging data from one spreadsheet to another
    By lukela85 in forum Excel General
    Replies: 1
    Last Post: 07-03-2010, 06:32 PM
  7. “Import External Data:Import External Data
    By Jeff in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-03-2005, 06:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1