How to Pass the Parameters From Excel 2007 to an MS Query

Knowing how to use values in a Microsoft Office Excel workbook as parameters to query a Microsoft Office Access table can make your database application more dynamic. Excel is a powerful spreadsheet application commonly used to store and analyze large amounts of data. Access is a relational database management system used for small and local databases. Both of these programs are included in the Microsoft Office suite. Visual Basic for Applications (VBA) is a computer programming language used to automate routine tasks in MS Office.

• 1

  Open Microsoft Office Excel, type "1" in "A1", "2" in "B1", and "3" in "C1." Save the workbook as "mydata" in "C:\".

• 2

  Start Microsoft Office Access, select "Band Database," then click "Create." Select the "Database Tools" menu and click "Visual Basic." Click the "Insert" menu and select "Module."

• 3

  Click the "Tools" menu, select "References," and check the box next to "Microsoft Excel <version number> Object Library." Click "OK."

• 4

  Copy and paste the following VBA code to open the "mydata" workbook. Use the value in "B2" as the parameter to query data from the "dbTable" table and display the description.

  Private Sub useExcelParamenters()

  Dim strSQL As String

  Dim dbs As Database

  Dim rst As Recordset

  Dim XLPar As Integer

  Dim XLApp As Excel.Application

  Dim XLBook As Excel.Workbook

  Dim XLSheet As Excel.Worksheet

  Set dbs = CurrentDb

  Set XLBook = Workbooks.Add(Template:="C:\myData.xlsx")

  Set XLApp = XLBook.Parent

  Set XLSheet = XLBook.Worksheets("Sheet1")

  With XLSheet

  .Range("B1").Select

  XLPar = Selection.Value

  End With

  XLBook.Close

  strSQL = "CREATE TABLE dbTable (Product NUMBER, Description TEXT);"

  DoCmd.RunSQL (strSQL)

  strSQL = "INSERT INTO dbTable (Product, Description) "

  strSQL = strSQL & "VALUES (1, 'Toys');"

  DoCmd.SetWarnings False

  DoCmd.RunSQL (strSQL)

  strSQL = "INSERT INTO dbTable (Product, Description) "

  strSQL = strSQL & "VALUES (2, 'Computers');"

  DoCmd.SetWarnings False

  DoCmd.RunSQL (strSQL)

  SQLstr = "SELECT dbTable.Product, dbTable.Description "

  SQLstr = SQLstr & "FROM dbTable "

  SQLstr = SQLstr & "WHERE (((dbTable.Product) = " & (XLPar) & "));"

  Set rst = dbs.OpenRecordset(SQLstr)

  rst.MoveLast

  rst.MoveFirst

  Debug.Print "The description for product in B1 is " & rst.Fields(1).Value

  rst.Close

  dbs.Close

  End Sub

• 5

  Press "F5" to run your subroutine.