PDA

View Full Version : saving spreadheet to network drive



mykal66
07-17-2011, 07:20 AM
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

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


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

Thanks

Bob Phillips
07-17-2011, 08:56 AM
What's the UNC path for that drive, use that in the file name.

Paul_Hossler
07-17-2011, 11:42 AM
If you have the uill path( inc. Drive Letter) you can get the UNC for each user's PC like this


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 (file://\\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


Paul

mykal66
07-18-2011, 09:43 AM
Thank you, i will try this at work tomorrow.