PDA

View Full Version : Adjusting MyPath Variant to point to specific folder



KCTiger
01-24-2013, 08:12 AM
I have the below code that I'm needing to adjust to point to a specifice file folder (within Sharepoint) I'm getting a runtime error 53 when I attempt to update MyPath to a specifice folder (bolded below):



Dim MyFile, MyPath, MyName, MyPD, PDFile, SummaryFile, _
ModelFile

Sub DirList()
' Display the names that represent directories.
Application.ScreenUpdating = False
Worksheets("PD Directory List").Select
Cells.Select
Selection.ClearContents
Range("A1").Select
MyPath = ActiveWorkbook.Path
MyPath = MyPath & "\"
Range("A2").Select
MyName = Dir(MyPath, vbDirectory) ' Retrieve the first entry.
Do While MyName <> "" ' Start the loop.
'Ignore the current directory and the encompassing directory.
If MyName <> "." And MyName <> ".." Then
' Use bitwise comparison to make sure MyName is a directory.
If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
Debug.Print MyName ' Display entry only if it
MyPD = MyPath & MyName & "\"
Selection.Value = MyPD
ActiveCell.Offset(1, 0).Select
End If ' it represents a directory.
End If
MyName = Dir ' Get next entry.
Loop
Range("A1").Select
FileList
PDImport
Range("A1").Select
Worksheets("PD Totals").Select
Range("A1").Select
Worksheets("Project Totals").Select
Range("A1").Select
End
End Sub

Sub FileList()
Worksheets("PD File List").Select
Cells.Select
Selection.ClearContents
Range("A1").Select
Do While MyPD <> ""
Worksheets("PD Directory List").Select
ActiveCell.Offset(1, 0).Select
MyPD = ActiveCell.Value
If MyPD = "" Then
Exit Do
End If
MyFile = Dir(MyPD & "*.XLS")
Do While MyFile <> ""
Worksheets("PD File List").Select
ActiveCell.Offset(1, 0).Select
Selection.Value = (MyPD & MyFile)
MyFile = Dir
If MyFile = "" Then
Exit Do
End If
Loop
Loop
Worksheets("PD Directory List").Select
Range("A1").Select
Worksheets("PD File List").Select
Range("A2").Select
PDFile = ActiveCell.Value
End Sub


Thanks for any help in advance!

Teeroy
01-24-2013, 09:09 PM
I've read that DIR has issues with SharePoint (since it's not a windows file structure) but can't remember where. Have a look here (http://stackoverflow.com/questions/1344910/get-the-content-of-a-sharepoint-folder-with-excel-vba) for some ideas how to make a workable link.