Copy Excel workbook from a sharepoint site

Posted on 2011-04-22
Medium Priority
Last Modified: 2012-05-11
I am trying to copy the used range from a Corporate network sharepoint 2003 list excel workbook and getting a "subscript out of range" error. I know the address is good as I can go there directly. Any Help would be appreciated
Sub CSMCopy()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws As Worksheet
Set wb1 = Workbooks("http://sharepoint.fmr.com/sites/CSMBusinessManagementTeam/default.aspx?RootFolder=%2fsites%2fCSMBusinessManagementTeam%2fShared%20Documents%2fPlanning%20and%20Performance%2fSegment%20Assignments&View=%7bEB37DB38%2dBFFF%2d41B3%2dAF97%2dBBACDD1BB0F3%7d/All_Plan_Assignments-All_Segments.xls")
Application.DisplayAlerts = False
Set wb2 = Workbooks("\\dmn1.fmr.com\FIDFILE\FS093\workgrp\COMMON\Testing and Reporting Services\Help Line\CallAnswer-TC_SDM\Resources\All Plan Assignments - All Segments.xls")
wb1.UsedRange.Copy wb2.Cells

Application.DisplayAlerts = True
End Sub

Open in new window

Question by:llawrenceg
1 Comment

Accepted Solution

jason987 earned 2000 total points
ID: 35449447
I don't think you can use the workbooks object with URLs

Try this instead:

Workbooks.Open Filename:="http://sharepoint.fmr.com/sites/CSMBusinessManagementTeam/default.aspx?RootFolder=%2fsites%2fCSMBusinessManagementTeam%2fShared%20Documents%2fPlanning%20and%20Performance%2fSegment%20Assignments&View=%7bEB37DB38%2dBFFF%2d41B3%2dAF97%2dBBACDD1BB0F3%7d/All_Plan_Assignments-All_Segments.xls"

Then you can refer to the workbooks by name:


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you create your solutions on SharePoint sooner or later you will come upon a request to set  permissions of the item depending on some of the item's meta-data - the author, people assigned as approvers, divisions, categories etc. The most natu…
Pimping Sharepoint 2007 without Server-Side Code Part 1 One of my biggest frustrations with Sharepoint 2007 in the corporate world is that while good-intentioned managers lock down the more interesting capabilities of Sharepoint programming in…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question