By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,345 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

FileCopy command - run time error 52 in Excel VBA

P: 2
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:
Expand|Select|Wrap|Line Numbers
  1. strPathSrc = "SharepointLoc/My%20Reports/"
  2. strPathTgt = "P:\MyReports\"
  3.  
  4. strFileSrc = "This%20File.xlsx"
  5. strFileTgt = "This File.xlsx"
  6.  
  7. 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.

Expand|Select|Wrap|Line Numbers
  1. Workbooks.Open Filename:=strSrcFull
  2.  
  3. Workbooks(strFileTgt).SaveAs strTgtFull
  4.  
  5. Workbooks(strFileTgt).Close
Any help would be so appreciated!
Aug 21 '18 #1
Share this Question
Share on Google+
13 Replies


Rabbit
Expert Mod 10K+
P: 12,315
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.
Aug 21 '18 #2

P: 2
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!
Aug 21 '18 #3

zmbd
Expert Mod 5K+
P: 5,287
Last year I had a very similar problem
I ran across this in another forum.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. 'Thank you Tragamor for the following code:
  5.  
  6. Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
  7.     ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
  8.  
  9. Function DownloadFileFromWeb(strURL As String, strSavePath As String) As Long
  10.     ' strSavePath includes filename
  11.     DownloadFileFromWeb = URLDownloadToFile(0, strURL, strSavePath, 0, 0)
  12. 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"

Expand|Select|Wrap|Line Numbers
  1. (...)
  2.  strPathSrc = "SharepointLoc/My%20Reports/"
  3.  strPathTgt = "P:\MyReports\"
  4.  
  5.  strFileSrc = "This%20File.xlsx"
  6.  strFileTgt = "This File.xlsx"
  7.  
  8.  strFullSrc = strPathSrc & stFileSrc
  9.  strFullTgt = strPathTgt & strFileTgt 
  10.  
  11.  lngSuccess = DownloadFileFromWeb(strFullSrc, strFullTgt )
  12. (...)
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)
Aug 21 '18 #4

twinnyfo
Expert Mod 2.5K+
P: 3,055
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!
Aug 22 '18 #5

NeoPa
Expert Mod 15k+
P: 31,186
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 :-)
Aug 22 '18 #6

twinnyfo
Expert Mod 2.5K+
P: 3,055
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.
Aug 22 '18 #7

NeoPa
Expert Mod 15k+
P: 31,186
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.
Aug 23 '18 #8

twinnyfo
Expert Mod 2.5K+
P: 3,055
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!
Aug 23 '18 #9

NeoPa
Expert Mod 15k+
P: 31,186
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
Aug 23 '18 #10

twinnyfo
Expert Mod 2.5K+
P: 3,055
Ahhh.....

So, let me show you (top left):

Attached Images
File Type: jpg QuickAccess.jpg (66.1 KB, 478 views)
Aug 23 '18 #11

NeoPa
Expert Mod 15k+
P: 31,186
Ahhh (Right back atcha!)
On my Windows 7 system that would be equivalent to Favourites I suspect (although spelt wrong of course ;-)).
Aug 23 '18 #12

zmbd
Expert Mod 5K+
P: 5,287
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.
Aug 23 '18 #13

twinnyfo
Expert Mod 2.5K+
P: 3,055
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!
Aug 23 '18 #14

Post your reply

Sign in to post your reply or Sign up for a free account.