Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Carouser is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Location
    Melbourne, Australia
    Posts
    56

    File Dialog to Rename Folder & File and send to another folder

    I've been researching FSO and fileDialog and coming up a bit short in putting together code as it is a little beyond my experience level in regards to what I want it to do. Information is a bit scarce on this topic, so I hope you can help. There are so many variations and ways people do things, it's not easy to piece the bits I need together to suit my needs



    I want to open a dialog box from a record on a form so the operator can search for The Presenter's file (.PPT*) on their local Desk top and rename from the field "NamePath" on the form. If the file has video attachments, I would need to save the selected PPT file and also the folder that would contain all files. I have already used Folder Picker to copy path addresses, but this is a bit more complicated for me. Would it work like select the file, click SaveAs, and it renames the file keeping it's extension, then selecting the Folder and click SaveAS to change the Folder name? This is what I imagine will happen.

    After the names have been changed I want to send a copy to another folder, the address is from the field "FullPath" on my form.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, that can all be done. As you already discovered, it can be a bit complicated. I suggest you take one step at a time. Get code working for one aspect before expanding to the next.

    Start with an outline of all the steps then rough out some pseudocode logic then try actual code.

    Write simple independent test code with static values for each feature, such as copying a folder, then work into using variables, then work out code integration.

    First, get the FileDialog working to get the file/folder name, then move on to concatenating with 'NamePath', then the copy from/to code, etc.

    As you develop code and encounter issue, post specific question and code for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Carouser is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Location
    Melbourne, Australia
    Posts
    56
    I've gone back to basics and have no end of trouble just by trying to use the MSDN examples. http://msdn.microsoft.com/en-us/libr.../ff196794.aspx

    I get an error on the first declaration "Dim fDialog As Office.FileDialog"
    I have MS Office Object Library 14. loaded
    Code:
    Private Sub cmdFileDialog_Click()        ' Requires reference to Microsoft Office 11.0 Object Library.       Dim fDialog As Office.FileDialog     Dim varFile As Variant
    I found some code that will work to open the Dialog
    Code:
    Private Sub cmdFileDialog_Click()
     Dim objDialog As Object
     Dim varFile As Variant
     
        ' Clear listbox contents.
       Me.FileList.RowSource = ""
    
    Set objDialog = Application.FileDialog(3)
    The problem with the above is that I have to use =Application.FileDialog(3) . If I use =Application.FileDialog(msoFileDialogFilePicker), I get an error.

    And finally, I get an error after I make a file selection in the Dialog. I set up a form with a listBox named "FileList"

    Code:
    Private Sub cmdFileDialog_Click()
     Dim objDialog As Object
     Dim varFile As Variant
     
        ' Clear listbox contents.
       Me.FileList.RowSource = ""
    
    Set objDialog = Application.FileDialog(3)
    
    With objDialog
        .AllowMultiSelect = True
        ' Set the title of the dialog box.
          .Title = "Please select one or more files"
     
          ' Clear out the current filters, and add our own.
          .Filters.Clear
          .Filters.Add "Access Databases", "*.MDB"
          .Filters.Add "Access Projects", "*.ADP"
          .Filters.Add "All Files", "*.*"
     
          ' Show the dialog box. If the .Show method returns True, the
          ' user picked at least one file. If the .Show method returns
          ' False, the user clicked Cancel.
          If .Show = True Then
     
             'Loop through each file selected and add it to our list box.
             For Each varFile In .SelectedItems
            Me.FileList.AddItem varFile
             Next
     
          Else
             MsgBox "You clicked Cancel in the file dialog box."
          End If
       End With
    Set objDialog = Nothing
    
    End Sub
    I can't understand why every step has an issue, considering the original code came from a reputable source. I didn't have any problems with my folder picker to copy Folder paths, so I wasn't expecting this. Any guidance or a good source for all things FileDialog would be appreciated.

  4. #4
    Carouser is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Location
    Melbourne, Australia
    Posts
    56
    After an hour of diagnosis, I turned off the MSO reference Library, then checked it back on and now all is working except for the last code example where I still have an error-

    Me.FileList.AddItem varFile

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is the error message?

    Could it be: The RowSourceType property must be set to 'Value List' to use this method.

    Fix that property and code should run, it does for me.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Carouser is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Location
    Melbourne, Australia
    Posts
    56
    Ahhhaaa! moment. Thanks June7. It's all working.

    All my research today has not unearthed any further information or examples of what I am wanting to do with filedialog box. I can either setup to select a file, or setup to select folder, and as I don't know which I would need, I am not able to setup for both. As far as renaming, that will only be possible if I File/copy the file to another location, but the original will not be renamed. I want an original backup (Renamed) in the event something happens to the file in the processed folder. I'm running on empty right now as I am looking for solutions that don't exist and I'm not at a level to create them.

    Today I contemplated being a programmer, because if I got paid by the hour, I would be a millionaire in no time at the rate I am working at.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You are now dealing with issues I have no experience in. It would be a journey of discovery for me as well. I wish you luck in your effort.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Carouser is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Location
    Melbourne, Australia
    Posts
    56
    I have finally got some code together for copying files to another folder with a designated destination. It is working except there is no error handling and the Msgboxes are for information rather than having functionality at the moment- but works a treat. I want to be able to rename the file first and not sure what code I need to do so. I am attempting to use the Name "oldfile" As "newfile' method, but I think it needs some kind of instructions as the file is currently open. I have got the basic code of what I want to do, i.e created the rename variant, but my naming code doesn't do anything. I have spent a long time researching to get me where I am, and I hope I am on the right path.

    I have the code below and also attached is my test db so you can understand what I am trying to achieve. It is a simple process of selecting a destination folder and coping a file to it. The New Name is what I am working on, where entering a new name on the form will rename the file. I hope someone will be able to assist me and give me some guidance. Ensure you have MS Office Object Library referenced if viewing the db.

    Code:
    Private Sub cmdRenameCopy_Click()
    'Declare a variable as a FileDialog object.
        Dim fd As FileDialog
    
        'Create a FileDialog object as a File Picker dialog box.
        Set fd = Application.FileDialog(msoFileDialogOpen)
    
        'Declare a variable to contain the path
        'of each selected item. Even though the path is aString,
        'the variable must be a Variant because For Each...Next
        'routines only work with Variants and Objects.
        Dim OrigPath As Variant
        Dim OP As Variant
        Dim NNPath As Variant
        
      
        'Use a With...End With block to reference the FileDialog object.
        With fd
    
            'Use the Show method to display the File Picker dialog box and return the user's action.
            'The user pressed the button.
        'If .Show = -1 Then
    
                'Step through each string in the FileDialogSelectedItems collection.
                'For Each vrtSelectedItem In .SelectedItems
                If fd.Show = True Then
                
            For Each OrigPath In fd.SelectedItems
            
    
             
             OP = OrigPath
            
             OP = Split(OrigPath, "\")
            
               
               NNPath = Left(OrigPath, InStrRev(OrigPath, "\")) & NewName
               
                MsgBox NNPath
                
                'Name OrigPath As NNPath
                
             
       
    'MsgBox FullPath & "\" & OP(UBound(OP))
    MsgBox "Copy:" & _
            vbCrLf & "" & _
            vbCrLf & OrigPath & _
            vbCr & "To" & _
            vbCrLf & FullPath & "\" & OP(UBound(OP)), _
            vbOKCancel + vbQuestion, _
            "Accept or Cancel"
            
            
    
    FileCopy OrigPath, FullPath & "\" & OP(UBound(OP))
    
    MsgBox "File Copy Successful"
    
    Next
          
           
           ' Next
            End If
    
                    'vrtSelectedItem is a string that contains the path of each selected item.
                    'You can use any file I/O functions that you want to work with this path.
                    'This example displays the path in a message box.
                 
    
               ' Next vrtSelectedItem
            'The user pressed Cancel.
        'Else
            'End If
        End With
    
        'Set the object variable to nothing.
        Set fd = Nothing
    
    End Sub
    FileDialogTest.accdb

  9. #9
    Carouser is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Location
    Melbourne, Australia
    Posts
    56
    Update. I was closing down my computer, and discovered a file on my desktop which was the one I was trying to rename as renamed to New Name I assigned. The Name oldfile As newfile works except for the extension. It was a .jpg, but there was no file extension on the renamed file. So it works....sort of...

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't understand how this code is working for you. You should get a compile errror because Command6_Click() event has an orphan End Sub line following it. However, there is not even a button named Command6 so why is this procedure there?

    The List Files, Open Xplorer, Create Folder Path buttons do work.

    However, Rename/Copy button insists on opening file picker. Why should this take place? Shouldn't this procedure use the files already selected by List Files into the listbox?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Carouser is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Location
    Melbourne, Australia
    Posts
    56
    Sorry about the messy form. The List Files, Open Xplorer, Create Folder Path buttons were projects exploring the filedialog system.

    The Rename/Copy is what I have been working on and testing. And Yes, it is supposed to go to file Picker. In my other db I have a "Path field" and rename "Name Field" generated as part of as part of a record. To simulate, setup a "Test" folder somewhere and using the find path button, select the "Test" folder Path. The rename box is a txt box, and type in anything that you want to rename to -( I am still looking into this as file extension is lost from original file after renaming and I have disabled the Name As line). Select a file using Rename/Copy button (i have some test .jpgs) and the files will be sent to the destination folder. I am only able to copy and send files at the moment. What kind of additional code do I need to look at? At the moment I am happy that it copies files, but how would I know for sure if the files were copied? Do I need to setup code to check or is my code so lean that it will always work and if it doesn't windows will alert me of an error.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use code to get the file extension from the original file, like: Mid(fd.SelectedItems(InStrRev(fd.SelectedItems,"." )) to concatenate onto the new name. However, what if user anticipates and actually types the file extension in the textbox?

    Okay, am running only the Rename/Copy button procedure. So is user going to have the CreateFolderPath button? User needs to be aware to provide values in the FullPath and NewName boxes before clicking Rename/Copy.

    The first popup message concatenates the old folder path with the new filename. Then the next popup message shows the old folder/old filename copy to new folder/old filename. The file is copied to new location but with original filename. If this wrong, use debugging to figure out what to fix.

    Will be issue if folder has permission restrictions. I do have code that does automatic copy of database onto user local computer (not using filedialog, user is not involved) but must copy to the C:\ root level as folder permissions do not allow automatic copy into folders (Access is not authorized 'user'). IT sets up computers with restrictions I can't get around and C:\ root is only location available to me.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Carouser is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Location
    Melbourne, Australia
    Posts
    56
    My Reply in Red

    Use code to get the file extension from the original file, like: Mid(fd.SelectedItems(InStrRev(fd.SelectedItems,"." )) to concatenate onto the new name.
    Not sure how to use this function, will see if I can work it out.

    However, what if user anticipates and actually types the file extension in the textbox?
    You can assume that no file extension will be entered for New Name.


    Okay, am running only the Rename/Copy button procedure. So is user going to have the CreateFolderPath button? User needs to be aware to provide values in the FullPath and NewName boxes before clicking Rename/Copy.
    The folder Path and Name field are auto generated on the real db and the user only needs to select the file. The name box and path selector is just a simulation to generate values for testing.

    The first popup message concatenates the old folder path with the new filename. Then the next popup message shows the old folder/old filename copy to new folder/old filename. The file is copied to new location but with original filename. If this wrong, use debugging to figure out what to fix.
    This is correct. Am wanting to rename the file with New Name with .* and send to New path. So I would like to keep original extensions of files. See first response


    Will be issue if folder has permission restrictions. I do have code that does automatic copy of database onto user local computer (not using filedialog, user is not involved) but must copy to the C:\ root level as folder permissions do not allow automatic copy into folders (Access is not authorized 'user'). IT sets up computers with restrictions I can't get around and C:\ root is only location available to me.
    I will be setting up a LAN, no server. Each computer is standalone with IP. The BE will be on a main computer with the main shared folder where all files will be sent to.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Correction on the Mid function:

    Mid(OrigPath, InStrRev(OrigPath,"."))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    Carouser is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Location
    Melbourne, Australia
    Posts
    56
    Had to revise your function and managed to concatenate to get new file name and path with extension. I know it's very basic for all you programmers, but I'm learning all these new functions and how to incorporate them.

    NNPath = Left(OrigPath, InStrRev(OrigPath, "\")) & NewName & Mid(OrigPath, InStrRev(OrigPath, "."))

    I think I'm getting somewhere now. Thanks June7

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

Similar Threads

  1. How to copy a file from one folder to another in vba
    By pkstormy in forum Code Repository
    Replies: 2
    Last Post: 09-20-2012, 05:32 PM
  2. Replies: 1
    Last Post: 06-26-2012, 03:06 AM
  3. Replies: 21
    Last Post: 01-24-2012, 06:21 PM
  4. folder locked after attaching a file
    By jimg972 in forum Access
    Replies: 0
    Last Post: 05-25-2011, 06:21 PM
  5. Copy file to folder
    By tpcervelo in forum Programming
    Replies: 11
    Last Post: 08-31-2010, 10:01 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