473,322 Members | 1,566 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Filenames with Blanks Cause WSH FSO.CopyFile to Fail

2
I have an MS Access 2010 database with FrontEnd/BackEnds, which I have a clone script for. I have tested this thoroughly and it works perfectly but only where there are no spaces on the filename path. Once I relocate it to any LAN location with a blank in the pathname it wont work and I have tried Double Tabs, %20 and everything else I can find on the Net and nothing seems to work.

Pathname that fails is:
I:\EMDC Est-Areas\PNG_LNG Low\10.0 Procurement\10.2 Contracting\10.2.5 Contracts\10.2.5.2 ProcurementRegister\PRDBclone

Clone Script was taken from online source and is:

Expand|Select|Wrap|Line Numbers
  1. Set WshNetwork = CreateObject("WScript.Network")
  2. GetUser = WshNetwork.UserName 
  3. Set WshNetwork = Nothing
  4. LUName = "I:\EMDC Est-Areas\PNG_LNG Low\10.0 Procurement\10.2 Contracting\10.2.5 Contracts\10.2.5.2 ProcurementRegister\PRDBclone\PRDB_FE_TEST.accdb"
  5. oldname = LUName
  6. newName = Replace(LUName, ".accdb", "") & GetUser & ".accdb"
  7. retval = 0
  8. Dim objFSO
  9. Set objFSO = CreateObject("Scripting.FileSystemObject")
  10. retval = objFSO.CopyFile(oldname, newName, True) 
  11. Set objFSO = Nothing
  12. Dim objShell
  13. Set objShell = CreateObject("Wscript.Shell")
  14. objShell.run "MSAccess.exe " & newName
  15. Set objShell = Nothing
Any help would be greatly appreciated
Thanks in advance, Jacob
Jan 27 '13 #1
8 5553
TheSmileyCoder
2,322 Expert Mod 2GB
When something doesn't work its always a good idea to be specific about which part. Is it the copy or the opening that doesn't work? And is it given you errors, or something else?

I mean would ever take your car to the mechanic and just say "It doesn't work"? or would you try be as specific as possible?
Jan 27 '13 #2
NeoPa
32,556 Expert Mod 16PB
@Jacob
Although, in truth, you haven't made a bad stab at your first question, Smiley's comments are fair and backed by good reasoning. One of the benefits of using the mandatory [ CODE ] tags is that you can then refer to the line on which the error occurred. In this case I suspect it was line #10. For future questions please see Before Posting (VBA or SQL) Code.

As for your problem, your code is calling a library that is outside of standard Access. Common enough, so we can help, but not Access itself. Your line #10 is calling a method within the FileSystemObject object of the Windows Scripting Host.

From my reading of the CopyFile Method documentation, they haven't even commented on the situation where a file path contains embedded spaces (which is frankly poor). I tested using strings with double-quotes (") around them to see if that would work and it doesn't. From my reading of the situation, I'm afraid it simply doesn't support paths with embedded spaces. I suggest you use the inbuilt VBA statement Name As instead (This would be my default recommendation anyway - Why use WSH for something that comes as standard in the system already?).
Jan 27 '13 #3
TheSmileyCoder
2,322 Expert Mod 2GB
The fileSystemObject's copyfile does not have a return value. That might be what is giving you grief.

Note that access has a built in filecopy method. For example:
Expand|Select|Wrap|Line Numbers
  1.    Dim strSource As String
  2.    strSource = "C:\File Test For Copy.xlsx"
  3.    Dim strDest As String
  4.    strDest = "C:\File Test For Copy_1.xlsx"
  5.    FileCopy strSource, strDest
Note that you cannot specify whether or not to override the target. It will override the target location provided the target file is not open.
Jan 27 '13 #4
NeoPa
32,556 Expert Mod 16PB
Smiley:
The fileSystemObject's copyfile does not have a return value
The documentation doesn't specify that it's a subroutine, but it appears you're quite right in that it is. Not a function.

Once the calling syntax is fixed it seemed to work perfectly well. With or without embedded spaces, and on a local drive as well as on a networked one.

I'd still recommend using native code over one from a library that needs to be referenced, but Name As, as Smiley noticed, is a Rename command rather than one to copy files :-(
Jan 27 '13 #5
Stewart Ross
2,545 Expert Mod 2GB
Interesting - the code you show for copying the file is not quite as expected if it is the Scripting library that is being used. There is no CopyFile method in the Scripting library as shown in the Object Browser, although this method is referred to in ADezii's article (see below). There is a Copy method, which is used as follows:

Expand|Select|Wrap|Line Numbers
  1. Set fso = CreateObject("Scripting.FileSystemObject")
  2. Set f2 = fso.GetFile(strCopyFrom)
  3. f2.Copy (strCopyTo)
I have no problem running file copies which contain spaces in file names using the Copy method as shown. I suspect that the problem is that the GetFile call has been missed off the code used, leading to unpredictable results.

Our expert contributor ADezii wrote a series of Insight articles on the use of the Scripting run-time library. The one that particularly applies in this case is about copying and moving files, and is linked here for reference:

http://bytes.com/topic/access/insigh...ng-runtime-3-a

-Stewart
Jan 27 '13 #6
Cobb
2
WOW, thanks for all the fast responses. I thought it was something along those lines. The piece of code I am palgiarizing was posted on another Forum (as some of you probably may have recognized it).
Ok guys, I have to admit I am a Greenhorn, a complete VBA NOVICE who is trying to learn on the fly. Now comes the fun part, to see whether or not I understand your responses correctly... I do have my VBA bible at hand so here goes nuthin. Will report back with results.
I could ask for the Correct CODE from one of you but that defeats my LEARNING curve...if I do come back and ask then it has me Beaten or my Grey Matter is being dysfunctional.
Thanks again
Jacob
Jan 27 '13 #7
zmbd
5,501 Expert Mod 4TB
I echo Neopa's sentiment in that why load/refer to another library when the core VBA has the ability to do the job?

The FSO is truly a fascinating piece of work, until the WSH shell is disabled or set for Admin/System level execution only by your IT because they read some white-paper from one or more of the antivirus companies or the networking companies.

Now what do you do with all of those external file manipulations? To the rescue: Built in VBA External File Commands
Jan 28 '13 #8
TheSmileyCoder
2,322 Expert Mod 2GB
I could ask for the Correct CODE from one of you but that defeats my LEARNING curve
Thats the spirit! Wish more people would follow that path.
Jan 31 '13 #9

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

Similar topics

11
by: Marian Aldenhövel | last post by:
Hi, I am very new to Python and have run into the following problem. If I do something like dir = os.listdir(somepath) for d in dir: print d The program fails for filenames that contain...
7
by: Abby Lee | last post by:
I have back to back text boxes used to create university account numbers. I'm actually creating them with a script so they can create as many account numbers as they need. <edited and cut down...
0
by: Ron Wagner | last post by:
I've got a small VC++ program I'm working on that uses 5 property pages. On my development machine everything works fine. On a laptop that I sometimes use for development, clicking on 2 of the...
4
by: ad | last post by:
I am finding to copyfile in the online document. But I only find the namespace : Namespace: Microsoft.VisualBasic.FileIO How can I copyfile in c#?
11
by: brother52 | last post by:
I am using My.Computer.FileSystem.CopyFile() to copy a bunch of files from one directoy to another, and would like to display a progress bar on the screen for each file being copied. Is this...
34
by: Registered User | last post by:
Hi experts, I'm trying to write a program that replaces two or more consecutive blanks in a string by a single blank. Here's what I did: #include <stdio.h> #include <string.h> #define MAX 80
1
by: Hugo Ferreira | last post by:
Hi there, I have a problem. I'm using calling shutil.copyfile() followed by open(). The thing is that most of the times open() is called before the actual file is copied. I don't have this...
1
by: twocansam1 | last post by:
I am writing a routine to check for bad or no input, How can I check for blanks or spaces and negative numers?? negative numbers and spaces just cause the program to lock up. They don't get...
10
by: Robert Dailey | last post by:
Hi, I'm trying to create a Python equivalent of the C++ "ifstream" class, with slight behavior changes. Basically, I want to have a "filestream" object that will allow you to overload the...
34
by: niranjan.singh | last post by:
This is regarding to test an SDK memory stuff. In what situation malloc gets fail. any comment/reply pls.... regards
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.