Network Mapped drive Excel freeze when running macro

taz23340

Active Member
Joined
Jun 11, 2003
Messages
336
HI Everyone,

been awhile since i have needed your help but im really stumped.

the below macro has been in use for over 10 years with periodic correcting of some lookups within the file itself and the only change to the code has been to change the location of the dump file (what this file creates) into a different network folder.

everyone has write access to a network drive, most have it mapped as K and i have yet to see an employee having changed the mappings.

last week our Exchange drive had a behind the scenes failure and the physical location of the drive has changed while what the users see is the same.

I have tried to modify the code to the mapped location or the hard code location and i always have the same problem, it works for me and noone else unless they change the output folder to the local C drive.


prior to 2014 we were using #1
until recently using #2


1- 'myPath = "'K:\Inter-Territorial\OUTGOING IT'S FROM JULY 04"
2- 'myPath = "\\MTL-FP1-H\MTL-XCHANGE$\Inter-Territorial\OUTGOING IT'S FROM JULY 04"

Ive tried to revert to the old scheme of using the mapped location code and even created a smaller sized output folder and nothing is working

#3 is the mapped location
#4 is the hard coded location

#3 - 'myPath = "K:\Inter-Territorial\OUTGOING IT'S FROM JULY 04\OUTGOING FROM JAN-01-2018"
#4 - myPath = "\\Mis-fserver-c\mtl-xchange$\Inter-Territorial\OUTGOING IT'S FROM JULY 04\OUTGOING FROM JAN-01-2018" ' New Location hardcoded

all options work for me and nothing works on any other user.
ive been on a network specialists computer, we reviewed all security rights and everyone can write. he has admin rights and he cant complete the tasks.

there is no password to open the file.

the only thing that has changed is the physical location of the server xchange

Anyone have any suggestions?

I was wondering if I needed to have the code tell me the location, then concatenate a portion of it with the folder location of the ouput folder, but why would that be needed if we all have access to the location.

FYI, I did open the file and modify on others pc's, so they have write privileges.

Thank you once again
Kevin

----------------------

Option Explicit

Sub enreg()
' enreg Macro
' Macro recorded 2008-07-07 by Christian Matte

On Error Resume Next

Dim myName As String
Dim myPath As String
Dim filenumber As Long
Dim myStr As String
Dim strNum As String
Dim strPre As String
Dim strTest As String
Dim strNext As String
Dim lMin As Long
Dim lMax As Long
Dim wb As Workbook
Set wb = ActiveWorkbook

filenumber = 1
strPre = "\M"
lMin = 6950
lMax = 19999

'myPath = "c:\atest" ' Set the working path -- FOR TESTING ONLY.

'original working one - myPath = "\\Mtl-vs_xchange\XCHANGE\Inter-Territorial\OUTGOING IT'S FROM JULY 04"
'myPath = "'K:\Inter-Territorial\OUTGOING IT'S FROM JULY 04"
'myPath = "\\MTL-FP1-H\MTL-XCHANGE$\Inter-Territorial\OUTGOING IT'S FROM JULY 04"

myPath = "\\Mis-fserver-c\mtl-xchange$\Inter-Territorial\OUTGOING IT'S FROM JULY 04\OUTGOING FROM JAN-01-2018" ' New Location hardcoded
'myPath = "K:\Inter-Territorial\OUTGOING IT'S FROM JULY 04\OUTGOING FROM JAN-01-2018" ' New Location

myName = ""
For filenumber = lMax To lMin Step -1
strTest = ""
On Error Resume Next
strNext = myPath & strPre & Format(filenumber, "0000000") & ".xls"
strTest = Dir(strNext)
On Error GoTo 0

If strTest <> "" Then
'found one!
If filenumber = lMax Then
MsgBox "All file numbers have been used. Count max is " & lMax & ". Please change macro."
Exit Sub
Else
myName = myPath & strPre & Format(filenumber + 1, "0000000") & ".xls"
Exit For
End If
End If
Next filenumber

'to display network location of file

'MsgBox Application.CommandBars("Web").Controls("Address:").Text

myStr = Format(filenumber + 1, "0000000") ' Format the number of existing files to 7 digits.

If myName = "" Then
myName = myPath & strPre & myStr & ".xls"
End If


'Save the file with normal options.
With wb
.ActiveSheet.Range("FileNum").Value = myStr
.SaveAs Filename:=myName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
.Close
End With

Set wb = Nothing

End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top