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
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