-
May 10th, 2018, 04:59 PM
#1
Thread Starter
Lively Member
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.
-
May 10th, 2018, 05:32 PM
#2
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
-
May 10th, 2018, 05:42 PM
#3
Thread Starter
Lively Member
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.
-
May 10th, 2018, 05:45 PM
#4
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.
-
May 10th, 2018, 05:52 PM
#5
Thread Starter
Lively Member
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
-
May 10th, 2018, 05:57 PM
#6
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
-
May 10th, 2018, 06:04 PM
#7
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.
-
May 10th, 2018, 09:47 PM
#8
Thread Starter
Lively Member
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,
-
May 11th, 2018, 04:51 AM
#9
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
-
May 11th, 2018, 08:43 PM
#10
Thread Starter
Lively Member
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..
-
May 12th, 2018, 05:52 AM
#11
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|