Excel Macro File Transfer
Excel Macro File Transfer
(OP)
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
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





RE: Excel Macro File Transfer
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=...
RE: Excel Macro File Transfer
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: www.essexsteam.co.uk
RE: Excel Macro File Transfer
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.
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
RE: Excel Macro File Transfer
Didn't any of the posts work for you?
Do let us know...
Mala Singh
'Dare to Imagine'
RE: Excel Macro File Transfer
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
RE: Excel Macro File Transfer
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: www.essexsteam.co.uk
RE: Excel Macro File Transfer
Will "Name" move files from one network harddrive to another?
Roger
RE: Excel Macro File Transfer
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: www.essexsteam.co.uk
RE: Excel Macro File Transfer
path1 = C:\here
fn1 = test.pdf
path2 = \\this\that\there
Name path1 & fn1 As path2 & fn1
RE: Excel Macro File Transfer
CODE
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: www.essexsteam.co.uk