Please help with relative file path syntax??

[Rick]

I just can't remember how to refer to a relative path in VB.
I want to open the "Summary.xls" workbook from another workbook using a
command button but I want the command to work for multiple users from their
"My Documents" folder and not mine.

Presently I'm using the code as follows but of course it always opens MY
Summary.xls workbook and not the Summary.xls workbook associated with each
user.
Workbooks.Open Filename:= _
"C:\Documents and Settings\Rick\My Documents\House\Summary.xls"

Can someone remind me of the relative path syntax to open a file from a "My
Documents" subfolder?


I would also like the Summary.xls workbook to open with the cell pointer
positioned at a particular cell. One different than that which was saved.
Can this be done? I could write code in the Summary.xls workbook but it
can't be autoexec code because the particular cell can be one of three
possibilities. I'll have to define which code is run from the opening
workbook.

Thanks VERY much for any help or suggestions you may offer.

Rick

 

[Guest]

Do you mean the users default file path. If so, Application.DefaultFilePath
will return that path.
Your other question. Can you save the cell address that you want to return
to in another cell on the worksheet using a sub which runs on close (either
Auto_Close or a close event) . You can then use the data from this cell in an
Auto_Open sub or open event.

and use this value in sub which runs on open

 

[Guest]

I added these examples to my library which I have been compiling so I thought
that I would post them in case they are of further help to you. I have used
Auto_Open and Auto_Close because that is what you included in your post but I
would personally use events but Auto_Open and Auto_Close still work well.

Dim shtName
Dim celAddr
Dim userDefaultPath
Dim fileToOpen

Sub Auto_Open()
'On opening the workbook, selects the sheet name
'saved during last close in cell AA1 on Sheet1.
'Then selects the cell address saved during last close
'in cell AA2 on Sheet1.
'On Error Resume Next 'In case the address not saved.

shtName = Sheets("Sheet1").Range("AA1")
celAddr = Sheets("Sheet1").Range("AA2")
Sheets(shtName).Select
Range(celAddr).Select
'On Error GoTo 0 'Cancel On Error
End Sub

Sub Auto_Close()
'Copies the active sheet name to cell AA1 on Sheet1
'Copies the active cell address on the active sheet
'to cell AA1 on Sheet1.
'Saves the workbook before closing.

shtName = ActiveSheet.Name
celAddr = ActiveCell.Address
Sheets("Sheet1").Range("AA1") = shtName
Sheets("Sheet1").Range("AA2") = celAddr
ActiveWorkbook.Save
End Sub

Sub User_Default_Path()
'Finds the users default filepath and then
'concatenates this with the required filename
'and then opens the file.
'(Don't forget the back slash at beginning of filename.)

userDefaultPath = Application.DefaultFilePath
fileToOpen = userDefaultPath & "\Summary.xls"
Workbooks.Open Filename:=fileToOpen

End Sub

 

[Tom Ogilvy]

This will work in Windows 2000 and later:

Option Explicit

Private Const S_OK = &H0 ' Success
Private Const S_FALSE = &H1 ' The Folder is valid, but does not
exist
Private Const E_INVALIDARG = &H80070057 ' Invalid CSIDL Value

Private Const CSIDL_LOCAL_APPDATA = &H1C&
Private Const CSIDL_PERSONAL = &H5&
Private Const CSIDL_FLAG_CREATE = &H8000&

Private Const SHGFP_TYPE_CURRENT = 0
Private Const SHGFP_TYPE_DEFAULT = 1
Private Const MAX_PATH = 260

Private Declare Function SHGetFolderPath Lib "shfolder" _
Alias "SHGetFolderPathA" _
(ByVal hwndOwner As Long, ByVal nFolder As Long, _
ByVal hToken As Long, ByVal dwFlags As Long, _
ByVal pszPath As String) As Long

Private Sub GetMyDocuments(s As String)
Dim sPath As String
Dim RetVal As Long

' Fill our string buffer
sPath = String(MAX_PATH, 0)

RetVal = SHGetFolderPath(0, CSIDL_PERSONAL, 0, SHGFP_TYPE_CURRENT, sPath)

Select Case RetVal
Case S_OK
' We retrieved the folder successfully

' All C strings are null terminated
' So we need to return the string upto the first null character
sPath = Left(sPath, InStr(1, sPath, Chr(0)) - 1)
s = sPath
Case S_FALSE
' The CSIDL in nFolder is valid, but the folder does not exist.
' Use CSIDL_FLAG_CREATE to have it created automatically
'MsgBox "The folder does not exist"
s = ""
Case E_INVALIDARG
' nFolder is invalid
'MsgBox "An invalid folder ID was specified"
s = ""

End Select
End Sub

--------------------------------
Usage
---------------------------------

Dim sPathMyDocuments as String
GetMyDocuments sPathMyDocuments
if sPathMyDocuments <> "" then
msgbox "The path is " & sPathMyDocuments
else
msgbox "Not found"
End if

 

[Gary Keramidas]

you should be able to use something like this:

fPath = Environ("Userprofile ") & "\My Documents\House\"