Copy Selection and Append Paste

  • Thread starter Thread starter ktoth04
  • Start date Start date
K

ktoth04

I would like to create a VB that copies the current selection FROM EXCEL,
switches to Access (where timecard.mdb is open), opens Table "Eng Hours
Master New Format" and append pastes the data, and then saves the table.

Is it even possible to cross program VB like that? I have no idea...
 
You can use VBA to do things between EXCEL and ACCESS, but what you seek to
do here will not work because ACCESS is not a spreadsheet program and
therefore does not store data the same way the EXCEL spreadsheet does --
therefore, a simply Copy/Paste (such as you'd do in EXCEL VBA for worksheet
data) is not applicable.

You can write code that "walks across" the first row of the Selection in
EXCEL, and adds the data field by field to a new record in the ACCESS table
(via a recordset or via an SQL statement), and then "walks down" to the next
row and repeats the "walk across" process for a new record, and continues
until the end of the Selection. How familiar are you with EXCEL and ACCESS
VBA programming?
 
I know next to nothing about how to program in VBA, I need to get to the
library and pick up a guidebook... lol
 
The VBA code to do what you need here is not the most complex, but it's
definitely not simple either. It actually requires knowledge of VBA in both
EXCEL and ACCESS because you will need to know how to do things in both
environments' data sets. Any particular reason you are using this setup
instead of having all the data in ACCESS from the beginning? Is this a
process that you repeat regularly? It might be easier if ACCESS were to read
the EXCEL spreadsheet directly instead of using a somewhat complicated
"middle man" of selecting cells and asking ACCESS to "copy" the data.
 
Its a timesheet thing, so everyone hands in an excel spreadsheet (and I don't
have the authority to change the system, no matter how silly it is). So each
week we get something like 20 of these spreadsheets, and using a hidden sheet
all the new data is accumulated in the same sequence as it is stored in
Access, and is then manually copied and pasted into Access.
 
This sounds as if it could be "handled" by using the TransferSpreadsheet
action (macro or VBA) to read and import the "accumulated" data into a
temporary table in ACCESS, and then use an append query to copy the data
from the temporary table into the permanent table.

But, if you want to try another manual approach, copy just the cells that
you want to copy into the ACCESS table (columns must match the first fields
in the ACCESS table, so if you have an autonumber primary key field in the
ACCESS table, move it to the "end" of the field list (do this in design view
of the table)), go to the datasheet view of the table, highlight the entire
"new row" (bottom row, one with the * on the left) by clicking on the record
selector "button" at far left of the row, and then press Ctrl+v to paste the
data into the table.
 
Back
Top