VBA to get folder name

[imskin]

I want to open and save multiple csv as excel file. Below is the macro that i found. but this macro only can use in my own computer as it is hard coded to "e:\new foler\" and "c:\testing2\". How can make mypath and mypath2 as variant with get application.foldername?

Sub TransformCSVToXls()
Application.DisplayAlerts = False
MyFile = ActiveWorkbook.Name
myPath = "[FONT=arial black]E:\New Folder\[/FONT]"
mypath2 = "[FONT=arial black][B]C:\Testing2\[/B][/FONT]"
WorkFile = Dir(myPath & "*.CSV")


Do While WorkFile <> ""
   Application.StatusBar = "Now working on " & WorkFile
   Workbooks.Open Filename:=myPath & WorkFile
    ActiveWorkbook.SaveAs Filename:=mypath2 & _
      Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4), FileFormat:=xlNormal
   ActiveWorkbook.Close
   Windows(MyFile).Activate
   WorkFile = Dir()
Loop


Application.StatusBar = False


End Sub[CODE]

 

[patel45]

Sub TransformCSVToXls()
    Application.DisplayAlerts = False
    MyFile = ActiveWorkbook.Name
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    InitialFoldr$ = "E:\" '<<< Startup folder
    With Application.FileDialog(msoFileDialogFolderPicker) 'User input for folder to look at
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder source"
        .InitialFileName = InitialFoldr$
        .Show
        If .SelectedItems.Count = 0 Then Exit Sub
        mfolder = .SelectedItems(1) & "\"
    End With
    myPath = "mfolder
    InitialFoldr$ = "C:\" '<<< Startup folder
    With Application.FileDialog(msoFileDialogFolderPicker) 'User input for folder to look at
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder target"
        .InitialFileName = InitialFoldr$
        .Show
        If .SelectedItems.Count = 0 Then Exit Sub
        mfolder = .SelectedItems(1) & "\"
    End With
mypath2 = mfolder
WorkFile = Dir(myPath & "*.CSV")
Do While WorkFile <> ""
   Application.StatusBar = "Now working on " & WorkFile
   Workbooks.Open Filename:=myPath & WorkFile
    ActiveWorkbook.SaveAs Filename:=mypath2 & _
      Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4), FileFormat:=xlNormal
   ActiveWorkbook.Close
   Windows(MyFile).Activate
   WorkFile = Dir()
Loop
Application.StatusBar = False
End Sub

 

[imskin]

Exactly what i' looking for. Thanks