export a filtered search from a datasheet to an excel

[janeyg]

Hi
I am trying to export a filtered search from a datasheet to an excel spreadsheet - only including certain columns. I am using the below code but it is falling over on the column selection. Without that part it exports fine but i really want to exclude some columns from the export.

Private Sub Command1021_Click()
Columns("H:J").EntireColumn.Hidden = True
DoCmd.OutputTo acOutputForm, "RenewCancelList", acFormatXLS, _
"U:\BOB\ExportedResults.xls"
MsgBox ("Export Complete")
End Sub


Can anyone help?
Janeyg

 

[Trevor G]

You should use a query rather than the datasheet, set the query to include your criteria and exclude the columns you don't need then you have the source for the output to Excel.

 

[boblarson]

After trying a lot to get something to work reliably and with possible situations depending on what the recordsource is of the form (table/query name or select statement), I can do it using a table or query name but am having a harder time coming up with something if the form's record source is a select statement.

It might just be easier to delete the columns you don't want AFTER export than to try to stop them from going.

 

[cyb3rwolf]

If I'm understanding what you're trying to do correctly, i would go a different direction altogether. I would create a query, and output that instead. If you're using user-defined filter conditions specified on the form; you can simply tie those filter definitions to the query definition.

 

[janeyg]

I did set up a query to include only the columns I wanted to export and that worked fine but it output all the data and not the data in the filtered on a search in the datasheet. I dont know how to do that bit? I don't know how to set the query so that it exports the filtered data in the datasheet?

Can you advise me how to do that please?
thanks

 

[Trevor G]

If you have set up the query then set the criteria in the query design window not on the datasheet as when you run the query the datasheet is reset. So set the criteria in the design area of the query, if you have problems then let us know what the criteria is against the field names and table name, this can then be replicated to provide a solution for you.