locked
SQL statements vs Excel RRS feed

  • Question

  • Hi all,

    Just a question.

    Is it possible to use SQL statements in MS Access VBA to retrieve data in an MS Excel workbook?

    I have a databse that uses automation to create an Excel workbook with 12 sheets, one for each month of the year. The columns are created as follows:

    Column A = date of the month (01-01-2018, 02-01-2018 and so on.

    Column B = the WeekDayName of each date, next to the date

    I now retrieve data from a table which contains the dates. I want to select the row where the same date appears and add some data in columns C to O. I thought it might be possible to use a "SELECT" query to retrieve the position in the Excel sheet where the dates correspond.

    If this is possible, I would appreciate some clarity how to achieve this. If not, what would be the easiest way of accomp-lishing this?

    Thanks

    Deon

    Thursday, February 1, 2018 2:20 PM

Answers

All replies

  • Absolutely. You can add the Excel Workbook/Worksheet as a linked table to your database and query it as you would an Access table, or you can use DAO or ADO:

    How To Use ADO with Excel Data from Visual Basic or VBA


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Deon SA Friday, February 2, 2018 5:50 AM
    Thursday, February 1, 2018 3:46 PM
  • Great!! - Thanks Paul.
    • Marked as answer by Deon SA Friday, February 2, 2018 5:50 AM
    • Unmarked as answer by Deon SA Friday, February 2, 2018 5:50 AM
    Friday, February 2, 2018 5:50 AM