Macro To Move Files - Will Not Move .msg or .csv files

[rachel06]

Hi, me again!

I'm digging into something a previous colleague worked on which is a macro that can rename and move files. It works great, however it won't move .msg or . csv files and I can't figure it out. I added those two cases myself matching the others that were there and just not having no luck. Anyone know what I'm missing here as to why it won't move csv or msg files but will move everything else?

Dim NewName As String

Sub Copyfilefromto()

Dim mycheck As VbMsgBoxResult
        
    mycheck = MsgBox("Confirm that you'd like to start the file mover. The more files to move, the longer this will take. ", vbYesNo)
    If mycheck = vbNo Then
        Exit Sub
        End If
    

Dim a As Long, x As Long
Dim FilePath As String
Dim FileName As String
Dim ErrCount As Long

ErrCount = 1

x = Worksheets("Query").Cells(Rows.Count, 3).End(xlUp).Row
For a = 4 To x

FilePath = Worksheets("Query").Cells(a, 4)
FileName = Worksheets("Query").Cells(a, 3)

On Error GoTo ErrorHandler

Call GetFileType(FileName, FilePath, a)

FileCopy Worksheets("Query").Cells(a, 4) & Worksheets("Query").Cells(a, 3), Worksheets("Query").Cells(a, 1) & NewName

Next a

MsgBox ("Process Complete. Please review ErrMsgs Sheet for failures.")
Cells(2, 5).Value = x - 3
Exit Sub

ErrorHandler:
    Worksheets("ErrMsgs").Activate
    Cells(ErrCount, 1).Value = FileName
    Cells(ErrCount, 2).Value = Err.Description
    Worksheets("Query").Activate
    ErrCount = ErrCount + 1
Resume Next

End Sub

Sub GetFileType(SourceName, SourcePath, iRow)

    mySourcePath = SourcePath & SourceName
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFile(mySourcePath)
        
    Select Case mySource.Type
        Case "Adobe Acrobat Document"
            Base = Left(Cells(iRow, 3), InStr(Cells(iRow, 3), ".pdf") - 1)
            Call BuildName(Base, ".pdf")
        Case "Microsoft Excel Worksheet"
            Base = Left(Cells(iRow, 3), InStr(Cells(iRow, 3), ".xlsx") - 1)
            Call BuildName(Base, ".xlsx")
        Case "Microsoft Word Document"
            Base = Left(Cells(iRow, 3), InStr(Cells(iRow, 3), ".pdf") - 1)
            Call BuildName(Base, ".doc")
        Case "Outlook Item"
            Base = Left(Cells(iRow, 3), InStr(Cells(iRow, 3), ".msg") - 1)
            Call BuildName(Base, ".msg")
        Case "Comma Separated Values"
            Base = Left(Cells(iRow, 3), InStr(Cells(iRow, 3), ".csv") - 1)
            Call BuildName(Base, ".csv")
    End Select
    Cells(iRow, 6).Value = NewName
End Sub

Sub BuildName(BaseName, FileExt)
    Dim BegText As String
    Dim EndText As String
    
    BegText = Range("C1")
    EndText = Range("C2")
    If Len(BegText) > 0 Then
        BegText = BegText & "_"
    End If
    If Len(EndText) > 0 Then
        EndText = "_" & EndText
    End If
    NewName = BegText & BaseName & EndText & FileExt
End Sub

 

[Micron]

Did you step through the code and verify that variable values are what you expect? If you can, give it a test folder with 1 csv and one msg file. Put a break point on the first Case line and run the code. Then mouse over mysource.type - I think you will find that it's not what you're testing for. This will be true if it is an Excel csv but I don't know about others for sure. A notepad csv file (if there is such a thing) is probably a text file.

EDIT - this is what I get for a wb saved as a csv:
Microsoft Excel Comma Separated Values File

 

[rachel06]

So so simple. I at least got the csv portion to work. I'll have to take a look at the Outlook portion next.

Thanks so much!!

 

[Micron]

What I'd expect a msg file to return seems correct in your code, but I thought perhaps they aren't really files that end in .msg
Glad I could help & thanks for the recognition.

 

[rachel06]

What I'd expect a msg file to return seems correct in your code, but I thought perhaps they aren't really files that end in .msg
Glad I could help & thanks for the recognition.

Turns out in my code I had a .pdf instead of a .msg. Kind of complicated but I have two versions of this for two different teams, one was updated, and one had a .pdf instead of a .msg. Easy peasy!