Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do File Open and default to the MyDocuments dialog box
How do I create a macro to do a File Open and default to the 'My Documents'
dialog box? Thank you, Steven |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do File Open and default to the MyDocuments dialog box
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 Steven wrote: How do I create a macro to do a File Open and default to the 'My Documents' dialog box? Thank you, Steven -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do File Open and default to the MyDocuments dialog box
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" wrote in message ... How do I create a macro to do a File Open and default to the 'My Documents' dialog box? Thank you, Steven |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do File Open and default to the MyDocuments dialog box
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" wrote: 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 Steven wrote: How do I create a macro to do a File Open and default to the 'My Documents' dialog box? Thank you, Steven -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do File Open and default to the MyDocuments dialog box
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 Steven wrote: 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" wrote: 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 Steven wrote: How do I create a macro to do a File Open and default to the 'My Documents' dialog box? Thank you, Steven -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
specify default path in dialog open box | Excel Programming | |||
how can I display the file location (ie:C/mydocuments..)print | Excel Discussion (Misc queries) | |||
Prompt user to select file with default file selected dialog | Excel Programming | |||
Prompt user to select file with default file selected dialog | Excel Programming | |||
Prompt user to select file with default file selected dialog | Excel Programming |