Edit: could I use powershell to create a list on SharePoint through
VBA?
Not specifically using Powershell but this answer attempts to answer the question :
How to create a list on SharePoint through VBA.
(1) Not sure from question as to what exactly your trying to accomplish, but this seems to be the rather most relevant snippet of code to take stuff from Excel and then write it out to a sharepoint list :
Excerpts from the link follow :
Original Question (Only the relevant portions pasted here):
I have an automation report that users use to generate a customer
report and I want to see who actually is using it and what parameters
they are using. I have thought that if I create a hidden list on a
sharepoint site, and at the end of the Excel macro I can take the
parameters from the main excel page and write it out to a sharepoint
list.
This is the answer that may work (Posted to that question above):
Sub UpdateSpecificCells()
'If nobody has the file checked out
If Workbooks.CanCheckOut("http://excel-pc:43231/Shared Documents/ExcelList.xlsb") = True Then
Application.DisplayAlerts = False
'Open the file on the SharePoint server
Workbooks.Open Filename:="http://excel-pc:43231/Shared Documents/ExcelList.xlsb", UpdateLinks:=xlUpdateLinksNever
ActiveSheet.Cells(2, 7).Value = 100
ActiveSheet.Cells(3, 7).Value = 200
ActiveSheet.Cells(4, 7).Value = 300
'Close the workbook
Workbooks("ExcelList.xlsb").Save
Workbooks("ExcelList.xlsb").Close
End If
End Sub
Another Snippet as Answer to the same question :
This updates the sharepoint list via Listobject's publish method
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
Link to the original Content here.
Two other links that may be helpful follow :
(2) How to add items to a Sharepoint list using Excel VBA?
Pasting a relevant portion from the Accepted Answer there:
All data manipulations can be done through SharePoint Lists Web
service named lists.asmx. You have to call into that web sevice. For
instance UpdateListItems method should do what MS Access does now.
One option you can use to access the lists web sevice, is "Microsoft
SOAP Toolkit" which has to be installed as office component (never
done that, but here is an article describing it:
http://oreilly.com/pub/h/1306
Another option is to use MSXML library (which is always available) and
sending SOAP requests as simple HTTP requests. Then parsing the
results as xml. There is an article showing examples how to do it:
http://blogs.msdn.com/b/jpsanders/archive/2007/06/14/how-to-send-soap-call-using-msxml-replace-stk.aspx
(3) VBA code to insert records into a Sharepoint database list
Not pasting any content from this link since not sure from your question whether this would be particularly relevant to your situation or not. Please cleck on the hyperlink to check...
Two other references that I would like you to kindly go through :
(4) Update the data in an existing SharePoint list using VBA
Well, thats not the exact title of that question there but this seems to be the actual question asked there. This could be helpful to you.
(5) Update Sharepoint list with new values from an Excel sheet (using VBA)
Please have a look at the answers to this question also ...
I guess that only if you are able to give a detailed clarification as to what exactly you are intending to accomplish, only then can we help you any further ...