Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Opening selected Folder (Excel 2002 SP3)

    Can anyone please help with the following code chunk?
    I am trying to get the "File Open" dialog box to open up in the correct folder with out the user having to navigate there way thru the server to the correct folder.
    Most things seems to work except for the fact that the system does not open up in the correct folder! Also I can not get the dialog box to default to a file selection property of DM*.TXT
    Every time I try to get the file pattern to be something other than *.TXT it seems to no work! any idea?

    Within Excel, a custom property is setup as follows:
    Name = Reports_Folder
    Value = P:MA600HBReports


    Code is:...
    Function Select_File_to_Open() As String
    'Opens dialog box to allow user to pick relevant report file.
    'Returns name of file to open

    Dim FileName As String 'variable to hold name of file
    Dim Path As String 'stores path name

    'read in the default file path for the data file from file properties
    Path = Application.ActiveWorkbook.CustomDocumentPropertie s("Reports_Folder")

    ChDir Path 'swap to the correct path for the reports
    Select_File_to_Open = Application.GetOpenFilename("Text Files (*.txt), *.txt")

    End Function

  2. #2
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Re: Opening selected Folder (Excel 2002 SP3)

    Damn that was great!
    Just what I was chasing! Only had to correct the assignment of the Path to strPath and away it went!
    Thanks so much for your prompt and accurate response Hans.
    Regards
    Brian.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening selected Folder (Excel 2002 SP3)

    In Excel 2002 and higher, you can use Application.FileDialog, this lets you specify the initial path and filename:

    Function Select_File_to_Open() As String
    'Opens dialog box to allow user to pick relevant report file.
    'Returns name of file to open

    Dim strPath As String 'stores path name

    'read in the default file path for the data file from file properties
    strPath =ActiveWorkbook.CustomDocumentProperties("Reports_ Folder")
    If Not (Right(strPath, 1) = "") Then
    strPath = strPath & ""
    End If

    With Application.FileDialog(msoFileDialogOpen)
    .Filters.Add "Text Files", "*.txt"
    .InitialFileName = strPath & "DM*.txt"
    If .Show = True Then
    Select_File_to_Open = .SelectedItems(1)
    End If
    End With
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •