I need to map network drives to copy a file ! I tried to use the microsoft suggested function getnextletter but am getting errors.
Seems the oDrives is not an object As I get object required at that line!
Public Function GetNextLetter(DriveLetter)
'Start x at the ascii value of the drive letter to start the search
'unless something is passed in. This sample uses capital letters and
'starts at F.
If IsEmpty(DriveLetter) Then
x = 70
x = Asc(DriveLetter)
Set oDrives = wshNet.EnumNetworkDrives
'Step by two since the first item in the collection is the drive letter
'and the second item is the network mapping
For i = 0 To oDrives.Count - 1 Step 2
If Chr(x) & ":" = oDrives.Item(i) Then
x = x + 1
GetNextLetter = Chr(x) & ":"
The VBA that references this function is!
Private Sub Command0_Click()
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set wshNet = CreateObject("WScript.Network")
strSource = GetNextLetter("F")
wshNet.MapNetworkDrive strSource, "\\TQFPRK06\project management\"
strDestination = GetNextLetter("F") '<-- You can change the letter. It represents the starting letter to work from.
wshNet.MapNetworkDrive strDestination, "\\TQFPRK06\Tech_drive\"
strSource = strSource & "test.xlsx"
objFSO.CopyFile strSource, strDestination & "\"
wshNet.RemoveNetworkDrive strDestination, True
wshNet.RemoveNetworkDrive strSource, True
Set objFSO = Nothing
Set wshNetwork = Nothing