(Solved) – How to resolve upper case lower case in copy file function While copying file over network

  • by
(solved)-–-how-to-resolve-upper-case-lower-case-in-copy-file-function-while-copying-file-over-network

Requirement:
To push (copy) whatever file is in Source Folder to the Path specified in dest string variable (which is basically IP address of pcs on a same network). There are multiple paths on network.

Problem:
Folder at destination locations is named user but it culd be written in upper case or lower case. If destination folder is on lower case
files copies without problem but if destination folder is in upper case (USER) then file is not copying.

I am trying with the help of If else but it is not working. Is there a way to use MATCASE expression with CopyFile function ?
or any other way to do it ?

Please guide.

Sub pushFile()

    Dim i As Integer
    i = 0
    Dim spath As String
    spath = Application.ActiveWorkbook.PATH & "Source Folder"
    Filename = Dir(spath & "*.*")

    ActiveWorkbook.Worksheets(2).Activate
    lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

    Dim dest As String
    Dim dest2 As String

    Set FSO = CreateObject("Scripting.FileSystemObject")

    For i = 2 To lastRow
        dest = ActiveSheet.Range("B" & i)
        dest2 = "\" & dest & "USER"
        dest = "\" & dest & "user"

        On Error Resume Next
        If FSO.FolderExists(dest) = True Then
            FSO.CopyFile (spath & Filename), dest, True
            ActiveSheet.Range("C" & i).Value = dest
         ElseIf FSO.FolderExists(dest2) = True Then
            FSO.CopyFile (spath & Filename), dest2, True
            ActiveSheet.Range("C" & i) = dest2
         Else
             MsgBox "Error"
         End If

    Next i

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *