Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    skydivetom is online now Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    832

    XLS Data Import -- Would like to use the "Browse Folder" (vs. "strPath = "C: ....)"

    Hello:



    Please find attached a DB containing a form "frmImportRoutine". Also, in the subfolder, I've included two XLS for import purposes.

    When clicking on the list menu item (in form), the two XLS sheets are properly imported correctly. Presently though, I hard coded the XLS's subfolder location (see line below):

    Code:
        
    strPath = "C:\Users\tb_pu\Desktop\SourceData\"
    How do I need to modify the VBA code so that I can utilize the "Browse Folder" and then locate the folder w/ the XLS (vs. the hard coded strPath)?

    Thank you,
    Tom
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,603
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,047
    put this in a module,
    Code:
    Public Function UserPick1File(ByVal pvFilter, Optional pvPath)
    Dim strTable As String
    Dim strFilePath As String
    Dim sDialogMsg As String, sDecr  As String, sExt As String
    'CONST msoFileDialogFilePicker = 3
    'CONST msoFileDialogSaveAs = 2
    'CONST msoFileDialogViewList = 1
    
    
    If IsMissing(pvPath) Then pvPath = "c:\temp\"
    
    
    With Application.FileDialog(3)   'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
        .AllowMultiSelect = True
        .Title = sDialogMsg   ' "Locate a file to Import"
        .ButtonName = "Import"
        .Filters.Clear
             '.Filters.Add "Access Files", "*.accdb;*.mdb"
        .Filters.Add "Excel Files", "*.xlsx"
        .Filters.Add "All Files", "*.*"
        .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

    usage:
    Dim sFile As String
    sFile = UserPick1File("c:") 'chg default folder

    if sFile <>"" then docmd.TransferSpreadsheet acImport ,acSpreadsheetTypeExcel12 ,sTable, sFile, true

  4. #4
    skydivetom is online now Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    832
    ranman256:

    Thank you for the feedback. As suggested, I copied the VBA into a module. In my ListboxAfterUpdate, I then added the 3 lines as provided.

    Presently, however, I'm getting a runtime error. See attached JPG and ACCDB.

    Did I miss anything? Any additional feedback would be greatly appreciated. Thank you.

    Tom
    Attached Thumbnails Attached Thumbnails RunTimeError.JPG  
    Attached Files Attached Files

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    1,001
    might be the pvfilter argument
    Code:
    Public Function UserPick1File(ByVal pvFilter, Optional pvPath)
    I dont see that it is used in the procedure or ranman's useage example.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  6. #6
    skydivetom is online now Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    832
    moke123 -- thanks for chiming in... I appreciate it.

    Unfortunately, I end up w/ the same error once I remove the pvFilter reference in the arguments line.

    Any additional thoughts?

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,603
    Ranman's example uses "C:" for that parameter.

    Ranman's code works fine for me?

    Have you added the Office reference as dictated in the comments?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  8. #8
    skydivetom is online now Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    832
    Welshgasman (and Ranman):

    Oh my... devil is in the detail. I just added the Office reference... sorry for that. So, yes, the Browse dialogue box pops up now.

    However, once I select multiple files and then click "Import" I'm now getting a run-time error. See attached.

    Is there any reference that I should have checked (besides "Microsoft Office 16.0 Object Library")?

    Thank you,
    Tom
    Attached Thumbnails Attached Thumbnails RunTime Error.JPG  

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,603
    I *think* the clue might be in the name of the function?

    No idea as to why MultiSelect is True in that case?

    https://docs.microsoft.com/en-us/off...lowmultiselect
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  10. #10
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    1,001
    Quote Originally Posted by Welshgasman View Post
    Ranman's example uses "C:" for that parameter.

    Ranman's code works fine for me?

    Have you added the Office reference as dictated in the comments?
    I have a procedure similiar to Ranman's.
    If you notice he has 3 lines commented out

    Code:
    'CONST msoFileDialogFilePicker = 3
    'CONST msoFileDialogSaveAs = 2
    'CONST msoFileDialogViewList = 1
    If you un-coment them, the procedure becomes late binding and no refernce is needed.

    here's the version I have

    Code:
    Public Function fFileDialog(Optional FileName = "", Optional FolderOnly As Boolean = False) As String
    'Does NOT require reference to Microsoft Office Object Library MSO.DLL
    
    
        Const msoFileDialogFolderPicker = 4
        Const msoFileDialogFilePicker = 3
        Const msoFileDialogViewDetails = 2
    
    
        Dim fd As Object
        Dim varItems As Variant
        Dim flPath As Variant
    
    
        If FolderOnly Then
    
    
            Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    
        Else
    
    
            Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    
        End If
    
    
        With fd
    
    
            .AllowMultiSelect = True
            .InitialView = msoFileDialogViewDetails
            .InitialFileName = FileName
    
    
            If .Show = True Then
                For Each varItems In .SelectedItems
                    flPath = varItems
                Next
            End If
    
    
        End With
    
    
        fFileDialog = flPath
    
    
    End Function
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  11. #11
    skydivetom is online now Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    832
    moke123... hmh, I end up w/ the same error: "The action or method requires a Table Name argument".

    If yours is working, would you mind posting your example?

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,603
    Walk though the code in the debug window.
    Is suspect that is coming from your code where you are supplying more than your code expects?

    Also you could press Debug button a SEE what line produces the error.?
    That always helps.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  13. #13
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    1,001
    Quote Originally Posted by skydivetom View Post
    moke123... hmh, I end up w/ the same error: "The action or method requires a Table Name argument".

    If yours is working, would you mind posting your example?
    My comments were about the FileDialog procedure only.

    The error you are getting, I believe, is from the transferspreadsheet procedure

    per ranman's usage suggestion:

    Code:
    usage:
    Dim sFile As String
    sFile = UserPick1File("c:") 'chg default folder
    
    if sFile <>"" then docmd.TransferSpreadsheet acImport ,acSpreadsheetTypeExcel12 ,sTable, sFile, true
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  14. #14
    skydivetom is online now Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    832
    I believe I used exactly those 3 lines. See attached sample DB v02.

    If you can think of anything else, would love to learn more about it. Thank you.
    Attached Files Attached Files

  15. #15
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,603
    Quote Originally Posted by skydivetom View Post
    I believe I used exactly those 3 lines. See attached sample DB v02.

    If you can think of anything else, would love to learn more about it. Thank you.
    Time to start learning.

    That function should only return ONE filename, not multiple filenames. That is what it is complaining about.?

    If you want to select a bunch of files in one go then you need a slightly different approach.

    Regardless, you still need to understand what the code is doing?, and in this case that does not appear so?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple table relationships ("faces" to "spaces" to "chairs")
    By skydivetom in forum Database Design
    Replies: 36
    Last Post: 07-20-2019, 01:49 PM
  2. Replies: 12
    Last Post: 10-01-2018, 02:40 PM
  3. Replies: 13
    Last Post: 12-12-2016, 12:26 AM
  4. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  5. Replies: 1
    Last Post: 08-23-2012, 08:32 AM

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