Import Sharepoint list via VBA in specific view

Question

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

Answer 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"

Answer 2

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.