• 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
 

Chihiro

Excel Ninja
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:

Chihiro

Excel Ninja
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

Chihiro

Excel Ninja
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
 

Chihiro

Excel Ninja
? "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
 

Chihiro

Excel Ninja
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.
 

Chihiro

Excel Ninja
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
 

M_Take

New Member
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
 

Chihiro

Excel Ninja
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).
 
Top