By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,497 Members | 1,287 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,497 IT Pros & Developers. It's quick & easy.

Export records via macro and append an existing Excel file

P: 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
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,701
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

P: 6
perfect...thanks.
--jason
Feb 6 '07 #3

NeoPa
Expert Mod 15k+
P: 31,768
Nice solution :)
Feb 6 '07 #4

ADezii
Expert 5K+
P: 8,701
perfect...thanks.
--jason
No problemo
Feb 6 '07 #5

ADezii
Expert 5K+
P: 8,701
Nice solution :)
Thanks for the vote of confidence, it is always appreciated.
Feb 6 '07 #6

NeoPa
Expert Mod 15k+
P: 31,768
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.