open all excel files in a sharepoint folder

[Ish23]

Hi All,

I need to open all the excel files in a sharepoint folder using excel VBA code.

i.e.., the excel file that i have will be the Master file .
So , the Master file should open all the excel files in a sharepoint folder and copies and paste the values into the Master file and should close the excel files in Sharepoint.


I tried using workbooks.CanCheckOut(xlFile).

For this i have to give the exact file name.

But what i need is to open automatically all the excel files in a sharepoint folder.

All help is highly appreciated.

Its very urgent.

Thanks and Regards,
Ish.

[June2010]

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.

[Ish23]

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.

[royUK]

Ish23

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[Ish23]

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

End sub

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.

[Nikhil_Meni]

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.

[FDibbins]

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.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

[mscurtin67]

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

[arlu1201]

mscurtin67,

This thread is 2 yrs old and the original poster is not likely to come back and respond. Its better if you post a new thread for your question. You will get better visibility and faster replies.

[faraha91]

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