[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 750
  • Last Modified:

Cannot disconnect network drive after saving file to network drive in excel

Hi,

I have code that maps a network drive (Q;) then saves the current open file to a folder in the drive (using SaveCopyAs) and then attempts to disconnect the network drive.  I'm receiving the error message "This network connection has files open or pending".  I need help in disconnecting this network share.  My code is attached.
Private Sub SaveWB_Click()
    Const Network_Path As String = "\\ki069\ESM_Auto_Save"
    Dim Release, strApplication, TNC, Project, CRNum, strUserID, strPassWord, strFullPath, strDirectoryPath, strFileName As String
    Dim objNetwork, objShell, CheckDrive As Object
    Dim intDrive As Integer
    Dim strDriveLetter, strRemotePath, CurrentFile As String
    Dim AlreadyConnected, ReplaceFile As Boolean
    Dim ActSheet As Worksheet
    Dim ActBook As Workbook
    Dim NewFileType, NewFile As String
    Dim Drive_Okay As Boolean
               
    strDriveLetter = "Q:"
    strUserID = "SomeUserID"
    strPassWord = "SomePassword"

    Set objShell = CreateObject("WScript.Shell")
    Set objNetwork = CreateObject("WScript.Network")
    Set CheckDrive = objNetwork.EnumNetworkDrives()
    
    AlreadyConnected = False
    
    For intDrive = 0 To CheckDrive.Count - 1 Step 2
        If CheckDrive.Item(intDrive) = strDriveLetter Then
            AlreadyConnected = True
        End If
    Next

    If AlreadyConnected = True Then
        objNetwork.RemoveNetworkDrive strDriveLetter
        objNetwork.MapNetworkDrive strDriveLetter, Network_Path, , strUserID, strPassWord
    Else
        objNetwork.MapNetworkDrive strDriveLetter, Network_Path, , strUserID, strPassWord
    End If
    
    Release = "2012.01"
    strApplication = "BMIU"
    TNC = "TC0"
    Project = "777777"
    CRNum = ""
    
    strFullPath = strDriveLetter & "\" & Release
    
    
    If Len(CRNum) <> 0 Then
        strFileName = strApplication & "_" & Project & "_" & TNC & "_" & CRNum & "_ESM.xls"
    Else
        strFileName = strApplication & "_" & Project & "_" & TNC & "_ESM.xls"
    End If
    
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then
        If Not Dir(strFullPath & "\" & strFileName, vbDirectory) = vbNullString Then
            ReplaceFile = MsgBox("A file with the name " & strFileName & " already exists in this directory.  " & _
                                 "Do you wish to replace it?", vbYesNo)
            If ReplaceFile = True Then
                NewFile = strFullPath & "\" & strFileName
                If NewFile <> "" And NewFile <> "False" Then
                    ActiveWorkbook.SaveCopyAs NewFile
                    MsgBox "ESM Saved."
                End If
            End If
        Else
            NewFile = strFullPath & "\" & strFileName
            If NewFile <> "" And NewFile <> "False" Then
                ActiveWorkbook.SaveCopyAs NewFile
                MsgBox "ESM Saved."
            End If
        End If
    Else
        strDirectoryPath = strFullPath
        MkDir strDirectoryPath
        If Not Dir(strFullPath & "\" & strFileName, vbDirectory) = vbNullString Then
            ReplaceFile = MsgBox("A file with the name " & strFileName & " already exists in this directory.  " & _
                                 "Do you wish to replace it?", vbYesNo)
            If ReplaceFile = True Then
                NewFile = strFullPath & "\" & strFileName
                If NewFile <> "" And NewFile <> "False" Then
                    ActiveWorkbook.SaveCopyAs NewFile
                    MsgBox "ESM Saved."
                End If
            End If
        Else
            NewFile = strFullPath & "\" & strFileName
            If NewFile <> "" And NewFile <> "False" Then
                ActiveWorkbook.SaveCopyAs NewFile
                MsgBox "ESM Saved."
            End If
        End If
    End If
    
    Set objShell = Nothing
    Set objNetwork = Nothing
    Set CheckDrive = Nothing
    Set MyDrive = Nothing

Set objShell = CreateObject("WScript.Shell")
Set objNetwork = CreateObject("WScript.Network")
Set AllDrives = objNetwork.EnumNetworkDrives()

    
    Set MyDrive = CreateObject("WScript.Network")

    MyDrive.RemoveNetworkDrive "Q:"
   
End Sub

Open in new window

0
nj_jeep_girl
Asked:
nj_jeep_girl
1 Solution
 
DerZaubererCommented:
Well you could use bForce parameter to force disconnection. Not sure if that will solve your problem:

...
objNetwork.RemoveNetworkDrive strDriveLetter, True

Open in new window

...
0
 
nj_jeep_girlAuthor Commented:
Thank you so much!  Okay, I read about this parameter, but was using it incorrectly...you have saved my day!!!!  Thank you!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now