Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 16th, 2010, 02:17 PM
Registered User
 
Join Date: Aug 2010
Posts: 4
Thanks: 0
Thanked 3 Times in 2 Posts
Default Run-time error '1004' on importing SharePoint list into Excel

I am trying to import data from SharePoint list into the existing Excel workbook. I am taking this approach since "Exporting into Excel" function of SharePoint list deletes all of hte VBA code and ActiveX controls containd in the workbook.
When I executed the following code, I received Run-time error '1004': Application-defined or object-defined error. Any idea to make it work?

Code:
Sub ImportSharePointList()

    Dim objMyList As ListObject
    Dim objWksheet As Worksheet
    Dim strSPServer As String
    Const SERVER As String = "www.tc2.mycompany.com/ts/EESE/EDSCore"
    Const LISTNAME As String = "{5D21CF4A-30CC-4E35-826F-A12A71A425CB}"
    Const VIEWNAME As String = "{B270C78D-C089-4BF8-B7A8-63000FCAB8F0}"
    
   ' The SharePoint server URL pointing to
   ' the SharePoint list to import into Excel.
    strSPServer = "https://" & SERVER & "/_vti_bin"
    ' Add a new worksheet to the active workbook.
    Set objWksheet = Worksheets.Add
    ' Add a list range to the newly created worksheet
    ' and populated it with the data from the SharePoint list.
    Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _
        Array(stServer, LISTNAME, VIEWNAME), True, , Range("A1"))
    
    Set objMyList = Nothing
    Set objWksheet = Nothing
    
End Sub
The Following User Says Thank You to yukorin For This Useful Post:
Ragnaros (December 10th, 2012)
 
Old August 16th, 2010, 03:19 PM
Registered User
 
Join Date: Aug 2010
Posts: 4
Thanks: 0
Thanked 3 Times in 2 Posts
Default

I have found that parameter name for server was spelled wrong in the sample. The corrected code worked fine. Thank you.

Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _
Array(strSPServer, LISTNAME, VIEWNAME), True, , Range("A1"))
The Following 2 Users Say Thank You to yukorin For This Useful Post:
Ragnaros (December 10th, 2012)
 
Old December 10th, 2012, 03:17 PM
Registered User
 
Join Date: Dec 2012
Posts: 1
Thanks: 2
Thanked 0 Times in 0 Posts
Default

I am trying to do the same thing that you're doing but I am getting an error on the "Set objMyList = ..." line. Any idea why?

Code:
Sub ImportSharePointList()

    Dim objMyList As ListObject
    Dim objWksheet As Worksheet
    Dim strSPServer As String
    
    Const SERVER As String = "REMOVED.com/REMOVED/REMOVEDEscalations/Lists/Escalations%20to%20the%20Desk/AllItems.aspx"
    Const LISTNAME As String = "Escalation Team issues"
    Const VIEWGUID As String = "All Items"
    
    strSPServer = "https://" & SERVER & "/_vti_bin"
    
    Set objWksheet = Worksheets.Add

    Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, Array(stSPServer, LISTNAME, VIEWGUID), True, , Range("A1"))
    
    Set objMyList = Nothing
    Set objWksheet = Nothing
    
End Sub
Edit: I only have Read access to this Sharepoint site. Could that have something to do with it?

Last edited by Ragnaros; December 10th, 2012 at 03:32 PM..





Similar Threads
Thread Thread Starter Forum Replies Last Post
Run-time error '1004' with Named cells mahaneca Beginning VB 6 1 January 29th, 2009 01:04 AM
Run time error 1004 Ken Matthews Excel VBA 1 March 2nd, 2007 06:30 AM
Run-time error '1004' Document not saved Tucky Excel VBA 0 October 28th, 2004 12:04 AM
Run-time error '1004': raja.gogoi VB Components 1 July 18th, 2004 11:02 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.