[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 825
  • Last Modified:

Extract folder selected from msoFiledialogFilePicker

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
G Scott
Asked:
G Scott
  • 3
  • 2
1 Solution
 
G ScottAuthor Commented:
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
 
darbid73Commented:
.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
 
G ScottAuthor Commented:
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
 
darbid73Commented:
I think a fix is the solution to that

Source

Hotfix
0
 
G ScottAuthor Commented:
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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now