CODEWITHSUNDEEP
vbaexcel
TurboCoder
TurboCoder

Reputation: 1011

VBA - Use Directory Path on Server without Drive Letter

I have many macros that have the following path definition:

"X:\Test\3rd Party\Other Files\"

But what I need to, which is what I did with the vbscripts, is make it like this:

"\\ServerName\Folder\Test\3rd Party\Other Files\"

This is because the files that have the macros in them are on the server and they need to be able to be executed by anyone who has access to the server - and since each person might map the drive with a different letter and/or have different levels of access, the first option wont work.

When I use this:

"\\ServerName\Folder\Test\3rd Party\Other Files\"

I get the error:

Sorry, we couldn't find \ServerName\Folder\Test\3rd Party\Other Files. Is it possible it was moved, renamed or deleted?

When I use this:

"\\ServerName\Folder\Test\3rd Party\Other Files"

I get the error:

Excel cannot access "Other Files". The document may be read-only or encrypted.

Sub RenameOriginalFilesSheets()

    Const TestMode = True
    Dim WB As Workbook

    Application.ScreenUpdating = False
    rootpath = "\\ServerName\Folder\Test\Terminations\"

    aFile = Dir(rootpath & "*.xlsx")
    Do
        Set WB = Application.Workbooks.Open(rootpath & aFile, False, AddToMRU:=False)

        WB.Sheets(1).Name = Left$(WB.Name, InStrRev(WB.Name, ".") - 1)
        WB.Close True

        aFile = Dir()
        DoEvents
    Loop Until aFile = ""

    Application.ScreenUpdating = True

End Sub

Upvotes: 0

Views: 5130

Answers (1)

Aaron Pan Vega
Aaron Pan Vega

Reputation: 174

Try this, I test in VBA and it works.

Sub serverfolder()
    Dim StrFile As String
    StrFile = Dir("\\ServerIP\Folder\" & "*")
    Do While StrFile <> ""
         StrFile = Dir
    Loop
End Sub

Upvotes: 2

Related Questions