batch update

[DM]

If you understand in those terms, is there a way to batch update an Excel
spreadsheet to an already populated Access table?

Basically, I routinely get spreadsheets of data that I need to amend to an
Access table. It is very time consuming to copy and paste line by line.

Thanks in advance.

 

[mcescher]

If you understand in those terms, is there a way to batch update an Excel
spreadsheet to an already populated Access table?

Basically, I routinely get spreadsheets of data that I need to amend to an
Access table. It is very time consuming to copy and paste line by line.

Thanks in advance.

Well, If all the columns line up, you could just highlight all the
lines, copy and do one big paste.

There are ways to automate this, but you might want to come up with a
better definition of "routinely". Four times a day, weekly, or twice
a year? That will help you decide how much time and energy you want
to put into automation. Anyway, you might try linking the Excel sheet
as a table, and then running an append query to your destination
table. You'll want to do your testing on a backup copy of your
database.

 

[DM]

follow up info:

I've though about linking an Excel spreadsheet to the Access table but when
I'm not around and it's time for records management and clean up the file
server, the not so computer savvy co-workers delete or move and break the
links. Then I get fussed at for not having a reliable product.

 

[DM]

Thanks,

It could be as often as weekly. The problem, the way I do it anyway, is the
one big copy/paste either by one roll at a time or add enough new blank
rolls to account for the one big paste.

Do you have a better way to eliminate the extra task of pre-creating the
needed blank rolls?

If you understand in those terms, is there a way to batch update an Excel
spreadsheet to an already populated Access table?

Basically, I routinely get spreadsheets of data that I need to amend to an
Access table. It is very time consuming to copy and paste line by line.

Thanks in advance.

Well, If all the columns line up, you could just highlight all the
lines, copy and do one big paste.

There are ways to automate this, but you might want to come up with a
better definition of "routinely". Four times a day, weekly, or twice
a year? That will help you decide how much time and energy you want
to put into automation. Anyway, you might try linking the Excel sheet
as a table, and then running an append query to your destination
table. You'll want to do your testing on a backup copy of your
database.

 

[Ron2006]

My results with linking to spreadsheets have been mixed, so I always
use importing.

1) I have a set directory where they are to put the spreadsheed that
needs to be imported.
2) If the spreadsheet always has the same name then I supply it in a
txtbox, In some apps if it is always named via a certain convention
(like always "xxxxx 20080315.xls" with the date being a Saturday, and
I supply that name. But in all cases I allow them to modify the name
to whatever they saved it as.

3) Clicking an Import button, checks if the files is there, imports
it, Appends the import to existing tables, copies the imported
spreadsheet to another directory with -yyyymmdd added to the name, and
erases the just imported spreadsheet. One app keeps track of what
sheets have been imported and if it has already been done once it
warns the user and they can abort the import - that option is not
foolproof if they are supplying the name but mostly they don't supply
the name.

Ron

 

[John W. Vinson]

If you understand in those terms, is there a way to batch update an Excel
spreadsheet to an already populated Access table?

Basically, I routinely get spreadsheets of data that I need to amend to an
Access table. It is very time consuming to copy and paste line by line.

Thanks in advance.

File... Get External Data... Import; select .xls as files of type;

OR

VBA code using the TransferSpreadsheet method.

Pasting line by line!? Ow. Not *quite* as bad as printing out and retyping but
close!

 

[DM]

Wow, I thought I knew MS Access. What is VBA?

Also, I didn't quite understand your closing comment. I must be having a bad
comprehensive day. Thanks a million for the help to all.

 

[John W. Vinson]

Wow, I thought I knew MS Access. What is VBA?

Visual Basic for Applications, also known as a Module. One of the things you
can do in VBA code is use the "TransferSpreadsheet" method to open an Excel
spreadsheet and transfer its contents into a table.

Also, I didn't quite understand your closing comment. I must be having a bad
comprehensive day. Thanks a million for the help to all.

About pasting line by line? Just expressing sympathy for your onerous chore.

 

[DM]

Thanks for the help and the lesson on VBA.

Visual Basic for Applications, also known as a Module. One of the things
you
can do in VBA code is use the "TransferSpreadsheet" method to open an
Excel
spreadsheet and transfer its contents into a table.


About pasting line by line? Just expressing sympathy for your onerous
chore.