Here are the steps to accomplish this:
1. Create a table in Access with the same columns as there in the Excel sheet. So for our example lets say that our excel has a column of data with the Column name as "Title" and we will keep the workbook name as Data in the Excel. So In Access we need to create a table having a column with the name as "Title". Lets have the name of the table created in Access as "AccessEmployeeTitle".
2. Now we will add a button to the Access tool, On click of which the Data in the excel sheet will be loaded into the table created in the step 1.
3. Add onClick eventhandler to the button and add the below code
On Error GoTo UploadDataErr
Dim Status As Integer
Status = ImportFile("AccessEmployeeTitle", "Data")
If (Status) Then
MsgBox "Successfully upload the data"
MsgBox "Error in Upload Data" & vbNewLine & "Error ID: " & Err & vbNewLine & "Error Desc: " & Error$
In this code the AccessEmployeeTitle is name of the table in Access and Data is the name of the workbook in Excel sheet. This code will put the data from Excel sheet to the local table created in Access.
4. Create a Sharepoint linked list table on Access. Here are the steps:
a. Goto Create Menu->Sharepoint lists -> Existing sharepoint list
b. Enter the SharePoint Site URL having list to insert the data:
c. Select the option “Link to the data source by creating a linked table”.
d. This will show all the lists in the SharePoint URL. Select the list in which we need to insert the data. I have a list with the name "SharePointEmployeeTitle".
e. With these steps a linked list in Access will be created and now you can directly edit the data in this list which will be reflected in SharePoint list.
5. Now we will create a stored proc which will copy data from the Access table created in the Step 1 in the SharePoint list:
INSERT INTO SharePointEmployeeTitle ( Title )
WHERE NOT EXISTS (SELECT ID FROM SharePointEmployeeTitle WHERE SharePointEmployeeTitle.Title = AccessEmployeeTitle.Title);
6.Create another button, and on clicking of this button, Data would be transferred to SharePoint list. So we will use OnClick event of this button and here is the code for transferring the data:
On Error GoTo TransErr
DoCmd.OpenQuery "<Query created in Step 5>"
MsgBox "Successfully Transferred data"
MsgBox "Error in Data Transfer" & vbNewLine & "Error ID: " & Err & vbNewLine & "Error Desc: " & Error$
So with these steps this can be accomplished.This solution gives the flexibility to append the data in SharePoint list and do not use any complex Object model code. It uses only OOB features of SharePoint.