Solved

Incorporate a folder picker in existing macro code

Posted on 2013-06-14
5
331 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculating Sales Tax 13 64
Excel Web Add-in Where is Visual Basic used 9 69
NEED LOOK FOR NUM 23 27
Excel VBA Script 9 50
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

738 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