• 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.

Download file from sharepoint and save it in system folder

Hello All,

Can any one help me on the below query, i need VBA code for the below situation.

I need to save the file from sharepoint link to system folder and the file name will be as "Namewith Date" e.g ABCD28.05.2019.xlsb.

thanks in advance
Anantha
 
Depends on SharePoint set up. Is it Local Server or is it Cloud SharePoint (i.e. Online)?

Oh also... what version of SharePoint and is it open to anonymous public access or requires login?
 
Last edited:
Then you can simply use urlmon code (as long as you are running on Windows based machine).

Code:
Option Explicit

#If Win64 Then
  Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias _
          "URLDownloadToFileA" ( _
          ByVal pCaller As LongLong, _
          ByVal szURL As String, _
          ByVal szFileName As String, _
          ByVal dwReserved As LongLong, _
          ByVal lpfnCB As LongLong) As LongLong
#Else
  Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
          "URLDownloadToFileA" ( _
          ByVal pCaller As Long, _
          ByVal szURL As String, _
          ByVal szFileName As String, _
          ByVal dwReserved As Long, _
          ByVal lpfnCB As Long) As Long
#End If


Function DownloadFile(Url As String, SavePathName As String) As Boolean
    DownloadFile = URLDownloadToFile(0, Replace(Url, "\", "/"), SavePathName, 0, 0) = 0
End Function

You can then use it like...
Code:
Sub Demo()
Dim strUrl As String, strSavePath As String, strFile As String
strUrl = "https://company.sharepoint.com/personal/User_domain_com/Documents/file.xlsx" 'SharePoint Path for the file
strSavePath = "C:\Test\" 'Folder to save the file
strFile = "FileName" & Format(Date, "dd.mm.yyyy") & ".xlsx"

If DownloadFile(strUrl, strSavePath & strFile) Then
    MsgBox "File saved to: " & vbNewLine & strSavePath
Else
    MsgBox "Unable to downloaf file:" & vbNewLine & strFile & vbNewLine & "Check url string and that document is shared", vbCritical
End If
End Sub
 
Hello Chihiro,

Above code which you provided is showing error message as "Sub or Function not defined", attached screenshot for your reference.

And also i need changes on coding as per below

i need particular file from the sharepoint link to save in the system, file name format will be as "Filename24.05.2019" and daily it will change based on date.

Please advise me with the coding.

Regards,
Anantha
 

Attachments

  • error.JPG
    error.JPG
    28.4 KB · Views: 15
That error tells me you didn't put 1st code into the module. Copy BOTH codes found in post#4 into module.

As for the second part... below line show you how it's done. As I have no idea what your filename is... just replace "FileName" with your actual file name (either hard coded or using variable).
Code:
strFile = "FileName" & Format(Date, "dd.mm.yyyy") & ".xlsx"
 
Hello Chihiro,

Now it is working fine, but below code while saving file it is naming as per below code, but i want coding to download the file from the sharepoint which is naming in the format like this "xxxxxx29.05.2019.xlsx", because tomorrow i have to download the file which is with the name "xxxxxx30.05.2019.xlsx"

strFile = "FileName" & Format(Date, "dd.mm.yyyy") & ".xlsx"

is it possible to do

thanks and Regards,
Anantha
 
? "Format(Date, "dd.mm.yyyy") " does change date according to current date. So what exactly is your issue?
 
today i have to download the file "xxxxxx29.05.2019.xlsx" from the sharepoint, if i give name as it is then that file is getting save to my system.

When is use the below coding
strFile = "xxxxxx" & Format(Date, "dd.mm.yyyy") & ".xlsx" then the file is not getting download and save to my system.

Regards,
Anantha
 
Can you elaborate? Code auto generates text dd.mm.yyyy (ex: 29.05.2019) and concatenates with "xxxxxx" and ".xlsx".

If there is already file with same name present in the destination folder... it will overwrite the existing file.

As per your initial post, I assumed you only download the file once each day.

Or are you saying that the file name of the file you are downloading from SharePoint changes each day?
 
Yes Chihiro, Every day file name will change on the sharepoint, that too only date will change in the file name. e.g today filename is india29.05.2019 and tomorrow file name will be india30.05.2019.
 
So just use the function I used to change the file name to download.
Code:
strUrl = "https://company.sharepoint.com/personal/User_domain_com/Documents/filename" & Format(Date, "dd.mm.yyyy") & ".xlsx"
 
Getting error message as unable to download file, with using above code

in the file name we have this "(PeC_ELA_+_WW)", due to this it is getting error?
 
i am sorry Chihiro, i have given wrong date format in coding, now it is working fine.

Thank you so much for your valuable time and support.

Regards,
Anantha Krishna
 
Hello Chihiro,

Trying to find a way to download a csv file from SharePoint, I have found your code.
It worked and I can download a csv file successfully from SharePoint.

But when I opened it, it came with the following. I would guess the code is for downloading a binary file, as opposed to ASCII file.
=====Beginning of file =============
<!-- Copyright (C) Microsoft Corporation. All rights reserved. -->
<!DOCTYPE html>
<html dir="ltr" class="" lang="en">
<head>
<title>Sign in to your account</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=2.0, user-scalable=yes">
<meta http-equiv="Pragma" content="no-cache">
<meta http-equiv="Expires" content="-1">
<link rel="preconnect" href="https://aadcdn.msftauth.net" crossorigin>
<meta http-equiv="x-dns-prefetch-control" content="on">
......
......
followed by actual data.
=====End of file =============

Would you advice how I should modify your code so that a downloaded file is intact?

Best regards,
Mari
 
That code is for publicly accessible record. From your post. "<title>Sign in to your account</title> " it requires sign-in and authentication.

For that you need API access and/or pilot IE. Personally, I don't use VBA to manipulate SharePoint Online etc.

There are better alternatives (MS Flow, TypeScript, Python etc).
 
So I am able to download a .xlsx file but I was not able to open it. I tried opening it as a .csv and I noticed it was downloading the webpage text or script (not sure) instead of actually pulling the file. I have the file name correct though and I have tried at different share points at work.

Any help?
Thanks
 
acv14003
As a new member and just read Forum Rules:
Please, reread those,
especially How to get the Best Results at Chandoo.org -part
Isn't there written:

Start a new post every time you ask a question, even if the theme is similar.
 
Then you can simply use urlmon code (as long as you are running on Windows based machine).

Code:
Option Explicit

#If Win64 Then
  Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias _
          "URLDownloadToFileA" ( _
          ByVal pCaller As LongLong, _
          ByVal szURL As String, _
          ByVal szFileName As String, _
          ByVal dwReserved As LongLong, _
          ByVal lpfnCB As LongLong) As LongLong
#Else
  Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
          "URLDownloadToFileA" ( _
          ByVal pCaller As Long, _
          ByVal szURL As String, _
          ByVal szFileName As String, _
          ByVal dwReserved As Long, _
          ByVal lpfnCB As Long) As Long
#End If


Function DownloadFile(Url As String, SavePathName As String) As Boolean
    DownloadFile = URLDownloadToFile(0, Replace(Url, "\", "/"), SavePathName, 0, 0) = 0
End Function

You can then use it like...
Code:
Sub Demo()
Dim strUrl As String, strSavePath As String, strFile As String
strUrl = "https://company.sharepoint.com/personal/User_domain_com/Documents/file.xlsx" 'SharePoint Path for the file
strSavePath = "C:\Test\" 'Folder to save the file
strFile = "FileName" & Format(Date, "dd.mm.yyyy") & ".xlsx"

If DownloadFile(strUrl, strSavePath & strFile) Then
    MsgBox "File saved to: " & vbNewLine & strSavePath
Else
    MsgBox "Unable to downloaf file:" & vbNewLine & strFile & vbNewLine & "Check url string and that document is shared", vbCritical
End If
End Sub





Is the above code working for ppt file in Sharepoint because I am getting this error:

PowerPoint found a problem with content.
PowerPoint can attempt to repair the presentation.

but when I repair the file. It gives this error: Sorry, PowerPoint cannot read this file.

Does anyone face this issue?
 
Hello @Chihiro - Hope you are having a good day! The details you had shared have been quite helpful.

However I have a question - How would we change the below code such that I can save the files with the exact same filename(without making any changes in filename?

Code:
Option Explicit

#If Win64 Then
  Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias _
          "URLDownloadToFileA" ( _
          ByVal pCaller As LongLong, _
          ByVal szURL As String, _
          ByVal szFileName As String, _
          ByVal dwReserved As LongLong, _
          ByVal lpfnCB As LongLong) As LongLong
#Else
  Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
          "URLDownloadToFileA" ( _
          ByVal pCaller As Long, _
          ByVal szURL As String, _
          ByVal szFileName As String, _
          ByVal dwReserved As Long, _
          ByVal lpfnCB As Long) As Long
#End If


Function DownloadFile(Url As String, SavePathName As String) As Boolean
    DownloadFile = URLDownloadToFile(0, Replace(Url, "\", "/"), SavePathName, 0, 0) = 0
End Function

Sub Demo()
Dim strUrl As String, strSavePath As String, strFile As String
strUrl = "https://company.sharepoint.com/personal/User_domain_com/Documents/file.xlsx" 'SharePoint Path for the file
strSavePath = "C:\Test\" 'Folder to save the file
strFile = "FileName" & Format(Date, "dd.mm.yyyy") & ".xlsx"

If DownloadFile(strUrl, strSavePath & strFile) Then
    MsgBox "File saved to: " & vbNewLine & strSavePath
Else
    MsgBox "Unable to downloaf file:" & vbNewLine & strFile & vbNewLine & "Check url string and that document is shared", vbCritical
End If
End Sub
 
Hello @Chihiro - Hope you are having a good day! The details you had shared have been quite helpful.

However I have a question - How would we change the below code such that I can save the files with the exact same filename(without making any changes in filename?

Code:
Option Explicit

#If Win64 Then
  Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias _
          "URLDownloadToFileA" ( _
          ByVal pCaller As LongLong, _
          ByVal szURL As String, _
          ByVal szFileName As String, _
          ByVal dwReserved As LongLong, _
          ByVal lpfnCB As LongLong) As LongLong
#Else
  Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
          "URLDownloadToFileA" ( _
          ByVal pCaller As Long, _
          ByVal szURL As String, _
          ByVal szFileName As String, _
          ByVal dwReserved As Long, _
          ByVal lpfnCB As Long) As Long
#End If


Function DownloadFile(Url As String, SavePathName As String) As Boolean
    DownloadFile = URLDownloadToFile(0, Replace(Url, "\", "/"), SavePathName, 0, 0) = 0
End Function

Sub Demo()
Dim strUrl As String, strSavePath As String, strFile As String
strUrl = "https://company.sharepoint.com/personal/User_domain_com/Documents/file.xlsx" 'SharePoint Path for the file
strSavePath = "C:\Test\" 'Folder to save the file
strFile = "FileName" & Format(Date, "dd.mm.yyyy") & ".xlsx"

If DownloadFile(strUrl, strSavePath & strFile) Then
    MsgBox "File saved to: " & vbNewLine & strSavePath
Else
    MsgBox "Unable to downloaf file:" & vbNewLine & strFile & vbNewLine & "Check url string and that document is shared", vbCritical
End If
End Sub

I'm also looking for the same. hope Chihiro could help us.
derrishdev If you find any solution, kindly share here.
 
Hello @Chihiro - Hope you are having a good day! The details you had shared have been quite helpful.

However I have a question - How would we change the below code such that I can save the files with the exact same filename(without making any changes in filename?

Code:
Option Explicit

#If Win64 Then
  Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias _
          "URLDownloadToFileA" ( _
          ByVal pCaller As LongLong, _
          ByVal szURL As String, _
          ByVal szFileName As String, _
          ByVal dwReserved As LongLong, _
          ByVal lpfnCB As LongLong) As LongLong
#Else
  Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
          "URLDownloadToFileA" ( _
          ByVal pCaller As Long, _
          ByVal szURL As String, _
          ByVal szFileName As String, _
          ByVal dwReserved As Long, _
          ByVal lpfnCB As Long) As Long
#End If


Function DownloadFile(Url As String, SavePathName As String) As Boolean
    DownloadFile = URLDownloadToFile(0, Replace(Url, "\", "/"), SavePathName, 0, 0) = 0
End Function

Sub Demo()
Dim strUrl As String, strSavePath As String, strFile As String
strUrl = "https://company.sharepoint.com/personal/User_domain_com/Documents/file.xlsx" 'SharePoint Path for the file
strSavePath = "C:\Test\" 'Folder to save the file
strFile = "FileName" & Format(Date, "dd.mm.yyyy") & ".xlsx"

If DownloadFile(strUrl, strSavePath & strFile) Then
    MsgBox "File saved to: " & vbNewLine & strSavePath
Else
    MsgBox "Unable to downloaf file:" & vbNewLine & strFile & vbNewLine & "Check url string and that document is shared", vbCritical
End If
End Sub

Hi derrishdev,

I found a way. Use below code to extract the file name from URL and assign the value to the "strFile "
Assuming "strUrl" contains the file name + path eg: "https://company.sharepoint.com/personal/User_domain_com/Documents/file.xlsx"

strFile = GetFilenameFromPath(strUrl)

Function GetFilenameFromPath(ByVal strPath As String) As String
' Returns the rightmost characters of a string upto but not including the rightmost '/'


If Right$(strPath, 1) <> "/" And Len(strPath) > 0 Then
GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
End If
End Function

[B]Chihiro[/B] could you pleases check this code? Is there any better way to accomplish this?
Thank you!
 
Last edited:
Back
Top