Exporting specific records to Excel from Form

[jaskew]

I have a form that allows me to filter data for the user. I would like to be able to export that filtered data to Excel. I found a solution on here by vbaInet, but I don't think I'm writing the code correctly. Here is what I have:

strSQL = "SELECT * From [KSA_by_Media Query] WHERE ([ID]=" & Me![ID] & ");"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strSQL, pad & "Test.xls", True

Most likely the problem is that I have hardly any idea what any of that means, and I'm just copying. I've tried KSA_by_Media, which is a table rather than a query. I have also tried changing ID to the key field in my table. I also am unsure if these two lines are supposed to be combined as they are. Basically, I'm clueless, but I would appreciate any help.

Thanks

 

[John Big Booty]

What is the error that you are getting?

 

[missinglinq]

Going from a Form to Excel, I think you need to use the Output To method.

From any Form code module, try entering Output To in Help.

Linq ;0)>

 

[jaskew]

John,

I get a message saying the object couldn't be found. The object from the strSQL statement, i.e.

strSQL = "SELECT * From [KSA_by_Media Query] WHERE ([ID]=" & Me![ID] & ");"

 

[jaskew]

missinglinq,

The issue is that I need specific records based off the filter in the form, though. I don't have any issues exporting information from the form to Excel, but it exports all the information despite the filter.

 

[missinglinq]

Then I expect you need to export from a Query using the same criteria for the Query as you do for the filter.

I don't think that TransferSpreadsheet is what you need, though. This presupposes, I think, that you're starting out with a spreadsheet, which you're not.

Sorry I can't be of more help! Hopefully someone who's done this will drop by!

Linq ;0)>

 

[boblarson]

You can use this code from my website to send a filtered form to Excel:
http://www.btabdevelopment.com/ts/default.aspx?PageId=47

 

[jaskew]

Thanks. Bob, I couldn't get it to work because the code apparently had issues with some expressions I had set up in the query that populated the form. (My key has a numeric structure like an IP address, and the expression was used so I could sort the records by key in ascending order). However, I did get what I wanted doing something similar to what missinglinq suggested. I used the pre-formed macro for printing the form. Then, I went into the macro design and changed the RunCommand to OutputTo.