How to Save an Access Query in Excel With VBA

104 11
    • 1). Open Microsoft Office Access 2007, select "Database Tools" then click "Visual Basic." Select the "Insert" menu and click "Module."

    • 2). Copy and paste the following into your new module.

      Private Sub ExportAccessDataToExcel()

      Dim SqlString As String

      SqlString = "CREATE TABLE testMeasurements (TestName TEXT, Status TEXT)"

      DoCmd.SetWarnings False

      DoCmd.RunSQL (SqlString)

      SqlString = "INSERT INTO testMeasurements VALUES('Average Power','PASS')"

      DoCmd.RunSQL (SqlString)

      SqlString = "INSERT INTO testMeasurements VALUES('Power Vs Time','FAIL')"

      DoCmd.RunSQL (SqlString)

      SqlString = "SELECT testMeasurements.TestName, testMeasurements.Status INTO exportToExcel "

      SqlString = SqlString & "FROM testMeasurements "

      SqlString = SqlString & "WHERE (((testMeasurements.TestName)='Average Power'));"

      DoCmd.RunSQL (SqlString)

      DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, _

      "exportToExcel", "G:\TestMeasurements.xls", True, "A1:G12"

      End Sub

    • 3). Run the subroutine by pressing "F5."

Subscribe to our newsletter
Sign up here to get the latest news, updates and special offers delivered directly to your inbox.
You can unsubscribe at any time

Leave A Reply

Your email address will not be published.