Excel: Run-time error '1004' - Application-defined or object-defined error

0
votes

I am trying to build a macro to pull information from a SharePoint List. I'm using the following code and receiving an error:

Run-time error '1004' - Application-defined or object-defined error

Sub ImportSharePointList()

Dim objMyList As ListObject
Dim objWksheet As Worksheet
Dim strSPServer As String
Const SERVER As String = "Services/RemoteServices/Lists/"
Const LISTNAME As String = "{2B965E30-4D0D-4D81-BC27-2CDCEC10A17B}"
Const VIEWNAME As String = "{9C91F383-8C5C-40BD-81E2-7319DA1A6E61}"

strSPServer = "http://" & SERVER & "AllItems.aspx"

Set objWksheet = Worksheets.Add

Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, Array(strSPServer, LISTNAME, VIEWNAME), False, , Range("A1"))

End Sub
2
excelvba
Is it because the strSPServer variable ends up with a value of http://Services/RemoteServices/Lists//AllItems.aspx? Notice the two / slashes before 'AllItems'? – andrew
I wish it was that simple! Good catch, but I still get the same error after removing. – Sol_Kralj
Unfortunately I can't see anything else that's obvious to me. Is there a chance that the unqualified Range("A1") reference is causing a problem? It has to be the same worksheet that you're adding the ListObject to, but having said that, I thought adding a sheet would make it the active sheet anyway. I avoid unqualified references like this just to be sure. – andrew

2 Answers

0
votes

The problem is related to your Destination of Range("A1") on a newly created worksheet.

From the documentation:

Destination Variant (Optional)

A Range object that specifies a single-cell reference as the destination for the top-left corner of the new list object. If the Range object refers to more than one cell, an error is generated.

  • If SourceType is set to xlSrcExternal, Destination must be specified.

  • If SourceType is set to xlSrcRange, Destination is ignored.

  • ☆ The destination range must be on *the worksheet that contains the ListObjects collection specified by expression. ☆

  • New columns are inserted at the Destination to fit the new list. Therefore, existing data is not overwritten.

It's impossible to troubleshoot completely without access to the server, but you can confirm the problem by reviewing the method syntax in the documentation.

Documentation:

0
votes

After several attempts, I could not get this code to work. It likely has to do with either permissions or our version of SharePoint. However, I do have a workaround:

From the SharePoint List:

  1. Go to List Tools > List > Export to Excel
  2. Save the IQY file to a path of your choice

  3. Now use the following code:

    Sub SharePointList()    

Dim IQYFile as String
Dim ws as worksheet
Dim cn as variant

Set ws = Worksheets.Add

IQYFile = "\\network\folder\SharePoint\list\owssvr.iqy" 'Enter the folder path & file name

With ws.QueryTables.Add(Connection:="FINDER;" & IQYFile, Destination:=Range("A1"))
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With

For Each cn In ActiveWorkbook.Connections
    cn.Delete
Next cn

End Sub