+ Reply to Thread
Results 1 to 5 of 5

possible to get and copy data from database to excel?

  1. #1
    Registered User
    Join Date
    08-24-2006
    Posts
    61

    possible to get and copy data from database to excel?

    I'm using dbeaver to connect to multiple database, postgresql or MsSQL.
    often, I need to query certain table to get the data, e.g. table 1 data from year 2016-2018. then copy the data into excel to further analysis. e.g. excel formula to sum all data.
    is that posubble to access those in excel directly?
    1. input DB credentials in excel
    2. click connect
    3. input table name or directly input sql commands
    4. click get data
    5. then data will be copied in next excel worksheet certain column. e.g. worksheet2 column C, column D.
    sorry that I'm not familiar with vba,macro. hope can get some idea which method to use to achieve this. then I can start research the relevant codes and apply it.
    thanks for the help.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,140

    Re: possible to get and copy data from database to excel?

    You should be able to do this with a thing called MS-Query. However you will need to set up an ODBC connection for which you will need the drivers. Chances are if you have the database, you already have the drivers installed. Otherwise, go out to the web and get them.

    The trick to using MS-Query is to develop the SQL in some other tool like WinSQL or TOAD. Then turn off the Wizard for MS-Query. When you launch MS-Query and make the connection, close out the windows that show you the tables. MS-Query is a terrible tool to develop anything other than the simplest of queries. Instead click on the SQL icon and paste the SQL code in there.

    MS-Query will remember your credentials if you tell it to. However, they are stored in plain text in the connection string. If this is OK for your environment, so be it. There is a VBA workaround using hidden code and / or hidden sheets that can supply the credentials, run the query and then remove them if you are in a more "hostile" environment, but, well, it's Excel security. Otherwise you will be prompted to enter your credentials when you run the query.

    Generally speaking you won't need any VBA with MS-Query. If the query can be displayed graphically, then you can even pass parameters to it at run time. If it cannot be displayed graphically or it's dynamic (like a model list that changes frequently), then there is some very simple VBA you can use to rewrite the SQL before running it. Basically you enter the SQL in an Excel table, make substitutions in the table, and the VBA takes it from there.

    If you want to give this a try, give me a shout. Here is some more information on MS-Query: http://www.utteraccess.com/wiki/MS_Query.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    08-24-2006
    Posts
    61

    Re: possible to get and copy data from database to excel?

    thanks alot. never know msquery can be done in such way with excel.
    will try it out.

  4. #4
    Registered User
    Join Date
    06-27-2017
    Location
    Collegeville, PA
    MS-Off Ver
    Office 365
    Posts
    5

    Re: possible to get and copy data from database to excel?

    I don't see which version of Excel you have, but you might consider connecting to MS-SQL using Power Query. Connecting Power Query to Postgre not as easy.

  5. #5
    Registered User
    Join Date
    08-24-2006
    Posts
    61

    Re: possible to get and copy data from database to excel?

    I'm using latest excel. should be 2017?

+ 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. Replies: 1
    Last Post: 02-09-2017, 05:36 AM
  2. Replies: 1
    Last Post: 02-09-2017, 05:30 AM
  3. Need macros to extract data from database/compare/update database - Excel 2007
    By saroby in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-15-2014, 01:45 PM
  4. Excel Macro to copy Excel Invoice data to Excel Database - Works Perfect. But
    By asifmars in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-02-2014, 04:01 AM
  5. copy data from text file or from SQL database into cells in excel
    By veD_DeXTer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-30-2012, 03:02 AM
  6. copy excel data into an access database
    By cutsygurl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-04-2007, 12:30 PM
  7. Replies: 5
    Last Post: 06-11-2006, 04:45 PM

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