Results 1 to 3 of 3
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Using FileDialog To Capture Selected Name

    I am using FileDialog to import a workbook into my access database. Is it possible to capture the name of the selected workbook from the FileDialog, and name the table the same as the workbook name?



    This is the syntax I use to import
    Code:
    Dim f As Object
    
    
        Set f = Application.FileDialog(3)
        
        With f
            .AllowMultiSelect = False
            .Title = "Please Select the Excel File To Import"
            .Filters.Add "Excel Files", "*.xlsx"
    
    
            If .Show = True Then
                For i = 1 To f.SelectedItems.Count
    				
    			Next i
    		End If
    	End With

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    vFile = f.SelectedItems(1)

    I use this:

    usage:
    vFile = UserPick1File("c:\folder")


    Code:
    Public Function UserPick1File(pvPath)
    Dim strTable As String
    Dim strFilePath As String
    Dim sDialog As String, sDecr  As String, sExt As String
    
    
    If IsMissing(pvPath) Then pvPath = "c:\"
    
    
    With Application.FileDialog(msoFileDialogFilePicker)   'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
        .AllowMultiSelect = False
        .Title = "Locate a file to Import"
        .ButtonName = "Import"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls"
        .InitialFileName = pvPath
        .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
    
    
            If .show = 0 Then
               'There is a problem
               Exit Function
            End If
    
    
        'Save the first file selected
        UserPick1File = Trim(.SelectedItems(1))
    End With
    End Function

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The "For...Next" loop is for when "AllowMultiSelect" is TRUE.

    Consider this:
    Code:
        Dim f As FileDialog
        Dim strTable As String
        Dim strPathFile As String
        Dim blnHasFieldNames As Boolean
        Dim StartPos As Integer
        Dim LenOfName As Integer
    
        strPathFile = vbNullString
        strTable = vbNullString
        blnHasFieldNames = True
    
        'must nave a reference to Microsoft Office XX.0 Object Library (for A2010, XX = 14)
        Set f = Application.FileDialog(msoFileDialogFilePicker)
        With f
            .Title = "Please Select the Excel File To Import"
            .AllowMultiSelect = False
            .InitialFileName = "some folder path"
            .InitialView = msoFileDialogViewList
            .Filters.Clear
            .Filters.Add "Excel Files", "*.xlsx", 1
            If .Show Then
                strPathFile = .SelectedItems(1)
            End If
        End With
    
        Set f = Nothing
    
        'is file selected???
        If Len(Trim(strPathFile & "")) > 0 Then
            ' get table name
            StartPos = InStrRev(strPathFile, "\") + 1
            LenOfName = InStr(strPathFile, ".") - StartPos
            strTable = Mid(strPathFile, StartPos, LenOfName)
    
            '        MsgBox strTable
            'import
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames
    
            MsgBox "Imported!"
        End If
    Note: Should add error handling code!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. FileDialog filter add
    By vicsaccess in forum Programming
    Replies: 2
    Last Post: 06-02-2016, 08:25 PM
  2. Replies: 1
    Last Post: 03-07-2016, 07:11 AM
  3. Replies: 43
    Last Post: 03-27-2014, 01:51 AM
  4. FileDialog Question
    By uaguy3005 in forum Programming
    Replies: 5
    Last Post: 06-27-2012, 02:01 PM
  5. FileDialog
    By Tomfernandez1 in forum Programming
    Replies: 9
    Last Post: 04-13-2011, 03:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums