Export records via macro and append an existing Excel file

6 New Member

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

Subscribe Reply

6309

ADezii

8,834

Recognized Expert Expert

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

 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","McNa mee","William", "1/26/1947"
"10408564","Alb erici","James", "2/16/1948"
"10567888","Man sfield","Martin ","12/7/1943"
"10637565","Del Rossi","George" ,"11/5/1946"
"10294610","Nob le","Robert", "8/16/1945"
"10954701","McG arrigle","James ","10/3/1947"
"10496556",#NUL L#,#NULL#,"3/17/1949"
"11130747","Dou gherty","Robert ","8/12/1948"
"11355062","Jac kson","Joseph", "1/16/1947"
"87654912","Dez ii","Armund", "3/17/1949"
"17189480","Bla ck","Matthew"," 11/11/1944"
"11484877","Cam pbell","William ","2/12/1946"
"11866509","Gaf fney","William" ,"5/14/1947"
"12304426","Mat arazzo","Vincen t","4/13/1943"
"12321281","Dep osit",#NULL#,"3/17/1949"
"12333079","McC loskey","John", "6/1/1942"
"12639184","Gra nde","Robert"," 5/29/1947"
"13292686","Yae ger","Michael", "12/27/1951"
"71729342","Han son","Francis", "8/18/1942"
"13293862","Car penter","Joseph ","7/22/1943"
"25536800","Sim on","Harry"," 7/17/1948"
"66253599","Sev erio","Robert", "1/6/1992"
"82593570","Fow ler","Fiona"," 3/17/1949"

Feb 6 '07 #2

jcf378

New Member

perfect...thank s.
--jason

Feb 6 '07 #3

NeoPa

32,569

Recognized Expert Moderator MVP

Nice solution :)

Feb 6 '07 #4

ADezii

8,834

Recognized Expert Expert

perfect...thank s.
--jason

No problemo

Feb 6 '07 #5

ADezii

8,834

Recognized Expert Expert

Nice solution :)

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

Feb 6 '07 #6

NeoPa

32,569

Recognized Expert Moderator MVP

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

Jul 15 '10 #7

Similar topics

4193

Local Access database to shared PHP/MySql hosting export

by: Mike MacSween | last post by:

My client has an MS Access database application on her local machine. I have full access to that in terms of changing the design. I've got a simple PHP/MySql application on shared hosting, so no direct access to the db server. I'd like to give her the facility to export the information in her local Access application to the shared PHP/MySql site. From one command button (or similar) in the Access application.

PHP

1898

Data types and export to Excel from ASP page

by: Bryan Russell | last post by:

Hi, My asp/sql server application gets an error, but only when you use the Excel export option: <% Response.ContentType="application/vnd.ms-excel" %> (very popular w/ users) I think somebody entered a string of characters that Excel does not like

ASP / Active Server Pages

25052

VBA code help Export Excel data to Access table

by: Mads Petersen | last post by:

I'm stuck in this code. Hope you can and will help me. I launch it from excel. I have made the following code work, but not as i whant. I need the ranges to be working with something like xlDown. eg. this only transferes the first record in the area. ..Fields("Uge").Value = ws.Range("A98").Value Sub SelectMaster()

Microsoft Access / VBA

18238

Export to Excel multiple existing sheets

by: sunilkeswani | last post by:

I need help with exporting data from 2 access tables, into 2 existing spreadsheets in a single Excel file. Currently, I am using this code: DoCmd.TransferSpreadsheet acExport, 8, "Table1", "D:\Test.xls", True

Microsoft Access / VBA

6539

Export to Excel issue (C#)

by: Arvind R | last post by:

Hello, how to ask saveas dialog before writing the data to the excel file? right now im able to save in c drive or any other specified location only. any solution will be a great help! System.Text.StringBuilder sbrHTML=new System.Text.StringBuilder(""); //StringBuilder sbrHTML = new StringBuilder();

ASP.NET

6437

CSV Export: Force Trailing Comma?

by: bonehead | last post by:

Greetings, I'm using the DoCmd.TransferText method to export the results of a MS Access query to a csv file. The csv will then be used to load an Oracle table. In other systems such as TOAD for Oracle, it's possible to force an additional comma delimiter after the last column, if the column is empty on a particular row. Oracle requires this additional comma on empty rightmost columns, for importing purposes.

Microsoft Access / VBA

8072

Append Selected Records to Another Table

by: wvmbark | last post by:

First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months. Every day we incur numerous service problems “Events”. Each morning we have a global conference call where events which occurred within the previous 24 hours are discussed. Prior to the call, an analyst has to review these events and provide a report, ‘The Morning...

Microsoft Access / VBA

4222

Excel Macro export data to specific Access Record

by: redbenn | last post by:

Hello, I am trying to create an excel macro, that when clicked... a specific cell will match a record in my database, and update certain fields in this record. The Excel file will have a cell that is the same as a 'Tracking Number' (the primary key) of my Access Table. The output from excel would access this record and update the field. I currently have a macro to create new records in the table, but I am not sure if there is a way to...

Microsoft Access / VBA

7838

Create a form button to export a table

by: DeZZar | last post by:

Hi all, I need to regularly backup my database as an Excel file and have been using the File Export option. Problem is I need anyone using the database to be able to do this easily - nopt just me. For all users the database is hidden and only a menu screen is visable. Can I create a button on my menu screen that will export a table >

Microsoft Access / VBA

8609

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

9166

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8899

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

6525

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5861

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4371

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4621

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3052

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2007

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General