X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Copy/paste From Excel To Table, Access 2010    
post Feb 2 2018, 10:26 AM

Posts: 89
Joined: 10-January 17

I've created a db that holds ~75 employees information (named the Slate Manager Tool), and a similar db (minus reports and queries) which the employee fills out/updates periodically (named the Profile Worksheet Tool). The employee's version only contains his information as I want to prevent sharing of certain information. After the employee has updated his Profile, I've built a button which saves the information as Excel 97 - Excel 2003 Workbook (*.xls), and emails the excel file as an attachment to the Slate Management Tool operator's email. Because each person uses the same PWT, the name of the excel file is Faculty.xls by default.

When the SMT Operator receives the excel file, what is the easiest way for him to import the information to the table [tblFaculty] of the parent db? For the record, both the table being exported and the table being imported to are formatted exactly the same.

I thought I was able to just copy/paste, but doing so takes all the information copied and pastes it into the first field of the table.
Go to the top of the page
Doug Steele
post Feb 2 2018, 10:42 AM

UtterAccess VIP
Posts: 22,002
Joined: 8-January 07
From: St. Catharines, ON (Canada)

Best way would like be to create a linked table pointing to the Excel workbook, run an Append query to add the data to the actual table then delete the linked table. You can write VBA code to do this repeated until all 75 spreadsheets have been read.

In the event that the spreadsheet might be updating data that's already contained tblFaculty, it's possible to write a query that will either insert or update, depending on what's appropriate. My item Doing Two Things at Once shows one way to accomplish this.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    14th December 2018 - 09:36 AM