File dialog, rename, & save

[tburg73]

Ok, you guys have been a big help, so here's a tough one. At least for me! Lol.

I want to open a file dialog box in excel when clicking on say a button in cell A1, choose a series of files and have then automatically renamed with info from say cells, A2, A3, A4 and then resaved, with the new names saved in Cell A5 with each one separated by a pipe (|) or comma (,). I might need it to list each new file in separate cells, not sure yet until I get rolling with this.

I hope someone can help!!

 

[AFPathfinder]

This is most likely going to take some testing and tweaking, but it should give you a solid start.

Sub FilePickerMartini()
    Dim i As Integer
    Dim fd As FileDialog
    Dim vrtSelectedItem As Variant
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With fd
        .Title = "Select the file(s) you wish to use."
        .InitialFileName = Application.DefaultFilePath
        .AllowMultiSelect = True
        
        If .Show = -1 Then
        
            'Loop through the multiple files selected and rename
            For Each vrtSelectedItem In .SelectedItems
            
                'Use "i" in Offset to move the cell reference in renaming.
                Name vrtSelectedItem As Range("A2").Offset(i).Value & ".xlsx"
                i = i + 1
            Next vrtSelectedItem
            
        End If
    End With
End Sub

 

[tburg73]

Super. I really appreciate the input. I am pretty good with complicated excel functions and basic vba but this is a bit over my head. What if I just wanted to select a file and didn't need to rename it. I would also like the file picker to default to a specific folder. Is that possible?

This is most likely going to take some testing and tweaking, but it should give you a solid start.

Sub FilePickerMartini()
    Dim i As Integer
    Dim fd As FileDialog
    Dim vrtSelectedItem As Variant
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With fd
        .Title = "Select the file(s) you wish to use."
        .InitialFileName = Application.DefaultFilePath
        .AllowMultiSelect = True
        
        If .Show = -1 Then
        
            'Loop through the multiple files selected and rename
            For Each vrtSelectedItem In .SelectedItems
            
                'Use "i" in Offset to move the cell reference in renaming.
                Name vrtSelectedItem As Range("A2").Offset(i).Value & ".xlsx"
                i = i + 1
            Next vrtSelectedItem
            
        End If
    End With
End Sub

 

[AFPathfinder]

That makes it a bit easier. What do you want to do with the file besides select it? Chances are that if you are allowing multiple files to be selected, you'll still need the For Each loop.

The ".InitialFileName" bit inside the With fd statement is what is used to select the default path. Using "Application.DefaultFilePath" just uses the user's default path setting. If the filepath is going to include the login username of the user, you can variable the username value in the string.

.InitialFileName = "C:\Users\" & Environ("USERNAME") & "\Desktop"

If it's going to be a mapped shared drive, I'd suggest using the UNC path instead of a drive letter, or else you'll need to make sure all users are mapped exactly the same for the coding to work. But if you are going to use the mapped drive letter, make sure to ChDrive the drive letter before referencing the path:

ChDrive "P:"

 

[tburg73]

Shoot. I wasn't very clear. Sorry. For now, I just need to select a file and have it leave the file path in the cell. I will eventually need it to do what was in my OP but I've got some other external issues to work out first. ?

That makes it a bit easier. What do you want to do with the file besides select it? Chances are that if you are allowing multiple files to be selected, you'll still need the For Each loop.

The ".InitialFileName" bit inside the With fd statement is what is used to select the default path. Using "Application.DefaultFilePath" just uses the user's default path setting. If the filepath is going to include the login username of the user, you can variable the username value in the string.

.InitialFileName = "C:\Users\" & Environ("USERNAME") & "\Desktop"

If it's going to be a mapped shared drive, I'd suggest using the UNC path instead of a drive letter, or else you'll need to make sure all users are mapped exactly the same for the coding to work. But if you are going to use the mapped drive letter, make sure to ChDrive the drive letter before referencing the path:

ChDrive "P:"

 

[AFPathfinder]

For a single file, the code is pretty standard. I commented out the unnecessary bits for when you want to tackle that.

Sub FilePickerMartini()
    Dim i As Integer
    Dim fd As FileDialog
    Dim vrtSelectedItem As Variant
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With fd
        .Title = "Select the file(s) you wish to use."
        .InitialFileName = Application.DefaultFilePath
        .AllowMultiSelect = False
        
        If .Show = -1 Then
        
            Range("A5").Value = .SelectedItems(1)
        
            'Loop through the multiple files selected and rename
            'For Each vrtSelectedItem In .SelectedItems
            
                'Use "i" in Offset to move the cell reference in renaming.
                'Name vrtSelectedItem As Range("A2").Offset(i).Value & ".xlsx"
                'i = i + 1
            'Next vrtSelectedItem
            
        End If
    End With
End Sub

 

[tburg73]

Hats off! Thank you so much!

For a single file, the code is pretty standard. I commented out the unnecessary bits for when you want to tackle that.

Sub FilePickerMartini()
    Dim i As Integer
    Dim fd As FileDialog
    Dim vrtSelectedItem As Variant
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With fd
        .Title = "Select the file(s) you wish to use."
        .InitialFileName = Application.DefaultFilePath
        .AllowMultiSelect = False
        
        If .Show = -1 Then
        
            Range("A5").Value = .SelectedItems(1)
        
            'Loop through the multiple files selected and rename
            'For Each vrtSelectedItem In .SelectedItems
            
                'Use "i" in Offset to move the cell reference in renaming.
                'Name vrtSelectedItem As Range("A2").Offset(i).Value & ".xlsx"
                'i = i + 1
            'Next vrtSelectedItem
            
        End If
    End With
End Sub

 

[AFPathfinder]

You're welcome!