locked
Multiple DSN in Excel Macro. RRS feed

  • Question

  • Hello All,

    I've a excel VBA add-in that fetches the data from SQL server based on user input. When the user enters customercode all the customer data is populated in the spreadsheet. I've setup the ODBC connection for this macro in ODBCad32.exe.

    Now I need to modify my macro to pull data from multiple database using complex join query. Is it possible to get data from multiple database/DSN in Excel Macro? Thanks.

    Regards

    Friday, October 17, 2014 6:05 PM

Answers

  • Ditto, create a View in SQL Server to get the data you want. I always use ADO with Excel VBA and import all fields for teh View. That way if you want too add or remove fields its automatic when you edit teh View in SQL Server.

    Note you need to sort the data in Excel after importing as teh sort in a View doesn't come thru to Excel.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    • Proposed as answer by danishani Tuesday, October 21, 2014 7:09 PM
    • Marked as answer by danishani Monday, November 10, 2014 11:20 PM
    Tuesday, October 21, 2014 5:44 AM
  • I am not sure if Excel is the place to accomplish this. 

    It would be easier to do handle all the different connections and linking on the Database End.

    Then create a pass through query in Excel using ADO.

    At least that's how I would solve this. My 2cts ...


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    • Proposed as answer by danishani Tuesday, October 21, 2014 7:09 PM
    • Marked as answer by danishani Monday, November 10, 2014 11:20 PM
    Monday, October 20, 2014 6:57 PM

All replies

  • Can somebody please help? Thanks
    Monday, October 20, 2014 5:56 PM
  • I am not sure if Excel is the place to accomplish this. 

    It would be easier to do handle all the different connections and linking on the Database End.

    Then create a pass through query in Excel using ADO.

    At least that's how I would solve this. My 2cts ...


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    • Proposed as answer by danishani Tuesday, October 21, 2014 7:09 PM
    • Marked as answer by danishani Monday, November 10, 2014 11:20 PM
    Monday, October 20, 2014 6:57 PM
  • Ditto, create a View in SQL Server to get the data you want. I always use ADO with Excel VBA and import all fields for teh View. That way if you want too add or remove fields its automatic when you edit teh View in SQL Server.

    Note you need to sort the data in Excel after importing as teh sort in a View doesn't come thru to Excel.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    • Proposed as answer by danishani Tuesday, October 21, 2014 7:09 PM
    • Marked as answer by danishani Monday, November 10, 2014 11:20 PM
    Tuesday, October 21, 2014 5:44 AM