• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA from SharePoint folder to local path folder excel

B

basu

New Member
Could you please help me to download excel files from SharePoint and do a consolidation of those excel files. There are sheet1 and sheet 2

Challenge are as follows
- SharePoint-main folder- many subfolders -inside there are excel
- in those excel there are two sheets sheet1 and sheet 2
- need to do consolidation of those two sheets sheet1 and sheet 2 respectively

It is one of high time consuming activity and I can see 200+ sub folders which I need to do consolidation daily manually


I know it is tough vba code required here could you please help me ( much appreciated for your help and support)

Thank you
 
basu
Can You use Marco Recorder while You're doing Your daily routines?
After that, You should write
- what have You done?
- what should You do?
 
vletm said:
basu
Can You use Marco Recorder while You're doing Your daily routines?
After that, You should write
- what have You done?
- what should You do?
Click to expand...
Am sorry share point to excel connection will not be useful through macro recorder and which is not a good solution. Simultaneously am trying and testing am not sure I may find answer
 
For some sharepoint sites, some have found this method to work. Assign the path to a virtual drive. You may need to remove the http part to make a UNC path as shown in the commented link. Obviously, change the url value to your path and the other parts.

I made this example to show how to copy a file.
Code: 
Sub Main()
  Dim fso As Object, sFile As String, dFile As String, url As String
  Dim ws As Object

  sFile = "d:\t\f.txt"
  dFile = "S:\ken.txt"
  'https://ss64.com/vb/mapnetworkdrive.html
  url = "\\sharepoint.com\YourLocation"

  Set fso = CreateObject("Scripting.FileSystemObject")
  Set ws = CreateObject("WScript.Shell")

  If Not fso.FileExists(sFile) Then GoTo ExitSub
  ws.Run "cmd /c subst S: " & """" & url & """", 0, True
  fso.CopyFile sFile, dFile

  ws.Run "cmd /c subst S: /D", 0, True
ExitSub:
  Set fso = Nothing
  Set ws = Nothing
End Sub
 
Kenneth Hobson said:
For some sharepoint sites, some have found this method to work. Assign the path to a virtual drive. You may need to remove the http part to make a UNC path as shown in the commented link. Obviously, change the url value to your path and the other parts.

I made this example to show how to copy a file.
Code: 
Sub Main()
  Dim fso As Object, sFile As String, dFile As String, url As String
  Dim ws As Object

  sFile = "d:\t\f.txt"
  dFile = "S:\ken.txt"
  'https://ss64.com/vb/mapnetworkdrive.html
  url = "\\sharepoint.com\YourLocation"

  Set fso = CreateObject("Scripting.FileSystemObject")
  Set ws = CreateObject("WScript.Shell")

  If Not fso.FileExists(sFile) Then GoTo ExitSub
  ws.Run "cmd /c subst S: " & """" & url & """", 0, True
  fso.CopyFile sFile, dFile

  ws.Run "cmd /c subst S: /D", 0, True
ExitSub:
  Set fso = Nothing
  Set ws = Nothing
End Sub
Click to expand...
Thank you for the information .... currently I found easier solution am testing as per my requirements initially I thought tough vba code required but here only logic is important
 
You must log in or register to reply here.
Back
Top