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:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Try this:

VBA Code:
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
 
Last edited by a moderator:
Upvote 0
Hello Mav55th,

You are wonderful! The code worked perfectly.

There is no way that I would have even scratched the surface on figuring this out.

Do you have any recommendations for beginner to intermediate VBA books that I should purchase?

Once again, thank you.

Margie Burgett
 
Upvote 0
Actually, most of the stuff I've accumulated has been from this site. I do have a couple of Bill's books that I reference often, mostly "VBA and Macros for Microsoft Excel".
 
Upvote 0
Thank you.

Well I am just starting on this learning curve so I have a very long way to go. Many years to go.

I appreciate you taking the time for assisting me.

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

LOL, first time around the message took so long, I timed out...

OK, so downloading with this very helpful piece of code worked really well and pulled documents from 4000 document libraries just fine. PDFs are all OK, but for reasons I do not understand, the word and mail messages are all hosed up with an encoding issue. Now when they came down the file names were URL encoded and I can fix that (yeah VBS), but I cannot get past "Select the encoding that makes your document readable. Text Encoding: Windows (Default) MS-DOS Other encoding" with a window that strangely enogh has "%PDF-1.3" despite being a Word 2003 document in a SharePoint 2003 document library.

What I need to know is how to fix these, and hopefully some BA code that will push them up to a different library.

Any assistance would be most appreciated...
 
Upvote 0
LOL, first time around the message took so long, I timed out...

OK, so downloading with this very helpful piece of code worked really well and pulled documents from 4000 document libraries just fine. PDFs are all OK, but for reasons I do not understand, the word and mail messages are all hosed up with an encoding issue. Now when they came down the file names were URL encoded and I can fix that (yeah VBS), but I cannot get past "Select the encoding that makes your document readable. Text Encoding: Windows (Default) MS-DOS Other encoding" with a window that strangely enogh has "%PDF-1.3" despite being a Word 2003 document in a SharePoint 2003 document library.

What I need to know is how to fix these, and hopefully some BA code that will push them up to a different library.

Any assistance would be most appreciated...

I Don't know if you're still having this issue and I'm not sure I have a definite solution for you but I'll tell you what I did in one case. I had an Excel add-in file I had to distribute from Sharepoint that was getting hosed when downloading. So I renamed it from somename.xla to somename.txt on Sharepoint. Then my distribution process downloaded it with no problem and followed up by renaming it back to somename.xla locally on disk. It was ugly but it worked.

Your case sounds more complicated but the idea of fooling Sharepoint with file extensions might work never-the-less.
 
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.

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

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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