FileCopy command - run time error 52 in Excel VBA

[FrustratedTonya]

I'm trying to copy a file from a SharePoint location to a network folder. I can get the file to Open and save as but all I really need to do is copy it.

Code looks like this:

strPathSrc = "SharepointLoc/My%20Reports/"
strPathTgt = "P:\MyReports\"
 
strFileSrc = "This%20File.xlsx"
strFileTgt = "This File.xlsx"
 
Filecopy strPathSrc & strFileSrc, strPathTgt & strFileTgt

On run, I'm getting the error 52 but again, if I run the code below, I'm good, so I think that the paths and file names are ok.

Workbooks.Open Filename:=strSrcFull
 
Workbooks(strFileTgt).SaveAs strTgtFull
 
Workbooks(strFileTgt).Close

Any help would be so appreciated!

[Rabbit]

Opening a file from within Excel uses a different mechanism than using the file system to copy a file. While Excel knows how to connect to SharePoint, the file system itself does not.

[FrustratedTonya]

Ahhh... so I'm not going to be able to use the Filecopy command here and will have to open and save as instead. Thank you!

[zmbd]

Last year I had a very similar problem
I ran across this in another forum.

Option Compare Database
Option Explicit
 
'Thank you Tragamor for the following code:
 
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
 
Function DownloadFileFromWeb(strURL As String, strSavePath As String) As Long
    ' strSavePath includes filename
    DownloadFileFromWeb = URLDownloadToFile(0, strURL, strSavePath, 0, 0)
End Function 

the "strSavePath" is the fully qualified file-path and file-name you are saving the document to; thus, if your network drive is mapped to "Z:" then you might have something like:
strSavePath = "Z:\directory\YourNewFileName.xtn"

(...)
 strPathSrc = "SharepointLoc/My%20Reports/"
 strPathTgt = "P:\MyReports\"
 
 strFileSrc = "This%20File.xlsx"
 strFileTgt = "This File.xlsx"
 
 strFullSrc = strPathSrc & stFileSrc
 strFullTgt = strPathTgt & strFileTgt 
 
 lngSuccess = DownloadFileFromWeb(strFullSrc, strFullTgt )
(...)

function returns a zero if there are no errors

BTW: Although Allowed with the newer OS, IMHO, file names should only contain alphanumeric and the underscore, no spaces nor special characters.
Smithsonian Data Management Best Practices
(While this suggests the hyphen, I personally avoid it too as it can be interpreted as a negative or subtraction function)

[twinnyfo]

All y'all,

Do all y'all realize that the web address for SharePoint is different than the File Structure address for SharePoint?

If I try to save something to my SharePoint web address: "https://SharePoint.Web.Address/org/A1/A1K/A1KM/A1KMP/", I will get an error.

However, if I try to save a file to my SharePoint File Structure address: "\\SharePoint.Web.Address@SSL\DavWWWRoot\org\A1\A1 K\A1KM\A1KMP\", I am able to connect. You can even add this address to your Windows File Manager as a Shortcut.

The only restriction is that you must connect to that location and make sure your file system can find that folder before you can automate it through VBA.

I use this method all the time without hitch or stitch.

Hope this hepps!

[NeoPa]

TwinnyFo:
You can even add this address to your Windows File Manager as a Shortcut.

I assume you mean "... as a drive letter."?

Very helpful info Twinny :-)

[twinnyfo]

Yes, you could assign it a drive letter, but I prefer not to and use the location explicitly in any VBA, just in case others might assign different drive letters. I can also drag folders into my Quick Access list.

Incredibly convenient once I learned how to do it.

[NeoPa]

I wasn't suggesting an alternative option my friend. I was translating your comment so that it made sense to me. I otherwise have no understanding of what you mean by "You can even add this address to your Windows File Manager as a Shortcut.".

As you comment, I also prefer to use UNC notation when referring to network addresses from within software. It's more robust.

[twinnyfo]

No offense taken, my friend--I was just not describing a drive letter. Since MS keeps changing what everthing is called, I sometimes confuse myself.

Hence, I still cling to the old moniker "File Manager" which sometimes slips out of my fingertips when what I really mean is "Windows Explorer".

And "shortcut" slips out, because it is easier to say and remember than "Quick Access List".

So, your confusion is justified and understandable.

Next week MS plans on renaming "Windows Explorer" to the "File Operations and Organization Listing Integrating the System Host" (FOOLISH). And the "Quick Access List" will be renamed "Method for Easily Adding Temporary Hotlinks Everyone can Access Directly" (MEATHEAD).

I'm sure you would agree that this would be a great improvement!

[NeoPa]

I'm not sure I even understand what a "Quick Access List" is in Windows Explorer, but I can see you weren't trying to say "Drive" so I'll leave it at that ;-)

I think we both agree a drive wouldn't be the best way to do it in software, but beyond that we have no real need to go :-)

PS. Nice acronyms :-D

[twinnyfo]

Ahhh.....

So, let me show you (top left):

[NeoPa]

Ahhh (Right back atcha!)
On my Windows 7 system that would be equivalent to Favourites I suspect (although spelt wrong of course ;-)).

[zmbd]

NeoPa - Tisk Tisk :)
Yes it's the favorites list - or you can add the link into the Libraries directory.

TwinneyFo,
At my work, the UNC link to the sharepoint server is apparently blocked either by group-policy or by the active directory server; however, the method I gave earlier works.

[twinnyfo]

Z - Ain’t it grand how there are so many ways to skin the same cat that something is bound to work sooner or later. Thanks for giving me an alternate method for when they lock down our servers even more!