Export records via macro and append an existing Excel file

[jcf378]

hello--
i am having trouble figuring out how to export individual records from an Access 2002 Form into a pre-existing Excel spreadsheet, such that the exported record is merely appended to the Excel file, rather than completely overwriting it (as occurs with the basic OutputTo function).

specifically, I am trying to create a macro that "backs-up" any deleted record. So, when someone goes to delete a record (i have a delete-record macro that deletes the selected record on a form via a command button), instead of deleting the record forever, the macro exports the selected record into an Excel file, which will contain a running list of all the deleted records. any thoughts?

thanks,
jason

[ADezii]

hello--
i am having trouble figuring out how to export individual records from an Access 2002 Form into a pre-existing Excel spreadsheet, such that the exported record is merely appended to the Excel file, rather than completely overwriting it (as occurs with the basic OutputTo function).

specifically, I am trying to create a macro that "backs-up" any deleted record. So, when someone goes to delete a record (i have a delete-record macro that deletes the selected record on a form via a command button), instead of deleting the record forever, the macro exports the selected record into an Excel file, which will contain a running list of all the deleted records. any thoughts?

thanks,
jason

I have a much simpler and even more practical solution involving only 3 lines of code. Prior to a Record's deletion, run the code below. It will open the specified *.txt File and "append" the relevant data to it in a comma delimited format. This data could then be easily imported into either Access, Excel, or a variety of other applications with a minimal amount of effort. The Import Process could be easily automated. The other option would probably be complex Automation Code. The code is listed now for your preview:

Open "C:\Deleted.txt" For Append As #1
  Write #1, Me![txtPayrollNumber], Me![txtLastName], Me![txtFirstName], CStr(Me![txtBirthDate])
Close #1

The Text File would look something like:
"6537228",#NULL#,"John","3/23/1940"
"9876343","McNamee","William","1/26/1947"
"10408564","Alberici","James","2/16/1948"
"10567888","Mansfield","Martin","12/7/1943"
"10637565","DelRossi","George","11/5/1946"
"10294610","Noble","Robert","8/16/1945"
"10954701","McGarrigle","James","10/3/1947"
"10496556",#NULL#,#NULL#,"3/17/1949"
"11130747","Dougherty","Robert","8/12/1948"
"11355062","Jackson","Joseph","1/16/1947"
"87654912","Dezii","Armund","3/17/1949"
"17189480","Black","Matthew","11/11/1944"
"11484877","Campbell","William","2/12/1946"
"11866509","Gaffney","William","5/14/1947"
"12304426","Matarazzo","Vincent","4/13/1943"
"12321281","Deposit",#NULL#,"3/17/1949"
"12333079","McCloskey","John","6/1/1942"
"12639184","Grande","Robert","5/29/1947"
"13292686","Yaeger","Michael","12/27/1951"
"71729342","Hanson","Francis","8/18/1942"
"13293862","Carpenter","Joseph","7/22/1943"
"25536800","Simon","Harry","7/17/1948"
"66253599","Severio","Robert","1/6/1992"
"82593570","Fowler","Fiona","3/17/1949"

[jcf378]

perfect...thanks.
--jason

[NeoPa]

Nice solution :)

[ADezii]

perfect...thanks.
--jason

No problemo

[ADezii]

Nice solution :)

Thanks for the vote of confidence, it is always appreciated.

[NeoPa]

Hijack post moved to its own thread at Export Access Query into Excel Worksheet.