CODEWITHSUNDEEP
excelvbams-access
Akshay Chari
Akshay Chari

Reputation: 43

Need only 2 columns to be pulled/export from query in access data base to excel

I have a macro that exports the query from Access Database to excel. However, I need the macro to export only the 2 columns (first 2 columns) to excel, which I am not able to figure out please help. below are the database screenshot and code.

I need the first 2 columns i.e. "County" and "Lisa Madigan" to be exported in an excel file

Code : -

Option Compare Database
Option Explicit

Public Sub ExportToExcel()

Dim xl As Excel.Application
Dim wbtarget As Workbook

Dim qdAGC As QueryDef
Dim rsAGC As Recordset

'Set up reference to the query to export

Set qdAGC = CurrentDb.QueryDefs("qdAGC")

'Debug.Print qdAGC.SQL

'Set up the parameter
'Execute the query

Set rsAGC = qdAGC.OpenRecordset()

'programetically reference exel
Set xl = CreateObject("Excel.Application")
'set ref to the export workbook

Set wbtarget = xl.Workbooks.Open("Path")

'Clear excel sheet
wbtarget.Worksheets("Sheet1").Cells.ClearContents

'use paste from recordset to put in excel sheet
wbtarget.Worksheets("Sheet1").Cells(1, 1).CopyFromRecordset rsAGC

'save workbook
wbtarget.Save

wbtarget.Close

Set wbtarget = Nothing
Set xl = Nothing

Set qdAGC = Nothing



End Sub

Upvotes: 0

Views: 199

Answers (1)

Doug Coats
Doug Coats

Reputation: 7107

this should be super easy

Open this query: qdAGC

Jot down the names of the first two column from the query.

Then change your code to this (obviously change out the names):

     Set qdAGC = CurrentDb.Openrecordset("SELECT ColumnOne, ColumnTwo FROM qdAGC")

There is another way to do the same thing. Check out:

     DoCmd.TransferSpreadsheet

Upvotes: 2

Related Questions