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

[Margie Burgett]

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

 

[MichalHabani]

Hi,

this is great, but i was wandering how it will work with an xlsx file.
I've tried to change the csv in the code to xlsx, the file has downloaded successfully but cannot be opened, i'm getting an error: Excel cannot open the file because the file format or file extension is not valid.
appreciate all the help!

Thanks!
Michal

 

[Negi1984]

Hi Zvi,

Hi,

I've tried to change the csv in the code to xlsx, the file has downloaded successfully but cannot be opened, i'm getting an error: Excel cannot open the file because the file format or file extension is not valid.
Could you please assist.

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:

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

 

[ssingh75]

Hi Zvi,

Hi,

I've tried to change the csv in the code to xlsx, the file has downloaded successfully but cannot be opened, i'm getting an error: Excel cannot open the file because the file format or file extension is not valid.
Could you please assist.

I've tried the below code but it doesnt work for me....however i'm looking to upload any type of files into the sharepoint...but no luck yet.


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 PtrSafe 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 = "https://abc365.sharepoint.com/sites/CommunicationDev/test_tree/acd/test/Test.csv"
Const strSavePath As String = "D:\main\Test\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

 

[nvh6]

Try this:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> URLDownloadToFile <SPAN style="color:#00007F">Lib</SPAN> "urlmon" Alias _<br> "URLDownloadToFileA" ( _<br> <SPAN style="color:#00007F">ByVal</SPAN> pCaller <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> szURL <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _<br> <SPAN style="color:#00007F">ByVal</SPAN> szFileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _<br> <SPAN style="color:#00007F">ByVal</SPAN> dwReserved <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br> <SPAN style="color:#00007F">ByVal</SPAN> lpfnCB <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br><br><SPAN style="color:#00007F">Sub</SPAN> DownloadFileFromWeb()<br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br> <SPAN style="color:#00007F">Const</SPAN> strUrl <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "http://teams/Dept/Shared Documents/Reports/Pivot_Source_Data/filename.csv"<br> <SPAN style="color:#00007F">Dim</SPAN> strSavePath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> returnValue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br> <br> strSavePath = "C:\Reports\Pivot_Source_Data\xxxxxxxx.CSV"<br> returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)<br> <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

hi Mav55th, thank you for the code. I am a newbie so I don't understand what your code is ). It looks like HTML more than VBA to me.

 

[Fluff]

Have another look.