Solved

Incorporate a folder picker in existing macro code

Posted on 2013-06-14
5
315 Views
Last Modified: 2013-06-16
Dear Experts:

below code performs a batch renaming of files by using cell values in Column A and Column B of the active worksheet (Column A values = old name; Column B values = new file name)

I would like to have a 'folder picker' incorporated in that code but I got no idea how this is coded.

I would appreciate it very much if somebody could help me with this.

Thank you very much in advance.

Regards, Andreas


Public Sub RenameFiles()

    Dim fso As Scripting.FileSystemObject
    Dim vFiles As Variant
    Dim i As Long
    Dim oFile As Scripting.File
    Dim sFilePath As String
    
    Const cFILE_PATH As String = "C:\Temp"
    
    Set fso = New Scripting.FileSystemObject
    
    vFiles = ActiveSheet.Cells(1).CurrentRegion.Value
    
    For i = 1 To UBound(vFiles, 1)
    
        With fso
            sFilePath = .BuildPath(cFILE_PATH, vFiles(i, 1))
            If .FileExists(sFilePath) Then
                Set oFile = .GetFile(sFilePath)
                
                oFile.Name = vFiles(i, 2)
                
            End If
        End With
    
    Next i

End Sub

Open in new window

0
Comment
Question by:AndreasHermle
  • 3
  • 2
5 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39249644
Well here it is:

This  Function will return the folder that the user chosses

Function GFolderName(fol As String) As String
Dim vrtSelectedItem

With Application.FileDialog(msoFileDialogFolderPicker)
    .InitialFileName = Application.ActiveWorkbook.Path
    .Title = "Please choose Folder location for: " & fol
    .InitialView = msoFileDialogViewDetails
    .Show
    
    
    For Each vrtSelectedItem In .SelectedItems
    GFolderName = vrtSelectedItem & "\"
    Next vrtSelectedItem
End With

Set vrtSelectedItem = Nothing

End Function

Open in new window


and you call this function like this
fFolder = GFolderName(Title)

So it returns the Folder in the variable fFolder and you call it with a value in Title that will be what you want to show when the dialog box opens up something like
Title = "Please Choose Folder"

Rgds/gowflow
0
 

Author Comment

by:AndreasHermle
ID: 39250157
Hi gowflow,

thank  you very much for your swift and professional support. As a matter of fact, I do not know how to 'merge' these two codes, i.e. how the 'calling' actually has to be done and I am sure the initial code also has to be changed, at least line 9 has to be deleted, am I right.

Regards, Andreas
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39250276
have your Sub and my Function in a Module and then yes


here it is

Public Sub RenameFiles()

    Dim fso As Scripting.FileSystemObject
    Dim vFiles As Variant
    Dim i As Long
    Dim oFile As Scripting.File
    Dim sFilePath As String
    Dim fFolder as String

    'Const cFILE_PATH As String = "C:\Temp"
    fFolder = GFolderName("Please Choose a Folder")

    
    Set fso = New Scripting.FileSystemObject
    
    vFiles = ActiveSheet.Cells(1).CurrentRegion.Value
    
    For i = 1 To UBound(vFiles, 1)
    
        With fso
            sFilePath = .BuildPath(fFolder , vFiles(i, 1))
            If .FileExists(sFilePath) Then
                Set oFile = .GetFile(sFilePath)
                
                oFile.Name = vFiles(i, 2)
                
            End If
        End With
    
    Next i

End Sub 

Open in new window


try it and let me know.
gowflow
0
 

Author Closing Comment

by:AndreasHermle
ID: 39250599
Hi gowflow,

great this did the trick. Thank you very much for your professional and swift help.

I really appreciate it.

Regards, Andreas
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39250958
Your most welcome and glad I could help
gowflow
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question