copying Excel file without opening file

[windsurferLA]

Can one copy an Excel workbook in a local directory to another directory
on a server (assuming no password is required) using a macro within an
unrelated Excel workbook without the file to be copied? This is what I
tried that did not work:

Sub BookMarkIt()
Dim SourceFile, DestinationFile

' Define source file name.

SourceFile = "C:\Documents and Settings\Michael\My
Documents\__ScratchDELL\_2007-11Nov\test1.xls"

' Define target file name.

DestinationFile =
"ftp://xx.xx.xxx.xxx/public_html/Actions/Nov18Test2.xls"


FileCopy SourceFile, DestinationFile

End Sub


I'm using Excel97, but I've found (probably as a result of various
updates) that I seem to have essentially all the capabilities of the
later versions of Excel, except for the most recent one. Furthermore, I
have found that once the password is entered, I can open and save files
from / to the server directory nearly as easily as if it was on a
local drive.

 

[broro183]

hi,

I'd recommend incorporating a test for the (source & dest) file existence
before running the filecopy statement & the below function suggested by Ken
Puls works for me:

Public Function FileFolderExists(strFullPath As String) As Boolean
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Check if a file or folder exists
If Not Dir(strFullPath,vbDirectory) = vbNullString Then FileFolderExists
= True
End Function
(This was sourced from: http://www.excelguru.ca/node/30)

Using the above testing will possibly highlight a typo etc, but (& now I'm
guessing):
*Does Filecopy work for "FTP" servers?
*Do you need to change one of your file definition strings?
(ie change to both strings using backslashes, or both strings using forward
slashes)

hth
Rob

__________________
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[windsurferLA]

Thanks for hint...

hi,

I'd recommend incorporating a test for the (source & dest) file existence
before running the filecopy statement & the below function suggested by Ken
Puls works for me:

Public Function FileFolderExists(strFullPath As String) As Boolean
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Check if a file or folder exists
If Not Dir(strFullPath,vbDirectory) = vbNullString Then FileFolderExists
= True
End Function
(This was sourced from: http://www.excelguru.ca/node/30)

Using the above testing will possibly highlight a typo etc, but (& now I'm
guessing):
*Does Filecopy work for "FTP" servers?
*Do you need to change one of your file definition strings?
(ie change to both strings using backslashes, or both strings using forward
slashes)

hth
Rob

__________________
Rob Brockett
NZ
Always learning & the best way to learn is to experience...