Find file path on users system.

[Alpacino]

Hi there,

I have an excel doc that opens a file within their system. E.g. Finance2011.xls but the macro needs to know the destination of the file. At the moment I have manually put the file path on each system individually. I'd there anyway I can write a code to insert the systems file path into the open code line
E.g
Workbook.open("File:///\\system34\mydocuments\Finance2011.xls")
So the code will find the ("File:///\\system34\mydocuments\" part

 

[Scott Huish]

Why are you using File:///?

Are you wanting to use UNC? Unless you have changed it, My Documents is usually on the users system.

 

[Alpacino]

Hi thanks for reply I don't know why I use the File:/// bit. I just know it works with that.the users are apart of a networksybe it's due to that I think this apart never changes I just want the \\\mydocuments\ part.

 

[Alpacino]

Anyone help???

 

[Scott Huish]

Private Declare Function SHGetSpecialFolderPath _
   Lib "shell32.dll" _
   Alias "SHGetSpecialFolderPathA" _
   (ByVal hWnd As Long, _
   ByVal lpszPath As String, _
   ByVal nFolder As Integer, _
   ByVal fCreate As Boolean) As Boolean

Private Const CSIDL_PERSONAL = &H5

Function GetMyDocuments()
Dim blnReturn As Long
Dim strBuffer As String
strBuffer = Space(255)
blnReturn = SHGetSpecialFolderPath(0, _
   strBuffer, _
   CSIDL_PERSONAL, _
   False)
   
strBuffer = Left(strBuffer, InStr(strBuffer, Chr(0)) - 1)
GetMyDocuments = strBuffer
End Function

Sub TEST()
MsgBox "My Documents Location is: " & GetMyDocuments
End Sub

 

[Alpacino]

Thanks this works a treat.
Anyway of finding out where a certain file name is on ur system ??
Eg finance.xls I'd in 2011 folder