Shell functon and cmd.exe window

[Paul Sansom]

Help........
I wrote this sub to copy a file from my local drive to networked SharePoint drive with a renamed file from within Excel using cmd.exe window.
If i use the text code in the code lines shown in Red directly into the cmd window the file is copied.
When i run this code I get a message "The network name cannot be found"
What is the difference between running this from my code an directly in cmd window????

Any help or guidance most welcome
FYI The code runs without errors but the file is not copied

Of course the secondary question is....
Is there a better way to copy/paste a renamed file from a selectable folder into a specific SP folder. (I asked this already without replies so I'm guessing not)

Paul

Sub Test3()
    
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 5
    newSecond2 = Second(Now()) + 60
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    waitTime2 = TimeSerial(newHour, newMinute, newSecond2)
    Dim retVal As String
            
    retVal = Shell("cmd.exe", vbNormalFocus) 
    Application.Wait waitTime
   
    SendKeys "cd\", True ' back to root of drive
    SendKeys "~", True
    
[COLOR=#FF0000]    Application.SendKeys ("copy /y")
    Application.SendKeys (" C:\Users\Paul\Pictures\Captures\original.PNG")
    Application.SendKeys (" [/COLOR][URL="file://\\network.name.com\sites\test\SupplimentaryDocuments\filenewname.PNG"][COLOR=#FF0000]\\network.name.com\sites\test\SupplimentaryDocuments\filenewname.PNG[/COLOR][/URL][COLOR=#FF0000]")
    Application.SendKeys ("~")[/COLOR]
    
    Application.Wait waitTime2 
    SendKeys "exit", True
    SendKeys "~", True
    
End Sub

 

[Norie]

Why are you using SendKeys?

 

[Kenneth Hobson]

Does FileCopy() not suffice? FSO's FileCopy is another way. If you must use Shell(), why not send it all in one string? e.g.

Shell "cmd /c copy c:\temp\ken.xlsx c:\temp\ken2.xlsx | y", vbHide

Note, when sending file names that may contain space characters, be sure to encapsulate those in quotes.
e.g.

Shell "cmd /c copy ""c:\temp\ken.xlsx"" ""c:\temp\ken2.xlsx"" | y", vbHide

If your code needs to wait some time until completion, Shell Execute and Wait() API method might be needed. e.g.

Of course FSO's FileExists() might be worth doing in some case that negates the need for a Y, yes, overwrite if file exists.

 

[welshgasman]

I'd use

FileCopy SourceFile, DestinationFile

 

[Paul Sansom]

I finally gave up on the cmd window. and am using FileCopy. The old cmd style window is easy to use but restrictive.

You cant select a folder or file name with a space or special character and these days everyone uses spaces in their folder names.
I am using CopyFile not FileCopy as this was not available????

 

[Norie]

Paul

FileCopy is VBA.

 

[welshgasman]

Sub Testproc()
Dim SourceFile, DestinationFile As String


SourceFile = "F:\Users\Paul\Documents\TrayStatus Backups\ProVersionDowngradeBackup.reg"   ' Define source file name.
DestinationFile = "c:\Temp\ProVersionDowngradeBackup.reg"   ' Define target file name.
FileCopy SourceFile, DestinationFile


End Sub

works fine and has spaces as you can see.?
Not sure if any extra references are required but I have

Visual Basic For Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library

Don't worry about the 12.0 as that is because I have 2007

 

[Kenneth Hobson]

I guess you found fso's CopyFile() which was what I meant in post #3 . It allows the overwrite option. VBA.FileCopy() does not. You must use something like Dir() to check if the file exits and if it does, Kill() it. Timing can be an issue when doing that.

I don't know what you mean in #5 . I do it all the time as I showed you in post #3 's 2nd code block. You are always safe using the encapsulated quotes method for it.

FSO method:

Sub Main()
  Dim Source$, Target$
  Dim fso As Object
  'Dim fso As New FileSystemObject
  
  Source = "F:\Users\Paul\Documents\TrayStatus Backups\ProVersionDowngradeBackup.reg"   ' Define source file name.
  Target = "c:\Temp\ProVersionDowngradeBackup.reg"   ' Define target file name.
  
  Set fso = CreateObject("Scripting.FileSystemObject")
  
  'FileCopy SourceFile, DestinationFile
  With fso
    If .FileExists(Source) And .FolderExists(.GetParentFolderName(Target)) Then _
      .CopyFile Source, Target, True
  End With
  
  Set fso = Nothing
End Sub

 

[welshgasman]

That code I posted above copies the file and overwrites it with no warning?
I even added DoCmd.SetWarnings True before the copy, with same result?

I guess you found fso's CopyFile() which was what I meant in post #3 . It allows the overwrite option. VBA.FileCopy() does not. You must use something like Dir() to check if the file exits and if it does, Kill() it. Timing can be an issue when doing that.

 

[Kenneth Hobson]

Right.

FSO makes it easier if you want to check for the file or folder existing before using the command which would error if either were true. You can always use Dir() to check for those though.

As for DoCmd, that is an Access command so I don't know what purpose it would serve. I guess you are thinking of Application.DisplayAlerts=False. Use that sort of thing with caution and be sure to set it back to True.