Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    GetOpenFilename (XP)

    I utilize the line of code (See Line A) to obtain a file name inconjunction with another line of code (See Line [img]/forums/images/smilies/cool.gif[/img] referencing another folder.

    My code work just fine but I was wondering how to retain the original path to the file selected so that the next time I loop through the code again for Line A will direct me to the correct path and I can select a different file.

    LIne A
    Application.GetOpenFilename(fileFilter:="

    Line B
    Application.FileDialog(msoFileDialogFolderPicker)

    Thanks,
    John

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

    Re: GetOpenFilename (XP)

    GetOpenFileName isn't ideal for that, I'd use Application.FileDialog(msoFileDialogOpen) instead. For example:
    <code>
    Static strPath As String
    Dim strFile As String
    Dim intPos As Integer
    With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Word documents", "*.doc"
    ' If we already set strPath...
    If Not strPath = "" Then
    '... then supply it to FileDialog
    .InitialFileName = strPath
    End If
    If .Show Then
    strFile = .SelectedItems(1)
    ' Get position of last in filename
    intPos = InStrRev(strFile, "")
    ' Set strPath for next time
    strPath = Left(strFile, intPos)
    End If
    End With</code>

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetOpenFilename (XP)

    Hans,

    I took your suggestion and have incorporated it into my code which uses a combination of "msoFileDialogOpen and msoFileDialogFolderPicker".

    The code first selects files from a source folder via "msoFileDialogOpen" and then continues on until a destination folder (path is different from source folder) is selected via "msoFileDialogFolderPicker".

    My challenge is with retaining the ".SelectedItems(1) " from the FileDialogOpen which gets overwritten with the ".SelectedItems(1)" from the FolderPicker. From what I have read, the FolderPicker has a numeric equivalent of 4 while the FileDialogOpen is 1. I would have thought that one could distinguish between the ".SelectedItems" based on the numeric value such as ".SelectedItems(4). Associating the 4 with the SelectedItems does not work.

    Thanks for your assistance,
    John

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

    Re: GetOpenFilename (XP)

    No. There is an option to allow selection of more than one file (AllowMultiSelect = True). You can then retrieve the names of all selected files by looping through the SelectedItems array.
    <code>
    With Application.FileDialog(msoFileDialogFilePicker)
    ...
    .AllowMultiSelect = True
    If .Show = True Then
    For i = 1 To .SelectedItems.Count
    MsgBox .SelectedItems(i)
    Next i
    End If
    End With
    </code>
    So .SelectedItems(4) would refer to the 4th selected file (if any).

    You must assign the name of the selected file or folder to a variable in order to preserve it.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: GetOpenFilename (XP)

    Hello John
    The following chunk of code should give you a technique for storing data between macro runs. <pre>Public Sub store()
    Dim LastFolderPath As String
    Dim f1 As Long

    f1 = FreeFile
    'Replace "filespec" with the pathname of the
    'text file holding the path you wanted to remember
    Open filespec For Input As #f1
    Input #f1, LastFolderPath
    Close #f1
    '
    '
    'Additional code
    '
    '
    f1 = FreeFile
    Open filespec For Output As #f1
    Write #f1, LastFolderPath
    Close #f1

    End Sub</pre>

    Don't forget to add some error trapping to cater for the first run when the file doesn't exist.
    H.T.H.
    Regards
    Don

Posting Permissions

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