Do File Open and default to the MyDocuments dialog box

[Steven]

How do I create a macro to do a File Open and default to the 'My Documents'
dialog box?

Thank you,

Steven

 

[Dave Peterson]

One way:

Option Explicit
Sub testme()

Dim myDocumentsPath As String
Dim wsh As Object
Dim myFileName As Variant
Dim CurPath As String
Dim wkbk As Workbook

'save the existing current directory
CurPath = CurDir

Set wsh = CreateObject("WScript.Shell")
myDocumentsPath = wsh.SpecialFolders.Item("mydocuments")

'change to the one you want
ChDrive myDocumentsPath
ChDir myDocumentsPath

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

'change back to the old directory
ChDrive CurPath
ChDir CurPath

If myFileName = False Then
Exit Sub
End If

'do what you want--open the file???
set wkbk = workbooks.open(filename:=myfilename)

End Sub

 

[Chip Pearson]

You can use the code at http://www.cpearson.com/Excel/SpecialFolders.aspx to
get the name of the current user's My Document folder (actual name and
location varies by locale and operating system version). One you have that,
you can use ChDrive and ChDir to set the default directory to that folder
and then call GetOpenFileName.

Dim MyDocsFolderName As String
Dim SaveDir As String
Dim FName As Variant

SaveDir = CurDir
' GetSpecialFolder at http://www.cpearson.com/Excel/SpecialFolders.aspx
MyDocsFolderName = GetSpecialFolder(CSIDL_PERSONAL)
ChDrive MyDocsFolderName
ChDir MyDocsFolderName
FName = Application.GetOpenFilename()
ChDrive SaveDir
ChDir SaveDir


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Steven]

Thank you Dave. One thing. I put a button on the formatting bar that runs a
macro in a file that only holds macros. At the end of this marco I have a
command to close the macro file w/o saving. But, if I hit the Cancel button
on the Open File Dialog box the macro command to close the macro file is not
processes. How do I still close the macro file even if the Cancel button is
clicked on the Open File Dialog box.

Thank you,

Steven

 

[Dave Peterson]

This portion:

If myFileName = False Then
Exit Sub
End If

'do what you want--open the file???
set wkbk = workbooks.open(filename:=myfilename)

End Sub

needs to be replaced with:

If myFileName = False Then
'do nothing
else
'do what you want--open the file???
set wkbk = workbooks.open(filename:=myfilename)
end if

thisworkbook.close savechanges:=false

End Sub