PDA

View Full Version : run Time error '76 -looking for files within Sharepoint



KCTiger
05-22-2012, 01:16 PM
Can someone please advise on the below and why I'm getting a run-time error? i'm trying to open up folders/files within Sharepoint and copy/past data into a summary sheet. Any advice is appreciated.

Sub OH()
Dim objFSO As Object
Dim objFolder As Object
Dim objSubFolder As Object
Dim objFile As Object
Dim MyFolder As String
Dim wkbOpen As Workbook
Dim wkb As Workbook
Dim wks As Worksheet
Dim CalcMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

'Change the path accordingly
MyFolder = ttp://communities.bv.com/sites/WFP/Shared%20Documents/2012%202Q%20Forecast"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(MyFolder) 'getting error message here
Set wkb = ActiveWorkbook
Set wks = ActiveSheet

For Each objSubFolder In objFolder.SubFolders
For Each objFile In objSubFolder.Files
Set wkbOpen = Workbooks.Open(objFile.Path)
Sheets("Input Staff").Select
Range("C1").Select
Selection.Copy
Windows("OH Sharepoint macro.xlsm").Activate
Sheets("Summary").Select
If Range("B2") = "" Then
Range("B2").Select
ActiveSheet.Paste
End If
Range("B2").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

wkbOpen.Close savechanges:=True
Next objFile
Next objSubFolder

With Application
.Calculation = CalcMode
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Completed...", vbInformation

End Sub

Kenneth Hobs
05-22-2012, 02:11 PM
Welcome to the forum!

Should this line not be?
MyFolder = "http://communities.bv.com/sites/WFP/Shared%20Documents/2012%202Q%20Forecast"

KCTiger
05-22-2012, 02:13 PM
Yes.