1

I am currenty trying to import a Sharepoint list in an Excel sheet via VBA and thanks to this: Import Sharepoint List into Excel Using VBA Only It is working pretty well, but if i leave the "VIEWNAME" field blank it only imports the list in the default view and if i put another name in the field it throws an error. Thanks in advance

Sub AddImportTable(KPIrow As Integer)
Dim objMyList As ListObject
Dim objWksheet As Worksheet
Dim strSPServer As String
Dim SERVER As String
beforeServerInput:
ServerInput.Show ' This is just a custom inputbox userform but works the same  ' 
 SERVER = ServerInput.TextBox1
If SERVER = "" Then
a = MsgBox("You have not entered a right statetment. Continue?", vbYesNo, "ERROR")
If a = vbNo Then

Exit Sub
ReturnValue = 1
Else
GoTo beforeServerInput
End If

End If
Dim LISTNAME As String
beforeListInput:
ListInput.Show
LISTNAME = ListInput.TextBox1
If LISTNAME = "" Then
a = MsgBox("You have not entered a right statetment. Continue?",          vbCriticalYesNo, "ERROR")
If a = vbNo Then

Exit Sub
ReturnValue = 1
Else
GoTo beforeListInput
End If

End If
Const VIEWNAME As String = ""
VIEWNAME = InputBox("Please enter the name of the view you want to import. For     example: 'All Items'. If you leave this blank standard view will be taken.", "View?")
' The SharePoint server URL pointing to'
' the SharePoint list to import into Excel.'
strSPServer = SERVER & "/_vti_bin"
' Add a new worksheet to the active workbook.'

Set objWksheet = Sheets.Add

On Error GoTo del
 Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _
    Array(strSPServer, LISTNAME, VIEWNAME), True, , Range("A2"))

objWksheet.Visible = xlSheetHidden
ListInput.TextBox1 = ""
ServerInput.TextBox1 = ""
Sheets("Sources").Cells(KPIrow, 1) = KPIrow - 2
Sheets("Sources").Cells(KPIrow, 3) = SERVER
Sheets("Sources").Cells(KPIrow, 4) = LISTNAME
objWksheet.Name = Sheets("Sources").Cells(KPIrow, 2)
ReturnValue = 0
GoTo ende

del:
Application.DisplayAlerts = False

objWksheet.Delete
Application.DisplayAlerts = True
Set objMyList = Nothing
Set objWksheet = Nothing
Sheets("Sources").Cells(KPIrow, 2) = ""
ReturnValue = 1
Err = MsgBox("I am sorry, but an Error occured.", vbCritical, "Error")
ende:
On Error Resume Next

Set objMyList = Nothing
Set objWksheet = Nothing
answer = MsgBox("Do you want to save? It is heavily recommended, because otherwise you can't refresh everything.", vbYesNo, "Save")
Application.DisplayAlerts = False

If answer = vbYes Then
ActiveWorkbook.Save
End If
Application.DisplayAlerts = True
End Sub

PS: I am using Excel 2010 but im not sure which sharepoint version

  • You can't import into a "View". You can import into a list and the views are a set of selection criteria against the list. – Paul Leigh Mar 13 '14 at 8:16
  • I think you are misunderstanding me. I want to import a List from a Sharepoint. So when i finished my makro, there is a newly created Sheet in my Excel Workobook with the Sharepoint list. So the information is on the sharepoint but i want it in my excel sheet. – AElxs Mar 13 '14 at 8:35
1

Ok iIknow now why it threw an error. I did not know that I needed the GUID of the View.

Just needed to put

 Viewname={84A7F7D8-C2E2-4C11-A105-39A436B39F53}

instead of

 VIEWNAME="All items"
  • Hi! Can you please extend this answer with the code that made it work? It will help future readers a lot – Robert Lindgren Mar 14 '14 at 9:48
  • I am sorry but there is no Code that made it work. I just needed to put Viewname={84A7F7D8-C2E2-4C11-A105-39A436B39F53} instead of VIEWNAME="All items". (This are only examples you need the specific GUID for this) – AElxs Mar 14 '14 at 14:29
  • To me, those are lines of code ;) I edited your answer for you to give you an idea of what I was looking for – Robert Lindgren Mar 14 '14 at 14:30
1

I know this is an old entry, but as I've been looking for solutions four years later, someone else may benefit from this information.

Public Function spEdit(wb As Workbook)
    Dim xlapp       As Object        
    Dim wb          As Workbook
    Dim ws          As Worksheet
    Dim src(0 To 2) As Variant

    Set xlapp = CreateObject("excel.application")
    Set wb = xlapp.Workbooks.Add
    Set ws = ws.Sheets.Add
    ws.Name = "Data"

    src(0) = "http://yourSite/sub_dir1/sub_dir2/blahblah/" & "_vti_bin" 'tableSource
    src(1) = "{abc123d4-999Z-1234-12AB-123HG444XXAA}" 'List ID example
    src(2) = "{XXXXXXXX-12AB-1AA1-12A1-1E2345678A9B}" 'GUI ID example

    ws.ListObjects.Add xlSrcExternal, src, True, xlYes, ws.Range("A1")

 End Function

Imports the SharePoint table in the desired view to the new table in the new sheet in the new workbook in the new window.

EXCEL 2010 - To find the List ID and GUI ID, export the table from SharePoint to Excel. In the Table Tools - Design ribbon, click the drop down option on the Refresh command. Select Connection Properties. In the new window, select the Definition tab. the long string of alphanumeric characters between the tags is the GUI ID. the long string of alphanumeric characters between the tags is the List ID.

Public Function saveSP(wb As Workbook)
    Dim ws      As Worksheet
    Dim splist  As ListObject

    On Error GoTo errH

    Set ws = wb.Sheets("Data")
    Set splist = ws.ListObjects(1)

    splist.UpdateChanges xlListConflictDialog

    Set ws = Nothing
    Set splist = Nothing
Exit Function

errH:

    Debug.Print Err.Description & Err.Number

End Function

Save Changes function that pushes your changes to the SP List back out to SP.

Public Function addtoSP(refRow As Integer, ws As Worksheet, ws2 As Worksheet)
    'My flag to determine if the row should be added or not.
    'Ensures line items are not duplicated in SP
    If ws2.Cells(refRow, 23) <> "New" Then Exit Function

    Dim tbl     As ListObject
    Dim newR    As ListRow

    Set tbl = ws.ListObjects(1)
    Set newR = tbl.ListRows.Add

    With newR
        .Range(2) = "This is what you see column A"
        .Range(3) = ws2.Cells(refRow, 2).value
    End With
End Function

Adding a new row to imported SP List. I pass the sheet containing the SP List, my datasheet that I intended to integrate into SP, and a row reference to pull the data into SP quickly.

Public Function updateSP(refRow As Integer, wb As Workbook, ws2 As Worksheet)
    Dim tbl     As ListObject
    Dim aName As String
    Dim newN    As String
    Dim lRow    As Integer
    Dim uAns    As Integer
    Dim aDate
    Dim cost    As Currency
    Dim ws      As Worksheet

    Set ws = wb.Worksheets("Data")
    Set tbl = ws.ListObjects(1)
    aDate = CDate(ws2.Cells(refRow, 8))

    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row 'SP worksheet lRow

    With tbl
        For i = 2 To lRow
            If .ListRows(i).Range(4).Value = aName Then
                If .ListRows(i).Range(14).Value = ws2.Cells(refRow, 10) Then
                    If .ListRows(i).Range(26).Value = aDate Then
                        .ListRows(i).Range(2) = "This is what you see in Column A"
                        cost = ws2.Cells(refRow, 13) '$
                        .ListRows(I).Range(3) = cost
                        uAns = ws2.Cells(refRow, 1).Value 'OFFICE NUM
                        .ListRows(i).Range(7) = uAns
                        Exit For
                    End If
                End If
            End If
        Next i
    End With
Exit Function

Update SP function cycles through the SP List and compares the owner's name (aName), the item's description, and the date it was entered. This is my key; yours may be different. We have unique key values, but they don't always get entered before the record is created. If you had a unique key, you could just do one If statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.