Copy Access Line to Excel

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Copy Access Line to Excel

Post by JimmyC »

When we copy a line of data from an Access table to Excel {high light the row in the Access table and press copy, then press paste once in Excel} the row from Access is correctly pasted into Excel. But it also copies the Field names from the access table to the Excel worksheet.

Is there any way to copy only the row of data from access to excel and NOT have the field names also post in to Excel? We are using Office XP with Win XP Pro SP3. Thank you.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy Access Line to Excel

Post by HansV »

Welcome to Eileen's Lounge!

I don't think you can avoid pasting the column headers (field names) with the data.
You could create a macro that pastes data from the clipboard, then clears or deletes the top row:

Code: Select all

Sub PasteWithoutHeader()
  ActiveSheet.Paste
  Selection.Rows(1).Clear
End Sub
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Copy Access Line to Excel

Post by JimmyC »

Hans,
Thanks for the macro code..I will try this out this afternoon.

One follow-up question--I am not a VBA code guy at all, I know its sad. ...Where do I put this code, in the Excel workbook and then run it manually each time after I have copied the Access data line--but before pasting it in my Excel worksheet. Thanks for the additional clarification. Could the code be modified to strip the field names and also post it {data line only} into EXCEL at the current row location? Again, thank you for the reply and code.
JimC

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy Access Line to Excel

Post by HansV »

You should copy the macro into a module in the workbook. You do this in the Visual Basic Editor (press Alt+F11 while in the workbook to activate the Visual Basic Editor).

Use it like this:
- Select one or more rows in a table, query or form in Access.
- Copy.
- Switch to Excel.
- Click in the cell above the one where you want the pasted data to start.
- Instead of using the standard Paste option, run the macro.

You can press Alt+F8 to activate the Macros dialog in Excel, or you can assign the macro to a custom toolbar button (Excel 2003 and before) or a button on the Quick Access Toolbar (Access 2007 and later).
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Copy Access Line to Excel

Post by JimmyC »

Hans,
Great...Thanks for the additional clarification. Greatly appreciated....off to "play" with the code to solve my problem. Again, thanks so much for sharing your knowledge with others.
JimC