471,337 Members | 829 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,337 software developers and data experts.

Export records via macro and append an existing Excel file

6
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
Feb 5 '07 #1
6 5946
ADezii
8,826 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Open "C:\Deleted.txt" For Append As #1
  2.   Write #1, Me![txtPayrollNumber], Me![txtLastName], Me![txtFirstName], CStr(Me![txtBirthDate])
  3. 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"
Feb 6 '07 #2
jcf378
6
perfect...thanks.
--jason
Feb 6 '07 #3
NeoPa
32,405 Expert Mod 16PB
Nice solution :)
Feb 6 '07 #4
ADezii
8,826 Expert 8TB
perfect...thanks.
--jason
No problemo
Feb 6 '07 #5
ADezii
8,826 Expert 8TB
Nice solution :)
Thanks for the vote of confidence, it is always appreciated.
Feb 6 '07 #6
NeoPa
32,405 Expert Mod 16PB
Hijack post moved to its own thread at Export Access Query into Excel Worksheet.
Jul 15 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

11 posts views Thread by Mike MacSween | last post: by
2 posts views Thread by Arvind R | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.