Solved

Extract folder selected from msoFiledialogFilePicker

Posted on 2011-03-24
5
755 Views
Last Modified: 2013-11-27
What I am trying to do is see what folder the user selects using the FileDialogPicker. I then want to store that value in a table so the next time they open the DB it 'remembers' the last location that they selected. I know if you remove the .InitialFileName it remembers that location per session, but I want to store it so that the next time they open the DB it goes right back to where they were.

Do I need to use a msoFileDialogFolderPicker in conjuction with this? Thanks for any tips.

 
Dim strSelectedFile As String
    
    Set fdg = Application.FileDialog(msoFileDialogFilePicker)
'    fdg.InitialFileName = "c:\"
    
    With fdg
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls"
        .Filters.Add "Excel 2007", "*.xlsx"
'        .AllowMultiSelect = True
        .InitialView = msoFileDialogViewDetails
       
        If .Show = -1 Then
            DoCmd.SetWarnings False
            DoCmd.RunSQL ("Delete * from DynamicReportTest")
            For Each vrtSelectedItem In .SelectedItems
            strSelectedFile = vrtSelectedItem
            MsgBox strSelectedFile
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "DynamicReportTest", strSelectedFile, False, , False
            
          
        
        Next vrtSelectedItem
Else
MsgBox "No File Selected"
End If
End With

Open in new window

0
Comment
Question by:G Scott
  • 3
  • 2
5 Comments
 
LVL 1

Author Comment

by:G Scott
ID: 35205514
Just thought of a solution, but still need help. If I take the whole selected file name and trim off the actual file name from the last "\" it would work. But I am not sure how to do that either. You can see in my code above  in line 18 that I am throwing up the whole file name/location to a msgbox, I can jus change that to a msgbox Trim(what do I put in here) kind of thing.  Right?
0
 
LVL 19

Accepted Solution

by:
darbid73 earned 500 total points
ID: 35205903
.SelectedItems is going to give you a path.  You need to do something like this

Some people take the path as a string and will find the "/" in it.

I use the filesystemobject instead see MSDN reference and example

You will need to add a reference to the file system object
Dim filesystem As New FileSystemObject

StripFilename = filesystem.GetParentFolderName(sPathFile) & "\"

Open in new window

0
 
LVL 1

Author Comment

by:G Scott
ID: 35206252
Hey darbid,

According to this post you can no longer assign FileSystemObjects in Access 2007:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_23589830.html

Am I looking at the right thing?
0
 
LVL 19

Expert Comment

by:darbid73
ID: 35206352
I think a fix is the solution to that

Source

Hotfix
0
 
LVL 1

Author Closing Comment

by:G Scott
ID: 35207179
Sorry darbid, your solution worked without the hotfix. I was looking for a FileSystemObject reference and not the MS Scripting reference.

Thanks for the help!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question