Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Move or Copy any File to New Folder (Excel 2003)

    I'd appreciate assistance on a simple macro to move or copy any nominated file to a new folder. I'm hoping for something like:

    MyMoveFile(File,CurrentFolder,NewFolder,Overwrite)

    where the first three arguments are text. If Overwrite is TRUE files would be replaced if a file of the same name is in the NewFolder. If Overwrite is FALSE (the default) replacements would not be allowed. Could the macro also return TRUE if it works, otherwise FALSE.

    Apologies in advance. My VB skills are poor, as I'm working in a Macro 4 language environment and though I've tried I can't make it work.

    regards

    Geoffrey Howell

  2. #2
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Thanks - is there an error?

    Hans,

    Thank you SO much; so quick and such good value. When I used it, it seemed to copy, not move. If that's not my fault, I'm sure that's something that I could easily learn and repair.

    regards

    Geoffrey

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move or Copy any File to New Folder (Excel 2003)

    The following function (it's not a macro because it has arguments and because it returns a value) will copy a file to another folder.

    Function MyMoveFile( _
    File As String, _
    CurrentFolder As String, _
    NewFolder As String, _
    Optional Overwrite As Boolean) As Boolean

    Dim strSource As String
    Dim strTarget As String

    On Error GoTo ErrHandler

    strSource = CurrentFolder
    If Not Right(strSource, 1) = "" Then
    strSource = strSource & ""
    End If
    strSource = strSource & File
    If Dir(strSource) = "" Then
    MsgBox "The source file does not exist.", vbInformation
    Exit Function
    End If

    strTarget = NewFolder
    If Not Right(strTarget, 1) = "" Then
    strTarget = strTarget & ""
    End If
    strTarget = strTarget & File
    If Overwrite = False Then
    If Not Dir(strTarget) = "" Then
    MsgBox "The target file already exists.", vbInformation
    Exit Function
    End If
    End If

    FileCopy strSource, strTarget
    MyMoveFile = True
    Exit Function

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Function

    Use it like this:

    Sub Test()
    If MyMoveFile("Test.xls", "C:This", "C:That") = True Then
    MsgBox "Success"
    Else
    MsgBox "Failure"
    End If
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Thanks - is there an error?

    You asked for code to move or copy a file. As I noted in my reply, I wrote code to copy a file. Here is a version to move a file.

    Function MyMoveFile( _
    File As String, _
    CurrentFolder As String, _
    NewFolder As String, _
    Optional Overwrite As Boolean) As Boolean

    Dim strSource As String
    Dim strTarget As String

    On Error GoTo ErrHandler

    strSource = CurrentFolder
    If Not Right(strSource, 1) = "" Then
    strSource = strSource & ""
    End If
    strSource = strSource & File
    If Dir(strSource) = "" Then
    MsgBox "The source file does not exist.", vbInformation
    Exit Function
    End If

    strTarget = NewFolder
    If Not Right(strTarget, 1) = "" Then
    strTarget = strTarget & ""
    End If
    strTarget = strTarget & File
    If Not Dir(strTarget) = "" Then
    If Overwrite = False Then
    MsgBox "The target file already exists.", vbInformation
    Exit Function
    Else
    Kill strTarget
    End If
    End If

    Name strSource As strTarget
    MyMoveFile = True
    Exit Function

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Function

  5. #5
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Apologies

    Hans,

    My apologies. You're absolutely correct. And thanks for the extra effort on your part.

    regards

    Geoffrey

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Apologies

    You're welcome.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •