saving spreadheet to network drive

[mykal66]

Fairly limited knowledge so apologies for asking what is likely a simple question!

I have a spreadsheet used by up to 10 people at once which uses a button to save a copy of itself using certain cells completed by the user on the main page which works on my machine at work and saves to correct folder.

However; it only works for me. When anyone else uses it it creates the correct filename but saves it to My Documents on their own pc rather than the network drive which could result in data loss.

How can i make the file save to a default location, given that the network drive on some users PC is mapped at T: and others as R:

e.g. my pc would be

T:MPC\Teams\prep\pcdl\scanning\appscanning\july\current letters

a colleagues would be

R:MPC\Teams\prep\pcdl\scanning\appscanning\july\current letters

Even though they are same drives

The code i use is

[VBA]Sub SvMe()
Dim newFile As String
Dim fName As String
fName = Range("C5").Value & " " & Range("C3").Value & " " & "(" & Range("B16").Value
newFile = fName & " " & Format$(Date, "dd-mm-yyyy") & ")"
ActiveWorkbook.SaveAs Filename:=newFile
End Sub
[/VBA]

Again sorry for lack of knowledge, just trying to self teach at home to help out at work

Thanks

[Bob Phillips]

What's the UNC path for that drive, use that in the file name.

[Paul_Hossler]

If you have the uill path( inc. Drive Letter) you can get the UNC for each user's PC like this

[vba]
Option Explicit

'http://support.microsoft.com/kb/160529

' 32-bit Function version.
Declare Function WNetGetConnection32 Lib "MPR.DLL" Alias _
"WNetGetConnectionA" (ByVal lpszLocalName As String, ByVal _
lpszRemoteName As String, lSize As Long) As Long

Dim lpszRemoteName As String
Dim lSize As Long

' Use for the return value of WNetGetConnection() API.
Const NO_ERROR As Long = 0

' The size used for the string buffer. Adjust this if you need a larger buffer.
Const lBUFFER_SIZE As Long = 511

Sub test()
MsgBox GetNetPath("n:\folder1\folder2\filename.txt")
End Sub

Function GetNetPath(LocalPath As String) As String

Dim DriveLetter As String
Dim cbRemoteName As Long, lStatus As Long, iNull As Long

DriveLetter = Left(LocalPath, 2)

' Specifies the size in characters of the buffer.
cbRemoteName = lBUFFER_SIZE

' Prepare a string variable by padding spaces.
lpszRemoteName = lpszRemoteName & Space(lBUFFER_SIZE)

' Return the UNC path (\\Server\Share).
lStatus = WNetGetConnection32(DriveLetter, lpszRemoteName, cbRemoteName)

' Verify that the WNetGetConnection() succeeded. WNetGetConnection()
' returns 0 (NO_ERROR) if it successfully retrieves the UNC path.
If lStatus = NO_ERROR Then
iNull = InStr(lpszRemoteName, Chr(0))
GetNetPath = Left(lpszRemoteName, iNull - 1) & Right(LocalPath, Len(LocalPath) - 2)

Else
' Unable to obtain the UNC path.
GetNetPath = vbNullString

End If
End Function
[/vba]

Paul

[mykal66]

Thank you, i will try this at work tomorrow.