• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to update data from Excel to Sharepoint Tables/Lists

Status
Not open for further replies.
S

S

New Member
Hi All,


I'm looking to update/append data from Excel to Sharepoint, and again retrieve the same.

I basically need this for multiple users having the excel template with macro to update their respective data, which gets updated onto sharepoint, and then when I run another macro, I get all the records from Sharepoint. (I want something similar to 'excel to access' and vice-versa)

I googled and found out addins to syncronize excel and SP, but somehow I was not able to use the same.(And I cannot download those addins at office)

Any suggestions in vba please.


Regards,

SS
 
Thanks for the link Narayan, I infact had googled this link before, however, what Im looking for is :

An excel template with a macro - from which data will be updated by multiple users(which means each one will have their own copy of this template(say temp1)),

this data needs to be appended to the data list on Sharepoint, and then I will have another macro which once run - should give me the entire dump into the set template(say Temp2).


The code in above link you provided will download and save the file located on sharepoint.
 
@ S23: How are you planning to save your data into SharePoint - Would it be an excel file or on a SharePoint list?


Comment on Option 1: If excel files, you can command files in a SharePoint list just like you do with one saved on your local machine. Just disable the Checked Out option for files in SharePoint because I dont dont how to check the files out using VBA. I tried searching for a solution but in vain. There are some classes in Office VBA but they didnt work in Office 2007. Haven't tried it on Office 2010.


Comment on Option 2: If on a SharePoint link, then you can command your SP Lists like access tables and upload data using Data connection/SQL queries. Using SharePoint Lists are easy, you can download SharePoint List as a linked/static table in excel.
 
Akash - Im looking to update the data into SharePoint list.

Will check the link that you provided - thanks for your time.
 
Akash,

I tried the code in above link, and it says "An unexpected error has occurred. Changes to your data cannot be saved"


However, as I need the data to be appended, I doubt if this would solve my problem.


as you stated:

Comment on Option 2: If on a SharePoint link, then you can command your SP Lists like access tables and upload data using Data connection/SQL queries.
Click to expand...

can you help me with those commands - I have really no clue on that.


this was the code I used from that link:

Code: 
Public Sub PublishList()

' Get the collection of lists for the active sheet

Dim L As ListObjects

Set L = ActiveSheet.ListObjects

' Add a new list

Dim NewList As ListObject

Set NewList = L.Add(xlSrcRange, Range("A1:G8"), , True)

NewList.Name = "PartsList"

' Publish it to a SharePoint site

NewList.Publish Array("http://sharepointportal.xxx.com/personal/xxx/_layouts/viewlsts.aspx?BaseType=0", _

"NewLists "), True

End Sub


thanks in advance :)
 
S said:
Hi All,


I'm looking to update/append data from Excel to Sharepoint, and again retrieve the same.

I basically need this for multiple users having the excel template with macro to update their respective data, which gets updated onto sharepoint, and then when I run another macro, I get all the records from Sharepoint. (I want something similar to 'excel to access' and vice-versa)

I googled and found out addins to syncronize excel and SP, but somehow I was not able to use the same.(And I cannot download those addins at office)

Any suggestions in vba please.


Regards,

SS
Click to expand...
Hi SS,

did you get solution to add items from an excel into SharePoint List? I have a similar requirement.

Thanks MM
 
Status
Not open for further replies.
Back
Top