Do File Open and default to the MyDocuments dialog box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steven - 19 Mar 2008 18:38 GMT

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

Thank you,

Steven

Reply to this Message

Dave Peterson - 19 Mar 2008 20:13 GMT

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

> How do I create a macro to do a File Open and default to the 'My Documents'
> dialog box?
>
> Thank you,
>
> Steven

Signature

Dave Peterson

Reply to this Message

Steven - 19 Mar 2008 21:21 GMT

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

> One way:
>
[quoted text clipped - 38 lines]
> >
> > Steven

Reply to this Message

Dave Peterson - 19 Mar 2008 22:28 GMT

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

> 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
[quoted text clipped - 53 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Chip Pearson - 19 Mar 2008 20:36 GMT

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

Signature

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

> How do I create a macro to do a File Open and default to the 'My
> Documents'
[quoted text clipped - 3 lines]
>
> Steven

Reply to this Message