Solved: Copy output of Access Select Query, Paste to Excel

[pliskers]

I'd like some code to run a select query in Access, copy the output, shift focus to Excel, and paste it to a workbook, then go back to Access and close the query.

I am familiar with code to export a table from Access to Excel, but am trying to avoid all of that, which seems unnecessary if I can just paste from the select query.

Thanks in advance!

[CreganTur]

Welcome to the forum- it's always good to see new members.

You don't need to go through all of those steps to export the contents of a query into Excel- just use the TransferSpreadsheet method. Look it up in Access Help to get a detailed explination. For the parameter where you name a table, you can name a query instead.

HTH:thumb

[pliskers]

Thanks for the reply and welcome! I was hoping to just copy to the Clipboard and paste to Excel. Is there a way to avoid the actual exporting as an Excel file?

[CreganTur]

Here's an example that uses a Data Access Object (DAO) connection to write data to Excel:

Sub ExportToExcelDAO()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim xlApp As Object
Dim wkb As Object
Dim rng As Object
Dim strExcelFile As String
Dim strTable As String
Dim iCol As Integer
Dim objSheet As Object

strTable = "Employees"
strExcelFile = "C:\Acc07_ByExample\ExcelFromAccess.xls"
'if excel file already exists delete it
If Dir(strExcelFile) <> "" Then Kill strExcelFile
Set db = CurrentProject.Connection
Set rst = db.OpenRecordset(strTable)

'set reference to Excel to make Excel visible
Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Visible = True
'set references to workbook and worksheet
Set wkb = xlApp.Workbooks.Add
Set objSheet = xlApp.ActiveWorkbook.Sheets(1)
objSheet.Activate

'write column names to the first worksheet row
For iCol = 0 To rst.Fields.count - 1
objSheet.Cells(1, iCol + 1).Value = rst.Fields(iCol).Name
Next
'specify cell range to recieve data
Set rng = objSheet.Cells(2, 1)

'copy specified number of records to worksheet
rng.CopyFromRecordset rst, rst.RecordCount
'autofit columns to make data fit
objSheet.Columns.AutoFit

'close the workbook
wkb.SaveAs FileName:=strExcelFile
wkb.Close

'quit excel and release object variables
Set objSheet = Nothing
Set wkb = Nothing
xlApp.Quit
Set xlApp = Nothing
db.Close
Set db = Nothing
End Sub

[pliskers]

Thanks, Randy - actually what worked out the best for me was exporting to Excel directly from the Access select queries (didn't know that was possible till you pointed it out), and linking those exported files to another Excel file. Thanks for the tip!