×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Macro File Transfer

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

RE: Excel Macro File Transfer

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=...

RE: Excel Macro File Transfer

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: www.essexsteam.co.uk

RE: Excel Macro File Transfer

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.

CODE

    '...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

RE: Excel Macro File Transfer

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


Mala Singh
'Dare to Imagine'

RE: Excel Macro File Transfer

(OP)
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

RE: Excel Macro File Transfer

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: www.essexsteam.co.uk

RE: Excel Macro File Transfer

(OP)
John,

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

Roger

RE: Excel Macro File Transfer

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: www.essexsteam.co.uk

RE: Excel Macro File Transfer

(OP)
And I can do it this way, right?

path1 = C:\here
fn1 = test.pdf

path2 = \\this\that\there

Name path1 & fn1 As path2 & fn1

RE: Excel Macro File Transfer

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

CODE

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: www.essexsteam.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close