VBA doesn't like my file path

[Guest]

I have what should be a very simple code snippet that is supposed to copy a
file to the same location with a new filename. It gets the file path and
name from cells in a worksheet. For illustration, I want it to copy

C:\NAPI DD Files\DDVSMTAgedRwk????????.xls

to

C:\NAPI DD Files\ARWK.xls


When I run it, though, the code barfs on the line preceded by <<<<<<<,
telling me "Runtime Error 76 -- path not found." When I look at the two
filename variables via the immediate window they look exactly as they should,
the MessageBox I threw in for testing shows them exactly the way I want them,
and I know darn well the first file is exactly where it's supposed to be, so
what is Excel complaining about???


************************************************
The code:

Dim strTargetDDFile As String, strStandardDDFile As String, objFSO As
Object, x As Integer

Set objFSO = CreateObject("Scripting.FileSystemObject")

For x = 2 To 10

strTargetDDFile = Sheets("Lookups").Range("G" & x).Value
strStandardDDFile = Sheets("Lookups").Range("H" & x).Value

MsgBox (strTargetDDFile & " " & strStandardDDFile)

<<<<<<<<objFSO.CopyFile strTargetDDFile, strStandardDDFile, True

Next x

 

[Ron de Bruin]

You can use FileCopy

See
http://www.rondebruin.nl/folder.htm

 

[Guest]

Just try:

FileCopy strTargetDDFile, strStandardDDFile

Regards
reklamo

 

[gimme_this_gimme_that]

It looks to me like the issue has to do with spaces in the path.

You can workaround that using the following ways:

1. Create your own unescape VBA Function:
Function unescape(s As String)
s = Application.Substitute(s, "%20", " ")
s = Application.Substitute(s, "%3A", ":")
s = Application.Substitute(s, "%5C", "\")
unescape = s
End Function

2. Use UrlUnescape

http://msdn2.microsoft.com/en-us/library/ms628645.aspx

And also its partner UrlEscape.

 

[Guest]

OK, fine, and thanks to both you and Ron. That works fine. But just so I'll
be a better person, can you tell me why the .CopyFile approach fails?