VBA to browse for folder of excel files and then save as new file

[tomdriscoll72]

I need to be able to have a prompt to select the file folder containing excel files, then have the vba open each file and save them in a new folder. Below is the code that is not currently working:

Private Sub Command35_Click()
Dim strFile As String
Dim strPath As String
Dim strBrowseMsg As String
 
strBrowseMsg = "Select the folder that contains the EXCEL files:"
strPath = BrowseFolder(strBrowseMsg)
 
sFile = Dir(strPath & "*")
    Do While strFile <> ""
      Workbooks.Open (strPath & strFile)
      result = ActiveWorkbook.Name
 
ActiveWorkbook.SaveAs _
      FileName:="C:\Users\tdri004\Desktop\KeHe\KPOF\Loaded KPOFs into Database\" + result, _
      FileFormat:=51
ActiveWorkbook.Close True
strFile = Dir()
  Loop
 
 
 
End Sub

[zmbd]

Out of curiosity... why are we duplicating what the OS does quite well?

Do you need a custom dialog or will the windows dialogs work for you?

Also I would use the Name function to rename the files as this makes DOS do the work and not the vba engine.

[NeoPa]

I'm not sure you're looking to rename (or Move) the files, but even if copying them there is a FileCopy command that would do the job more simply than opening and resaving them.

If you want just the Excel files from the specified folder then change line #9 to specify "*.XLS" or "*.XLSX" (depending on the type of files you're dealing with).

I suggest you look at making those fundamental changes first, then return here with your new code and details of what is not working at that stage. No-one likes questions where the OP simply imparts the fact that something "isn't working". Why ask for help if it's working after all. What makes a question make sense is details of what isn't working and, when code is involved, which line of code is causing the problem if known.