open all excel files in a sharepoint folder

Hi,

I am trying to implement similar solution for my problem.Getting data from a workbook which is on my share point to an in memory data structure(array of my custom class)

Can you please provide me the code which you have used yourself to get data from sharepoint.

Thanks.

Hi,

If you have list of *.xls files in sharepoint folder you can simply specify the path as below.

'this code will be in a command button click event

Private Sub Commandbutton1_click()
Dim xlFile as string,xlFullFile as string
Dim xlApp As Excel.Application
Dim wb As Workbook


xlFile = "\\**.**.**********.com\**\*************\**** ******** ****\**** ****"

****----denotes the path.(i.e) u give the path as windows search.Don't use "\" at the end.
In the sharepoint path %20 denotes space.so u remove that and use space .

Set xlApp = New Excel.Application
xlApp.Visible = True

xlFullFile = GetFullFileName(xlFile, "ANZ") 'ANZ denotes starting characters of the file.
xlFile = xlFile & "\" & xlFullFile
Set wb = xlApp.Workbooks.Open(xlFile, , False)

'Once the workbook is opened u can do ur code here

wb.close false





Function GetFullFileName(strfilepath As String, _
strFileNamePartial As String) As String

Dim objFS As Variant
Dim objFolder As Variant
Dim objFile As Variant
Dim intLengthOfPartialName As Integer
Dim strfilenamefull As String

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strfilepath)

'work out how long the partial file name is
intLengthOfPartialName = Len(strFileNamePartial)

For Each objFile In objFolder.Files 'Instead of specifying the starting characters of the file you can
directly loop through all files in the folder .


'Test to see if the file matches the partial file name
If Left(objFile.Name, intLengthOfPartialName) = strFileNamePartial Then

'get the full file name
strfilenamefull = objFile.Name
Exit For

Else

End If

Next objFile

Set objFolder = Nothing
Set objFS = Nothing

'Return the full file name as the function's value
GetFullFileName = strfilenamefull


End Function



Hope this helps.

Hi,

If you have list of *.xls files in sharepoint folder you can simply specify the path as below.

'this code will be in a command button click event

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hope this helps.

Were you able to solve your issue?. I have a similar need. If you were able to please provide code.

I am trying to run in a macro in a SharePoint excel document that copies values in standardised timesheets and pastes them into a master file in SharePoint.

The code below works in my personal drive but not when I export the master file in SharePoint and run from within there.

Can someone please help with the code below please:

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
Dim Filepath As String
Filepath = "//hub.bcu.ac.uk/sites/rie/cdc-work-area/Timesheet Test RESTRICTED Access"
MyFile = Dir(Filepath)

Do While Len(MyFile) > 0
If MyFile = "Zmaster.xlsx" Then
Exit Sub
End If

Workbooks.Open (Filepath & MyFile)
Range("A59:AF59").Copy
ActiveWorkbook.Close


erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("sheet1").Range(Cells(erow, 1), Cells(erow, 35))

MyFile = Dir

Loop
Range("D1").Select
MsgBox "Process is Complete!"
End Sub

The output is Process is complete but I don't see any values in the Zmaster file

Thanks

Hi I am trying to loop through a sharepoint folder and perform some operation on the files present in that folder. I replaced the path with my sharepoint folder. it is saying path not found error. can you please help me, like how to connect to sharepoint folder in a loop.