Excel Macro File Transfer

[rnordquest]

How do I get an Excel macro to transfer a file from one folder to another without opening it? I want it to work with all filetypes. I tried moving some PDF files and they don't open correctly. Here's what I did:

fromPath = Range("F12").Value
toPath = Range("F17").Value
Range("A1").Select

For i = 1 To endRow
FileName = ActiveCell.Value
If FileName = "" Then GoTo Found
On Error GoTo NotFound
Workbooks.Open FileName:=fromPath & FileName
Workbooks(FileName).SaveAs FileName:=toPath & FileName
Workbooks(FileName).Close SaveChanges:=False
GoTo Found

 

[AHay]

To move a file...

Sub MoveAFile(Drivespec)
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
fso.MoveFile Drivespec, "c:\windows\desktop\"
End Sub

to copy...

FileSystemObject.CopyFile "c:\mydocuments\letters\*.doc", "c:\tempfolder\"

for more information you should download the windows script documentation from

http://msdn.microsoft.com/library/default.asp?url=/downloads/list/webdev.asp

 

[johnwm]

Or you can just:

Name "c:\c.txt" As "c:\e.txt"

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[Mala]

The pdf's didn't open correctly because you opened them as workbooks and saved them to the destination folder in Excel workbook format.

You can move the files without opening them. Try inserting the following code snippet at the appropriate place (I have used your own variables names and AHay's suggested fileSystemObject technique).

Remember to get rid of the lines where you open/saveas workbook.

    '...Previous code...
    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")
    fromPath = Range("F12").Value
    toPath = Range("F17").Value
    Range("A1").Select
    For i = 1 To endRow
        Cells(i, 1).Select '(Added)
        Filename = ActiveCell.Value
        If Filename <> "" Then fso.MoveFile (fromPath & Filename), (toPath & Filename)
    Next i
    '...Subsequent code...

DARE TO IMAGINE
mala_rs_singh@rediffmail.com

 

[Mala]

rnordquest:
Didn't any of the posts work for you?
Do let us know...





Mala Singh
'Dare to Imagine'

 

[rnordquest]

Mala,

Your's is the one that will work best. Files will need to be moved from one network drive to another. I new there was a way to move them without opening.

Thanks,

Roger

 

[johnwm]

Nothing like doing it the hard way.....

FSO is lumpy, bloated and unnecessary for such simple tasks.

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[rnordquest]

John,

Will "Name" move files from one network harddrive to another?

Roger

 

[johnwm]

Sure will. Just use UNC as:

Name "c:\test1.txt" As "\\Eileen\SharedDocs\test.txt"

will move the file from the local C: drive to the remote network drive.

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[rnordquest]

And I can do it this way, right?

path1 = C:\here
fn1 = test.pdf

path2 = \\this\that\there

Name path1 & fn1 As path2 & fn1

 

[johnwm]

Once you put the strings in quotes and added appropriate backslashes it should work fine:

path1 = "C:\here\"
fn1 = "test.pdf"
path2 = "\\this\that\there\"
Name path1 & fn1 As path2 & fn1

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk