VBA code in Excel to copy Source file from SharePoint to another destination

Margie Burgett

New Member
Joined
May 23, 2008
Messages
16
Hi everyone,

Background: I have a CSV datasheet that is used in an Excel Pivot table workbook.

Each week the datasheet file is refreshed and posted on a SharePoint 2007 Shared Document folder.

Question: I would like to create a VBA macro in the excel workbook to allow users to download the newest version of the datasheet from SharePoint to their hard drive prior to refreshing their workbooks.

Issue: I am a newbie to this and having difficulty in finding the correct syntax in VBA to accomplish this. MS Excel help has been used and multiple possibilities tried but I have getting stuck on the SharePoint network address.

Any thoughts?

Simple summary:
1. Create a macro in an excel workbook to copy a CSV data source from a SharePoint folder to the users hard drive.
2. SOURCE: SharePoint folder network address (ex. '\\sharepoint.xxx.com\teams\Dept\Shared Documents\Reports\Pivot_Source_Data\) causing failure when trying to copy xxxxxxxx.CSV file.
3. TARGET: C:\Reports\Pivot_Source_Data\xxxxxxxx.CSV

Thank you.

BTW, I do have the book Excel 2007 by John Walkenback but is there another beginner to intermediate book that may provide actual VBA (highly used) examples? Any recommendations are greatly appreciated
 
Last edited:
Does anyone know how to answer this question?

Hello Mav55th,

The code s brilliant. But in my case I have another challenge that I need to copy multiple files from multiple folders of source path(https:) and save in the designated local drive/folder with same name and format(it will be .xlsm). Thank you in advance.
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Does anyone know how to answer selvendran's question?

Hello Mav55th,

The code s brilliant. But in my case I have another challenge that I need to copy multiple files from multiple folders of source path(https:) and save in the designated local drive/folder with same name and format(it will be .xlsm). Thank you in advance.


quote_icon.png
Originally Posted by Mav55th
Try this:

Option Explicit

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


Sub DownloadFileFromWeb()
Dim i As Integer

Const strUrl As String = "http://teams/Dept/Shared Documents/Reports/Pivot_Source_Data/filename.csv"
Dim strSavePath As String
Dim returnValue As Long

strSavePath = "C:\Reports\Pivot_Source_Data\xxxxxxxx.CSV"
returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)

End Sub



 
Upvote 0
Hello Mav55th,

The code s brilliant. But in my case I have another challenge that I need to copy multiple files from multiple folders of source path(https:) and save in the designated local drive/folder with same name and format(it will be .xlsm). Thank you in advance.

Hi Mav55th,

Thank you for below code. It did the trick. However I wanted the conditions to be used if the file doesn't exist in the share point the msg box prompting, and if there file already exist in the destination path msgbox should prompt stating do you want rewrite since file is already exist with Yes or no buttons. This would be more helpful.

Thank you for all your help.

Regards,
Savan

Option Explicit

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


Sub DownloadFileFromWeb()
Dim i As Integer

Const strUrl As String = "http://teams/Dept/Shared Documents/Reports/Pivot_Source_Data/filename.csv"
Dim strSavePath As String
Dim returnValue As Long

strSavePath = "C:\Reports\Pivot_Source_Data\xxxxxxxx.CSV"
returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)

End Sub
 
Upvote 0
Hello,

Can anybody help me in using if conditions to sharepoint in the above code.
If specified file does not exist in sharepoint then Msg box should prompt not available
And If the same file already exist in the local specified path then it should prompt msg stating do you want to overwrite the file already exist in the path.
Else it should save in the local given path.

Your help in this regard is greatly appreciated.

Thank you in advance.
Savan
 
Upvote 0
Hi there,

I know this is a bit of an old post, but seems I may have stumbled on someone who knows both VBA and Sharepoint. Hopefully :)

I am seeking some code that will open a sharepoint file, and obtain the following information.
a) a count of how many attachments are in that file; and then
b) the name of each attachment so that I can save each attachment to a local folder (the code above would do that, provided I have the file name)

I have no understanding of nodes, xPath.

Any help would be much appreciated.

Regards
 
Upvote 0
Hello

I know this is an old thread but hopefully someone can shed some light on this for me, I've been useing this code to successfuly download a file from sharepoint, until I sent the macro to a coworker, he's unable to download the file but the same macro in my pc works just fine, any ideas on why is this happening?
 
Upvote 0
... I've been using this code to successfully download a file from sharepoint, until I sent the macro to a coworker, he's unable to download the file but the same macro in my pc works just fine, any ideas on why is this happening?
Can be various reasons, like these:
1. There is no destination folder "C:\Reports\Pivot_Source_Data" on coworkers' PC. Check it manually or better by the code.
2. Your coworker has no access rights to read that file. Ask coworker to download file manually to be sure it's possible.
3. Antivirus software (like McAfee) blocks URLDownloadToFile API function. Add EXCEL.EXE to the exception list of antivirus, but be sure that all using macros are verified.
4. Your coworker uses 64-bit version of Excel. Try the below code to work with any version of Excel:
Rich (BB 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
 
Sub MyMacro()
 
  Const strUrl As String = "http://teams/Dept/Shared Documents/Reports/Pivot_Source_Data/filename.csv"
  Const strSavePath As String = "C:\Reports\Pivot_Source_Data\xxxxxxxx.CSV"
 
  If Dir(Left(strSavePath, InStrRev(strSavePath, "\")), vbDirectory) = "" Then
    MsgBox "Destination folder is not found for the file:" & vbLf & strSavePath, vbCritical, "Error"
    Exit Sub
  End If
 
  If DownLoadFile(strUrl, strSavePath) Then
    MsgBox "File from:" & vbLf & strUrl & vbLf & "is saved to:" & vbLf & strSavePath
  Else
    MsgBox "Can't download file:" & vbLf & strUrl, vbCritical, "Error"
  End If
 
End Sub
 
Function DownLoadFile(Url As String, SavePathName As String) As Boolean
    DownLoadFile = URLDownloadToFile(0, Replace(Url, "\", "/"), SavePathName, 0, 0) = 0
End Function
 
'Sub DownloadFileFromWeb()
'
'  Const strUrl As String = "http://teams/Dept/Shared Documents/Reports/Pivot_Source_Data/filename.csv"
'
'  Dim strSavePath As String
'  Dim returnValue As Variant 'LongLong/Long
'
'  strSavePath = "C:\Reports\Pivot_Source_Data\xxxxxxxx.CSV"
'  returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)
'
'End Sub
 
Upvote 0
thanks ZVI and sorry for the late response, I work in a rather large corporation so all systems, settings, antivirus, firewalls are the same, i thought it was a permission issue but my coworker was able to download the file via web browser, in the end I compared the code in your last post with the one I have and I added PtrSafe in the Declare and = 0 to the download function and it is working now, although I don't know what that = 0 is for
Code:
[COLOR=#333333]DownLoadFile = URLDownloadToFile(0, Replace(Url, "\", "/"), SavePathName, 0, 0) [B]= 0[/B][/COLOR]
 
Upvote 0
Hi Maycol, happy to know the problem is solved!

This line: DownLoadFile = URLDownloadToFile(...) = 0
is the same as DownLoadFile = ( URLDownloadToFile(...) = 0 )
where URLDownloadToFile(...) = 0 is the logical expression.

In case of successful downloading the API function URLDownloadToFile returns zero value, and that logical expression is equal to True. Else it is equal to False.
Thus DownLoadFile = True only on successful downloading.

Best Regards
 
Last edited:
Upvote 0
Try this:

Option Explicit

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


Sub DownloadFileFromWeb()
Dim i As Integer

****Const strUrl As String = "http://teams/Dept/Shared Documents/Reports/Pivot_Source_Data/filename.csv"
****Dim strSavePath As String
****Dim returnValue As Long
****
****strSavePath = "C:\Reports\Pivot_Source_Data\xxxxxxxx.CSV"
****returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)
****
End Sub

I was having problems with some code I had been using previously using (quite successfully, I might add) that used FileCopy to copy files from SharePoint to a location on our shared drive. I think the issue occurred when we migrated to SharePoint 2013. I tried several different options before attempting your code, but once I did, it worked like a charm. Thank you very much for sharing!
 
  • Like
Reactions: ZVI
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top