Results 1 to 11 of 11

Thread: VBA not working With Network Drive Path

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Lightbulb VBA not working With Network Drive Path

    Hello Everyone,
    I have a VBA code to replace the Special Characters from folder path.. and this VBA i got from this forum.
    This VBA worked well with Local Drive Path but when i try to run vba with Network Path i got an error like Bad file Name or Number error on this line If Len(Dir(oldpath, vbDirectory)) And Not NewPath = oldpath Then.

    Code:
    Sub test()
    Dim afind, areplace
    afind = Array("@", "#", "%", "_")
    areplace = Array("AT", "NUMBER", "PERCENT", "UNDERSCORE")
            For Each Cell In Range("D14:D10000")
               If IsEmpty(Cell) Then Exit For   '   finish on first empty cell, or change range to suit
                    farray = Split(Cell, "\")
                    NewPath = farray(0)
                    For f = 1 To UBound(farray)
                        oldpath = NewPath
                        oldpath = oldpath & "\" & farray(f)
                        tmp = farray(f)
                        For i = 0 To UBound(afind)
                           tmp = Replace(tmp, afind(i), areplace(i))
                        Next
                        NewPath = NewPath & "\" & tmp
                        If Len(Dir(oldpath, vbDirectory)) And Not NewPath = oldpath Then Name oldpath As NewPath
                    Next
                    If Not Len(Dir(NewPath, vbDirectory)) > 0 Then MsgBox "path in " & Cell.Address & " not changed"
                    'If Not Len(Dir(NewPath, vbDirectory)) Then MsgBox "path in " & Cell.Address & " not changed"
            Next Cell
    End Sub
    The Local file Path Like:E:\3\@hjghj_mnb#\zsh% c#
    The Network Path Like:\\lte.cell\UMTS\Strat\RTD\Folder\Trans\Misc Folder

    Please Help me anyone.


    Thanks in Advance.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: VBA not working With Network Drive Path

    And your problem is what?
    Checking if farray(0) is like C: or D: or E: (local Drive)?
    Checking if farray(0) is exactly two characters long with the second one being a ":"?
    And if not it has to be a Network-Drive?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: VBA not working With Network Drive Path

    Hey Zvoni
    Actually I'm not Expert on VBA..
    My problem is the vba shows error while trying to replace special character.

    Can you please give me the suggestion.

  4. #4
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: VBA not working With Network Drive Path

    With that error on the line indicated, it looks like the Dir function is not liking something.

    Post what the contents of the oldpath and vbDirectory are.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: VBA not working With Network Drive Path

    Oldpath is \\lte.cell\UMTS\Strat\RTD\Folder\Trans\Misc Folder\@#sam#_

    Ans I'm not aware of vbdirectory

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: VBA not working With Network Drive Path

    jdc, he's using Split on Oldpath along "\" as Delimiter
    farray is the Result of that Split.
    He's assigning farray(0) to Newpath, and the first statement in the For/Next is OldPath=Newpath
    The next statement is OldPath=OldPath & "\" & farray(f) --> f starting at 1
    The same with NewPath
    That works fine as long as the PathName (Variable "Cell") doesn't start with a Delimiter "\" (as in local drives).
    Now, what's the Result of a Split along "\" as a Delimiter on a Networkpath like "\\MyServer\My\Path\To\my\secret\Files"?
    especially what's farray(0) (and farray(1) and farray(2) for that matter) looking like?

    I wouldn't be surprised, if he's missing a "\" at the beginning in Oldpath and Newpath after rebuilding both again
    Last edited by Zvoni; May 10th, 2018 at 06:00 PM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: VBA not working With Network Drive Path

    If it is actually getting an error on the line indicated, put a Stop statement after this line:

    NewPath = NewPath & "\" & tmp

    Then see what the contents of the oldpath variable is at that point.

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: VBA not working With Network Drive Path

    Actually I don't want to run with local drive..If it is working with only for Network drive that's enough.


    Anyone help me please,

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VBA not working With Network Drive Path

    If Len(Dir(oldpath, vbDirectory)) And Not NewPath = oldpath Then
    i believe i changed that line in the original thread to
    Code:
    If Len(Dir(oldpath, vbDirectory)) > 0 And Not NewPath = oldpath Then
    while the original looks like it should work, i seem to remember it did cause some errors

    i have no idea if that has anything to do with the current problem

    maybe if you map the network drive the code could work correctly
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: VBA not working With Network Drive Path

    Hey Westconn
    I have changed that line as you mentioned on post#9
    But i got the same error..

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VBA not working With Network Drive Path

    it would appear that dir(oldpath,vbdirectory) does not work on UNC paths, you may need to use some other method, like FSO or a shell object

    you can test this code that use fso
    Code:
    Sub test()
    Dim afind, areplace
    Dim fso As FileSystemObject, pth As Object
    Set fso = New FileSystemObject
    afind = Array("@", "#", "%", "_")
    areplace = Array("AT", "NUMBER", "PERCENT", "UNDERSCORE")
            For Each Cell In Range("a1:a10000")
               If IsEmpty(Cell) Then Exit For   '   finish on first empty cell, or change range to suit
                    farray = Split(Cell, "\")
                    newpath = farray(0)
                    For f = 1 To UBound(farray)
                        oldpath = newpath
                        oldpath = oldpath & "\" & farray(f)
                        tmp = farray(f)
                        For i = 0 To UBound(afind)
                           tmp = Replace(tmp, afind(i), areplace(i))
                        Next
                        newpath = newpath & "\" & tmp
                        If f > 2 Then
                            On Error Resume Next
                            Set pth = fso.GetFolder(oldpath)
                            If Not Err.Number = 0 Then
                                Err.Clear
                                Set pth = fso.GetFile(oldpath)
                            End If
                            On Error GoTo 0
                            If Not newpath = oldpath Then pth.Name = tmp
                        End If
                    Next
            Next Cell
    End Sub
    Last edited by westconn1; May 12th, 2018 at 06:57 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width