move all xlsx files from directory to another using excel 2010 vba

Question

In the FromPath there are 4 xlsx files that I would like to move to the variable MyDirectory. Currently the vba runs but when it gets to this point the files remain in the temp directory (FromPath) and do not get moved and I am not sure why. Thank you :).

vba

'TRANSFER FROM TEMP '
Dim FSO As Object
Dim FromPath As String
Dim ToPath As String

FromPath = "C:\Users\cmccabe\Desktop\EmArray\*.xlsx"
ToPath = "MyDirectory"


If Right(FromPath, 1) = "\" Then
    FromPath = Left(FromPath, Len(FromPath) - 1)
End If

If Right(ToPath, 1) = "\" Then
    ToPath = Left(ToPath, Len(ToPath) - 1)
End If

Set FSO = CreateObject("scripting.filesystemobject")

If FSO.FolderExists(FromPath) = False Then
    MsgBox FromPath & " doesn't exist"
    Exit Sub
End If

FSO.CopyFolder Source:=FromPath, Destination:=ToPath

edit

Private Sub CommandButton21_Click()
Dim MyBarCode   As String      ' Enter Barcode
Dim MyScan      As String      ' Enter ScanDate
Dim MyDirectory As String

'GET USER INPUT '
Line1:
MyBarCode = Application.InputBox("Please enter the last 5 digits of the barcode", "Bar Code", Type:=2)

If MyBarCode = "False" Then Exit Sub   'user canceled
Do
    MyScan = Application.InputBox("Please enter scan date", "Scan Date", Date - 1, Type:=2)
    If MyScan = "False" Then Exit Sub   'user canceled
    If IsDate(MyScan) Then Exit Do
    MsgBox "Please enter a valid date format. ", vbExclamation, "Invalid Date Entry"
Loop

'CREATE NEXUS DIRECTORY AND VERIFY FOLDER '
MyDirectory = "N:\1_DATA\MicroArray\NexusData\" & "2571683" & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy") & "\"
If Dir(MyDirectory, vbDirectory) = "" Then
    MkDir MyDirectory
Else
    MsgBox ("Already exsists! Please enter again")
    GoTo Line1
End If

' TRANSFER FILES '
 Dim MyFile As String

MyFile = Dir("C:\Users\cmccabe\Desktop\EmArray\*.xlsx")

Do Until MyFile = ""

Name "C:\Users\cmccabe\Desktop\EmArray\*.xlsx" & MyFile As "N:\1_DATA\MicroArray\NexusData\" & "2571683" & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy") & MyFile

MyFile = Dir

Loop
End Sub

Does the topath need to be more explicit? C:\Users\Christophe\Desktop\MyDirectory\ you're also not checking if the MyDirectory folder exists... — Mathemats
– Mathemats, Commented Jan 14, 2016 at 22:50
MyDirectory is created in a step prior by user input.... I will try making the ToPath more explicit. Thank you :). — justaguy
– justaguy, Commented Jan 14, 2016 at 23:00
CopyFolder doesn't accept a file mask (*.xlsx). It copies entire folders, not individual files, and it doesn't move (copy to a new destination and delete from the old), it copies. (That's why it's named CopyFolder.) See the code in this question - the question asks about recursively moving files from multiple folders, but it has code for moving files rather than just copying them. — Ken White
– Ken White, Commented Jan 14, 2016 at 23:14
I added an edit in which the user enters a # and that is stored as a variable and then the user enters a date which is also stored as a variable. In the transfer files section all the xlsx files are supposed to be sent to those variables, but I am getting a bad file name error. Thank you :). — justaguy
– justaguy, Commented Jan 15, 2016 at 19:39

Paul · Accepted Answer · 2016-01-15 04:45:54Z

0

Have code that finds all xlsx files in a folder for other purposes, runs all the files through a for/next loop and finds those with .xlsx at the end, this will be really slow and inefficent if you've got millions of files to sort through.

Dim f As Object, fso As Object

Set fso = CreateObject("Scripting.FileSystemObject")

For Each f In fso.getfolder(Folder).Files
    If Left(f.Name, Len(ContractNumber)) = ContractNumber And Right(f.Name, 4) = "xlsx" Then
        f.CopyFile Source:=FromPath, Destination:=ToPath
    End If
Next

edited Jan 15, 2016 at 4:45

answered Jan 15, 2016 at 4:38

Paul

463 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

move all xlsx files from directory to another using excel 2010 vba

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related