Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Incorporate a folder picker in existing macro code

Posted on 2013-06-14
5
Medium Priority
?
343 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 31

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 31

Accepted Solution

by:
gowflow earned 2000 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 31

Expert Comment

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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.

715 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