VBA for Export to excel

[sladetroityer]

Yes the form is being filtered. The form is always filtered unless we are looking at ALL employees.

 

[vbaInet]

Private Sub cmdExportExcel_Click()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qselEmployeeInformation")
qdf.SQL = "SELECT * FROM TableName WHERE " & Me.Filter

What is this??

 

[sladetroityer]

This is the code you sent me? Was I supposed to put the name of the employee table at TableName?

 

[sladetroityer]

This code destroys my qselEmployeeInformation query when I run it......It removes all of the tables and relationships.

 

[vbaInet]

This is the code you sent me? Was I supposed to put the name of the employee table at TableName?

I thought that was obvious.

 

[sladetroityer]

FROM TableName WHERE

Would this be "FROM tblEmployee Where" ?

 

[sladetroityer]

VbaInet

this is the code that I was looking for... Take notes for the next person that asks!!!!

Private Sub cmdExportExcel_Click()
CurrentDb.QueryDefs("qExport").SQL = "select * from qselEmployeeInformation where " & Me.Filter
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qExport", "c:\t.xls", True
End Sub

 

[spikepl]

Your code will die if there is nothing in the filter

 

[sladetroityer]

There's always something in the filter! But thanks for the advice

 

[Rank Am]

If your using DAO or ADO to get the data, I find using the CopyFromRecordset method to be the simplest and fastest way to get data to a spreadsheet. I haven't used DoCmd for years to do this. This method allows you to specify exactly where the data goes, handle all formatting etc from access - so it is worth learning.

In VBA add a reference to MS Excel.

' Some useful Excel Object variables for automation from access
Dim objXL As Excel.Application
Dim objWKBook As Excel.Workbook
Dim objWKSheet As Excel.Worksheet
Dim objXLRange As Excel.Range

'There are various ways to open an instance of excel but if you have a template or workbook the easiest is to use

Set objWKBook = GetObject(Application.CurrentProject.Path & "\whatever.XLS")

'set a pointer to Excel's Application object
    Set objXL = objWKBook.Parent

'You can then choose an existing worksheet to send the data too (or even create a new one)

Set objWKSheet = objWKBook.worksheets("Name of existing wk sheet")

' Make sure all is visible
objXL.Visible = True
    objWKBook.Windows(1).Visible = True

You then need to add some code to create a recordset either DAO or ADO (I generally use ADO)

Finally you need to point an excel range or cells reference at the recordset and copy the data over (assume the recordset is called RS)

this is acheived by:

objWKSheet.Cells(x, y).CopyFromRecordset RS

'Get rid of the recordset

You can then use the full XL Object library directly from the access procedure to format the data / update charts etc.

DoCmd is very limited