VBA for Export to excel

[sladetroityer]

Access 2010.
I want to add a command button that exports my the data on my datasheet view Form to excel. The issue is that the data is filtered with macros. Below is VBA code that allows my reports to run off the filtered data, can I apply this same idea (me.filter) to the export to excel VBA code?

Private Sub cmdOpenHeadcountSummary_Click()
DoCmd.OpenReport "rptHeadcountSummary", acViewPreview, , Me.Filter
End Sub

Can someone show me how to write the code for export to excel utilizing the me.filter?

 

[vbaInet]

Two options:

* Change the SQL of the QueryDef in code then export the query

Or

* Refer to textboxes in a form from your query to get the filter criteria then export the query.

 

[sladetroityer]

What does the code look like for the export to excel macro?

 

[vbaInet]

DoCmd.TransferSpreadsheet

Check that out.

 

[sladetroityer]

Here is some code that I found on line? Where would I insert the me.filter so that access export only the filtered data from the query

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qselEmployeeInformation", "c:\temp.xls", True

 

[vbaInet]

Two options:

* Change the SQL of the QueryDef in code then export the query

Or

* Refer to textboxes in a form from your query to get the filter criteria then export the query.

May I refer you back to my first post. Note the highlighted parts.

 

[sladetroityer]

Sorry but I don't know what either of those statements mean. That's why I am looking for the code. The form is in datahseet format so there aren't any text boxes.

 

[vbaInet]

Search the forum for the query def option I mentioned and see what you come up with. If you're not having joy drop us a quick message on here.

 

[sladetroityer]

Well, I've searched the internet for queryDef and I see what it's doing but I cannot write the code because I don't know how. I am looking to place a me.filter in export code but I don't know where it goes or if it's even possible.

 

[sladetroityer]

Access 2010

Private Sub cmdExportExcel_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Me.Filter, "qselEmployeeInformation", "c:\EmployeeInfo.xls", True
End Sub
[code\]
 
I am using this code to export to excel and I'm getting this error. I know it's the filter but how else can I do it?

 

[vbaInet]

I keep telling you the ways to do it but you keep referring to Me.Filter. If Me.Filter would work I would have mentioned it.

 

[sladetroityer]

OK. Thanks. I'm not sure what the code would look like for Query Def.... a little help here?

I researched query def and I can't seem to find what I need.

 

[vbaInet]

It's simple enough. Some aircode for you:

dim qdf as dao.querydef

set qdf = currentdb.querydefs("[COLOR=Red]QueryName[/COLOR]")

qdf.sql = "SELECT * FROM TableName WHERE " & [COLOR=Red]Me.Filter[/COLOR]

qdf.close

set qdf = nothing

[COLOR=Red]... export the query here ...[/COLOR]

Amend the red bits.

 

[MStef]

Look at "DemoQueryExcelA2000.mdb" (attachment, zip).
Open form and try. Look at VBA, Query1.

 

[sladetroityer]

I am getting an error when I run this "Invalid Outside procedure"


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

Private Sub cmdExportExcel_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qselEmployeeInformation", "c:\EmployeeInfo.xls", True
End Sub

 

[vbaInet]

Of course it has to be inside a Function or a Sub don't you think?

 

[sladetroityer]

So I would type Sub first? Or Function? What would the code look like with a sub or a function?

 

[vbaInet]

You already have a Sub present. Put it within the sub.

 

[sladetroityer]

Below is my Sub and it doesn't work either....

Private Sub cmdExportExcel_Click()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qselEmployeeInformation")
qdf.SQL = "SELECT * FROM TableName WHERE " & Me.Filter
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qselEmployeeInformation", "c:\EmployeeInfo.xls", True
End Sub

 

[vbaInet]

Well, what is in Me.Filter?? Is your form currently being filtered?