CODEWITHSUNDEEP
excelvba
VJ D
VJ D

Reputation: 177

Creating a folder in a network drive using vba

I need to connect to a network drive and create a folder. I dont know the IP of the drive. Also I dont want to use the drive letter as this VBA will be used by many people on their PC.

I tried this :

Public Function create_folder()
 Dim NetworkObject As Object
    Dim FSO As Object
    Dim Directory As Object
    Dim Filename As Object
    Dim ServerShare As String
 ServerShare = "\\SSSXCXC\FOL_SAS\ASD123\"

Set NetworkObject = CreateObject("WScript.Network")
Set FSO = CreateObject("Scripting.FileSystemObject")
NetworkObject.MapNetworkDrive "", ServerShare, False
 Set Directory = FSO.CreateFolder(ServerShare & "\AAA")
End Function

But I am getting path error.

Upvotes: 2

Views: 2998

Answers (3)

Alasdair Walmsley
Alasdair Walmsley

Reputation: 11

I have a similar problem which I'll explain, and a working solution at the bottom (which can write to the network but not copy).

I would like to copy a folder on the network and rename it. It's a folder with a nest of sub-folders we use as a template.

It looks like it should work from the Locals window, but the folder doesn't get created.

I assume it's a network rights problem

Sub CreateMajorResponseFile()
'creates a response folder with the app number as folder name

'declare road name as a variable
Dim Road As String
Dim AppNo As String
Dim Prefix As String
Dim FolderPath As String
Dim tbl As ListObject
Dim LastRow As Long
'enable code to use Scripting.FileSytemObject so it's easy to copy and rename a folder pt1
Dim FSO As Object
'pt2 of enable code to use Scripting.FileSytemObject so it's easy to copy and rename a folder
Set FSO = CreateObject("Scripting.FileSystemObject")

Dim SourceFolder As String
Dim DestinationFolder As String

'set the folder you want to copy
SourceFolder = "S:my source folder"
'set the file path for the folder you want to copy to
DestinationFolder = "S:\my destination folder" & Road

'set the road variable to be 4 rows right of selected cell
Road = ActiveCell.Offset(0, 4).value

'set the AppNo the selected cell
AppNo = ActiveCell.value
AppNo = Left(AppNo, 6) & "-" & Right(AppNo, 5)

Prefix = Application.InputBox("Enter N followed by EITHER property number OR short name", "Give it a prefix", "N")


''create folder with file path to the road name folder
'MkDir "S:\my network path" & "\" & Road & "\" & Prefix & " " & AppNo & Suffix

FSO.CopyFolder Source:=SourceFolder, Destination:=DestinationFolder & "\" & Prefix & " " & AppNo


End Sub

But I have another piece of code that can create a new folder on the network without using file system objects. This is the code

Sub CreateResponseFile()
'creates a response folder with the app number as folder name

'declare road name as a variable
Dim Road As String
Dim AppNo As String
Dim Prefix As String
Dim Suffix As String
Dim FolderPath As String



'set the road variable to be 4 rows right of selected cell
Road = ActiveCell.Offset(0, 4).value

'set the AppNo the selected cell
AppNo = ActiveCell.value
AppNo = Left(AppNo, 6) & "-" & Right(AppNo, 5)

Prefix = Application.InputBox("Enter N followed by EITHER property number OR short name", "Give it a prefix", "N")

Suffix = Application.InputBox("Enter a space followed by e.g. C16 cycl parking or CEMP etc, if full app leave blank", "Give it a suffix", " C99EVCP")

'create folder with file path to the road name folder
MkDir "S:\my network path" & "\" & Road & "\" & Prefix & " " & AppNo & Suffix


End Sub

so it seems MkDir can work over a network where FSO copy cannot

Upvotes: 1

Aliasghar Ahmadpour
Aliasghar Ahmadpour

Reputation: 435

Function GetNetworkPath(ByVal DriveName As String) As String
    Dim objNtWork  As Object
    Dim objDrives  As Object
    Dim lngLoop    As Long

    Set objNtWork = CreateObject("WScript.Network")
    Set objDrives = objNtWork.enumnetworkdrives

    For lngLoop = 0 To objDrives.Count - 1 Step 2
        If UCase(objDrives.Item(lngLoop)) = UCase(DriveName) Then
            GetNetworkPath = objDrives.Item(lngLoop + 1)
            Exit For
        End If
    Next
End Function

Upvotes: 1

Kostas K.
Kostas K.

Reputation: 8518

I think you don't need to map the network drive to create a folder. If you have write access, the folder should be created.

Const SERVER_PATH As String = "\\SSSXCXC\FOL_SAS\ASD123\"

Dim folderPath As String
folderPath = SERVER_PATH & "AAA"

With CreateObject("Scripting.FileSystemObject")
    If Not .FolderExists(folderPath) Then .CreateFolder folderPath 
End With

Upvotes: 2

Related Questions