Exporting 1 Line after Another To Excel From Form

[Help.Chris]

Hi All,

I have built a form for user to export an individual record from a form. the user searches for each record in turn.

When the user has found the record i would like them to be able to export directly to an Excel spreadsheet.
After exporting 1, i would like them to search for another and then export to the next line in the spreadsheet.

I have been searching for ages on this, and now i am severely confused. Can this be done, if so how.

If not, what is the best way of accomplishing something similar, exporting each record to a temp table, and then having the user export all records at once.

Thanks for any help.

Chris

 

[Fizzio]

Have you looked at the TransferSpreadsheet function?

 

[Pat Hartman]

The TransferSpreadsheet Method/Action is intended to work on a set of data and does not have the ability to specify a specific location in the spreadsheet where the exported data should be placed. If you need this type of control, you'll need to do it via OLE from VBA. If you're not familiar with the Excel Object Model and you're not an experienced programmer, this will be difficult.

An easier (minimal code) method would be to let them choose the records to export, one at a time, but place them in a work table. Once all the records are selected, you can use the TransferSpreadsheet Method/Action to export the work table.

 

[Ken Grubb]

The Access Web has a number of sample code modules including several examples of Excel automation. Transferring Records to Excel with Automation may be what you're seeking.

HTH,

 

[llkhoutx]

Check out "Excel Automation" in any of Litwin, et al's Access 97/2000/2002 Developer's Handbooks. They have a good example of how to do it. With their technique, I've manipulated Excel sheets from Access before, but its been a while, that project and its code are history.