none
Using Excel VBA to run Sql statements RRS feed

  • Question

  • Greetings, first time poster here. I would like to execute queries against other excel files from within an excel macro. I am using Microsoft office home and student 2013. What do I need to do this? Where do I go to download the necessary files? Thanks.
    Monday, May 22, 2017 2:46 PM

All replies

  • Hi Sleggio,
     
    If these are the same queries over and over, I would just create connections
    manually which can be refreshed on workbook open using the built-in stuff.
     
    Alternatively if you do want to go VBA, set a reference to the Microsoft
    Activex Data Objects 2.x Library from within the VBA editor (Tools,
    References).
     
    Then search Google for getting data from Excel workbooks using ADO
     
    You can find the correct connection strings here:
     
     
    e.g.:
     
    cn.Open "driver={Microsoft Excel Driver (*.xls)};" & _
        "driverid=790;dbq=c:\foldername\workbookname.xls;" & _
        "defaultdir=c:\foldername"
       
    See an example here:
     
     
     
     
    Monday, May 22, 2017 3:10 PM
  • Hi,

    Please try Jan's suggestion and hope it is helpful to you.

    Additionally, this forum focuses on general discussion for Excel application. I notice you are looking for some Macro codes for your requirement. For further discussion, I would move the thread to Excel for developers forum for more help:
    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    Thanks for your understanding and hope you can have a happy discussion there :)


    Regards,

    Winnie Liang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, May 23, 2017 2:17 AM
  • Thank you!
    Tuesday, June 6, 2017 8:30 PM
  • Hello,

    If  Jan's suggestion works for you and your issue has been resolved, I suggest you mark helpful post as answer to close this thread.

    If you issue persist, please feel free to let me know and share here what code you are using now and what error you get.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, June 7, 2017 2:47 AM
    Moderator