Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127

    Import ALL FileDialog selected Database objects from a folder to the database

    Hi all in access forums.net
    Good day. I am stuck here with this coding. I am using this function plus this code behind a forms button. The form has just only this control. When I click the button it opens mso filedialog picker and I select the files I want to import. It Imports One of the selected files nicely.
    Can somebody please see this code and help me to import all selected files not just one file. Hope this is possible. Here is the function at the top of the button in code window and the code behind the single button on form.
    Thanks


    Please see the code
    Code:
    Option Compare Database
    Function FileNameNoExt(strPath As String) As String
    ' get file name without extension
    'x = FileNameNoExt("C:\Users\shiham\Desktop\New\FileDetail.txt")
    'MsgBox x
        Dim strTemp As String
        strTemp = Mid$(strPath, InStrRev(strPath, "\") + 1)
        FileNameNoExt = Left$(strTemp, InStrRev(strTemp, ".") - 1)
    End Function
    Private Sub Command11_Click()
    Dim F As Object
        Dim xfl As String
        Dim strFile As String
        Dim strFolder As String
        Dim varItem As Variant
        Set F = Application.FileDialog(msoFileDialogFilePicker)
        '-------------------------
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True   'Adjust the File Picker to allow multiple files to be selected
        .Title = "Locate a file to attach"
        .ButtonName = "Choose"
        .Filters.Clear
        .Filters.Add "All Files", "*.*"
        .InitialFileName = "C:\"
        .InitialView = msoFileDialogViewThumbnail
        
            If .Show = 0 Then
            Exit Sub
            End If
            '=============================
           
            For Each varItem In F.SelectedItems
                'OnlystrFile =
                '------------------------
                
                '-------------------------
                strFile = Dir(varItem)
                strFolder = Left(varItem, Len(varItem) - Len(strFile))
                '-------------------
                'x = FileNameNoExt("C:\Users\shiham\Desktop\New\FileDetail.txt")
                x = FileNameNoExt(Left(varItem, Len(varItem) - Len(strFile)) & Dir(varItem))
                   'MsgBox "xxx: " & x
                '-----------------------
                xfl = x
            Next
        
        Set F = Nothing
            '=================================
        
            
            Dim varFile As Variant
            
                
                For Each varFile In .SelectedItems
                
                Debug.Print varFile 'You will have to nest your code block(s) here (before NEXT) to attach each file
                '-----------------------------------------------------------------------
                'MsgBox "FullName: " & varFile
                'MsgBox "Folder: " & strFolder
                'MsgBox "file: " & strFile
                'MsgBox "OnlystrFileWithoutDotExt: " & OnlystrFileWithoutDotExt
                
                '---------------------------
                
               Application.LoadFromText acForm, xfl, varFile
                 'MsgBox "file: " & strFile
                'MsgBox "xfl: " & xfl
                '----------------------------------------------------------------------------
                Next varFile
                
    End With
    End Sub
    Last edited by aspen; 03-22-2014 at 10:21 AM.

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    I did not review your code. Here is code of a very similar requirement which may help:

    'WillR - www.willr.info (December 2004)
    'Macro Loops through the specified directory (strPath)
    'and links ALL Excel files as linked tables in the Access
    'Database.

    Const strPath As String = "C:\Users\Excel\Desktop\Coding\Microsoft Access\HSBC\Auto_Link_Excel_Files\" 'Directory Path
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File Array
    Dim intFile As Integer 'File Number

    'Loop through the folder & build file list
    strFile = Dir(strPath & "*.xlsx")
    While strFile <> ""
    'add files to the list
    intFile = intFile + 1
    ReDim Preserve strFileList(1 To intFile)
    strFileList(intFile) = strFile
    strFile = Dir()
    Wend
    'see if any files were found
    If intFile = 0 Then
    MsgBox "No files found"
    Exit Sub
    End If
    'cycle through the list of files & link to Access
    For intFile = 1 To UBound(strFileList)
    DoCmd.TransferSpreadsheet acLink, , _
    strFileList(intFile), strPath & strFileList(intFile), True
    'Check out the TransferSpreadsheet options in the Access
    'Visual Basic Help file for a full description & list of
    'optional settings
    Next
    MsgBox UBound(strFileList) & " Files were Linked"

  3. #3
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Hi Ntc thanks. For my code I have to get too things. The file name with extension and the file name without extension. The function "FileNameNoExt" is to return the file name without extension. When I pres the button and select many file from filedialog . It imports just one file. Maybe Its looping that's needed. I am very bad at vba. So Its either the last
    Application.LoadFromText acForm, xfl, varFile Is not running for each selected item or the xfl is not updated for each varFile. Please help

  4. #4
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Application.LoadFromText acForm, xfl, varFile. Xfl is the file name without extention returned from the function "FileNameNoExt". VarFile is the full path from filedialog selected items. So for each selected items there has to be an xfl and varFile. And "Application.LoadFromText acForm, xfl, varFile" has to run for each selected item. Now its running for one file from the selected items. Either the code is not running for each selected item or its not getting the xfl name for each varFile. Thanks

  5. #5
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Hi All please note these are Data base objects exported as text files that I am trying to import back to an empty database.
    Thanks please help

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Maybe if you consider this comment
    https://www.accessforums.net/program...tml#post213338

    and this code example
    https://www.accessforums.net/program...tml#post213660

    The thread's topic is not spot on your issue but I believe parts are relevant. I may have other examples if you are still stuck.

  7. #7
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Hi Itsme. Thanks for your help. I was using only your code from the previous thread thanks for that. yeah it has to be something like Iterate .SelectedItems(). I don't know how to ut the for I = 0 through. and we also has to get the file without extension value for each selected item. Can you please test the code by exporting some objects to a folder and import it using my code. Maybe then you can fix it. I used this code to export them http://www.access-programmers.co.uk/...ad.php?t=99179. Please help

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I was not aware I linked you to one of your own threads as reference. The code you linked to in post #7 is about exporting objects from Access. I do not see the connection to your original question. The following code is an example of multiple files being selected using the file dialogue.

    Are you trying to select multiple files using the file dialogue?

    What do you want to do with the files selected?

    Code:
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True   'Adjust the File Picker to allow multiple files to be selected
        .Title = "Locate a file to attach"
        .ButtonName = "Choose"
        .Filters.Clear
        .Filters.Add "All Files", "*.*"
        .InitialFileName = "C:\"
        .InitialView = msoFileDialogViewThumbnail
        
            If .Show = 0 Then
            Exit Sub
            End If
        
            'We will look for multiple files selected by the user
            Dim varFile As Variant
                For Each varFile In .SelectedItems
                'You can view the debug results in the immediate window (Keyboard shortcut Ctrl+G)
                Debug.Print varFile 'You will have to nest your code block(s) here (before NEXT) to attach each file
                Next
    On Error Resume Next
    End With
    Me.SetFocus

  9. #9
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Thanks Its me please see my code above. I am using this code to select multiple files and to import the selected files to a blank database which has one form and the code behind its one button. I have set multipleselect to true but some how its only importing only one file all though I select more than one. I am using the filedialog to select files so it can give the nessesary information to this code "Application.LoadFromText acForm, "YourFormName", "C:\Temp\Form_frmTest.txt". "YourFormName", "C:\Temp\Form_frmTest.txt". .
    "YourFormName", = The name of the file in folder without.txt. To extract that I use the function in the above code "FileNameNoExt". Then "C:\Temp\Form_frmTest.txt". The full path to folder which is in each VarFile.
    Application.LoadFromText acForm, xfl, varFile - Xfl gets the filename from the VarFile Without the .txt from the selected items using the function. so its the form name when the .txt is omited. and var file the full path. Using this code I am able to import on form. I want to import just all the forms I select. Please see the full code at the top of this thread.
    Well As the code works success fully to import one form I guess it should work for more than one too. But I can't figure out.
    Thanks Itsme.

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    What do you want to do with the files selected?

    Why are you using LoadFromText?

  11. #11
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Thanks. I have exported some forms as text files to a folder. So I want import them back to a new blank database. Its difficult to import one at a time when you have twenty or so. And I gues this is possible but I can't figure out how

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    This seems to be working. I do not have any forms to import to test it but should do the trick.... EDIT: you will still need your function too.


    Code:
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True   'Adjust the File Picker to allow multiple files to be selected
        .Title = "Locate a file to attach"
        .ButtonName = "Choose"
        .Filters.Clear
        .Filters.Add "All Files", "*.*"
        .InitialFileName = "C:\"
        .InitialView = msoFileDialogViewThumbnail
        
            If .Show = 0 Then
            Exit Sub
            End If
        
            'We will look for multiple files selected by the user
            Dim varFile As Variant
                For Each varFile In .SelectedItems
                    x = FileNameNoExt(Left(varFile, Len(varFile) - Len(strFile)))
                    
    '                Debug.Print "x = " & x
    '                Debug.Print "varFile = " & varFile
        
                    Application.LoadFromText acForm, x, varFile
                Next
    On Error Resume Next
    End With
    Me.SetFocus

  13. #13
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    Just curious why you are doing this?

  14. #14
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Code:
    Dim fs As Object
    Dim oldPath As String, newPath As String
    oldPath = "C:\Users\Shiham\Documents\AccessAspen3\Repair\New project simplify aspen" 'Folder file is located in
    newPath = "C:\Users\Shiham\Documents\AccessAspen3\Repair\New project simplify aspen\Backups" 'Folder to copy file to
    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.CopyFile oldPath & "\" & "Clean.Accdb", newPath & "\" & "Clean_backup" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hh-mm-ss") & ".accdb"
    Set fs = Nothing
    MsgBox "Backup was successful and saved "
    Hi Its me thank you so much. I just don't know how to thank you for this. It works seamless. thanks. So glad you are allways able to help. I would never have accomplished this without your help. And Hi trevor 40 glad you asked. Its because I frequently go on redesigning the project cause I have not begun with the end in mind cause I am just learning and access freak. so I read some where that if you export your objects as text and import back there is some tendency for anti corruption. well I am not sure if its true. I use this code to do a normal back up. But I am thinking of changing to save as text method instead. Do you think this is a good I dea. But Then loadind from text method for table is not working for me but I think I can fix it some how like maybe this link.
    http://gainingaccess.net/GainingAcce...SaveAsText.htm
    Thank you all In this forum for your continuous cooperation and help

  15. #15
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Glad it is working. I struggled to comprehend the objective even though your posts were explaining it rather well. The code you were trying had a lot of extraneous left, instr, etc. functions. I removed a lot of them and may still be able to refactor it some more.

    The idea of storing objects as text has sparked my interest. Although I normally store objects using mdb or accdb files, I am curious what a text file looks like.

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

Similar Threads

  1. Can't save database objects when changes made
    By stanley721 in forum Access
    Replies: 8
    Last Post: 10-12-2015, 08:51 PM
  2. FileDialog pass starting Folder as a variable
    By bbrazeau in forum Programming
    Replies: 3
    Last Post: 09-13-2013, 07:23 AM
  3. Get objects selected in the database window
    By jptros in forum Programming
    Replies: 2
    Last Post: 08-02-2012, 04:39 PM
  4. Replies: 5
    Last Post: 05-17-2012, 06:23 PM
  5. Cannot import objects from secured database
    By focosi in forum Security
    Replies: 2
    Last Post: 09-10-2011, 02:33 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