Solved Moving a file with click of a button

[donkey9972]

Ok, I have half of my problem solved, I just have no idea how to incorporate the 2nd half. So using a simpletext box, and button I can use the filedialog to select a file which I have done, How do I take that file location that is now appearing in the text box and move that file to a specific destination location? Note that the destination location will always be the same, so that can be put into the code or VBA without an issue. What is always changing is the location the file is coming from.

 

[CJ_London]

Look at using the filecopy function

FileCopy statement (VBA)

Office VBA reference topic

learn.microsoft.com

Followed by the kill statement

Kill statement (VBA)

Office VBA reference topic

learn.microsoft.com

 

[donkey9972]

I saw the filecopy but it requires a source file to be defined in the code. I am trying to find an option that takes the source file from whatever the user selects in the FileDialog method that appears in the textbox on the form.

 

[cheekybuddha]

You can use the VBA Name statement

eg.

Dim FileName As String
Const DESTINATION_PATH As Strin = "C:\Path\To\Move\File\"

If Len(Me.txtSelectedFilePath & vbNullString) > 0 Then
  ' Get file name
  FileName = Mid(Me.txtSelectedFilePath, InStrRev(Me.txtSelectedFilePath, "\") + 1)
  ' Do the move
  Name Me.txtSelectedFilePath As DESTINATION_PATH & FileName
End If

 

[donkey9972]

You can use the VBA Name statement

eg.

Dim FileName As String
Const DESTINATION_PATH As Strin = "C:\Path\To\Move\File\"

If Len(Me.txtSelectedFilePath & vbNullString) > 0 Then
  ' Get file name
  FileName = Mid(Me.txtSelectedFilePath, InStrRev(Me.txtSelectedFilePath, "\") + 1)
  ' Do the move
  Name Me.txtSelectedFilePath As DESTINATION_PATH & FileName
End If

Doesn't this still require the old file name to be put into the code? At least what I was reading in the link you provided that is what it seems to me.

 

[cheekybuddha]

So using a simpletext box, and button I can use the filedialog to select a file which I have done

Doesn't this still require the old file name to be put into the code?

Haven't you got the old filename in the textbox?

The code I posted (*should*) grabs the filename from the path/filename in the textbox.

 

[donkey9972]

I found my mistake on it. You are right, this does exactly as was needed. Thank you.

 

[cheekybuddha]

I found my mistake on it. You are right, this does exactly as was needed. Thank you.

Glad you got it working.

Did you understand what the code was doing?

If not, make sure to ask.

 

[donkey9972]

I had to look it over a few times, but eventually I understood this code. Thank you.

 

[moke123]

Personally I would use FSO as it has a move method as well as a copy method.

MoveFile method (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[cheekybuddha]

Personally I would use FSO as it has a move method as well as a copy method.

MoveFile method (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

A lot of additional overhead just to move a file when there is a native built-in method, no?

 

[moke123]

A lot of additional overhead just to move a file when there is a native built-in method, no?

Depends. IIRC, Name() will fail if a file by the same name already exists. I like fso to rename a file if the same name already exists.

Public Sub MoveFileRename(SourceFile As String, DestFolder As String, Optional CopyF As Boolean = False)

    If Right(DestFolder, 1) <> "\" Then DestFolder = DestFolder & "\"
    
    Dim fso As New FileSystemObject
    Dim pthA As String, pthB As String, pthC As String, Fpath As String
    Dim TName As String, Ext As String, i As Integer
    
    pthA = SourceFile
    pthB = DestFolder

    TName = fso.GetBaseName(pthA)
    Ext = fso.GetExtensionName(pthA)
    
    pthC = fso.BuildPath(pthB, TName & "." & Ext)

    If fso.FileExists(pthC) Then
    
        For i = 1 To 100
            pthC = fso.BuildPath(pthB, TName & "(" & i & ")" & "." & Ext)
            If Not fso.FileExists(pthC) Then
                Fpath = pthC
                Exit For
            End If
        Next
    Else
        Fpath = pthB
    End If

    If CopyF = True Then
        fso.CopyFile pthA, Fpath, False
    Else
        fso.MoveFile pthA, Fpath
    End If
    
End Sub

 

[cheekybuddha]

Depends. IIRC, Name() will fail if a file by the same name already exists. I like fso to rename a file if the same name already exists.

Renaming if the file already exists is equally simple using the native functions:

  Dim FileName As String
  Const DESTINATION_PATH As String = "C:\Path\To\Move\File\"
 
  If Len(Me.txtSelectedFilePath & vbNullString) > 0 Then
    ' Get file name
    FileName = Mid(Me.txtSelectedFilePath, InStrRev(Me.txtSelectedFilePath, "\") + 1)
    ' Check we don't try to overwrite
    If Len(Dir(DESTINATION_PATH & FileName)) > 0 Then
      ' Rename if already exists
      Name DESTINATION_PATH & FileName _
        As DESTINATION_PATH & _
           Left(FileName, InStrRev(FileName, ".") - 1) & "." & Format(Now, "yyyymmddhhnnss") & ".bak" & Mid(FileName, InStrRev(FileName, "."))
    End If
    ' Do the move
    Name Me.txtSelectedFilePath As DESTINATION_PATH & FileName
  End If

I would only use FSO if the app required performing many file operations.

Admittedly, FSO does have a much nicer and more 'readable' syntax.

 

[donkey9972]

You can use the VBA Name statement

eg.

Dim FileName As String
Const DESTINATION_PATH As Strin = "C:\Path\To\Move\File\"

If Len(Me.txtSelectedFilePath & vbNullString) > 0 Then
  ' Get file name
  FileName = Mid(Me.txtSelectedFilePath, InStrRev(Me.txtSelectedFilePath, "\") + 1)
  ' Do the move
  Name Me.txtSelectedFilePath As DESTINATION_PATH & FileName
End If

This code works perfectly.

 

[moke123]

Admittedly, FSO does have a much nicer and more 'readable' syntax.

Agreed. With method names like fso.GetExtensionName, fso.FileExists, and fso.BuildPath, it doesn't kill many brain cells figuring out what it does.