Advertisement
Guest User

jose's dog-starving macro

a guest
Sep 21st, 2015
116
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Sub Loopfiles()
  2. Dim wb As Workbook
  3. Dim myPath As String
  4. Dim myfile As String
  5. Dim myExtension As String
  6. Dim FldrPicker As FileDialog
  7.  
  8. 'Optimize Macro Speed
  9. Application.ScreenUpdating = False
  10. Application.EnableEvents = False
  11. Application.Calculation = xlCalculationManual
  12.  
  13. 'Retrieve Target Folder Path From User
  14. Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
  15.  
  16. With FldrPicker
  17. .Title = "Select A Target Folder"
  18. .AllowMultiSelect = False
  19. If .Show <> -1 Then GoTo NextCode
  20. myPath = .SelectedItems(1) & "\"
  21. End With
  22. 'In Case of Cancel
  23. NextCode:
  24. myPath = myPath
  25. If myPath = "" Then GoTo ResetSettings
  26.  
  27. 'Target File Extension (must include wildcard "*")
  28. myExtension = "*.xls"
  29.  
  30. 'Target Path with Ending Extention
  31. myfile = Dir(myPath & myExtension)
  32.  
  33. 'Loop through each Excel file in folder
  34. Do While myfile <> ""
  35. 'Set variable equal to opened workbook
  36. Set wb = Workbooks.Open(Filename:=myPath & myfile)
  37.  
  38. 'this is like the only bit i've added
  39.  
  40. With ActiveWorkbook
  41. .SaveAs myPath & "\" & wb.Name, FileFormat:=xlCSV
  42.  
  43. 'ok i have now stopped adding stuff
  44.  
  45. wb.Close SaveChanges:=True
  46.  
  47. 'Get next file name
  48. myfile = Dir
  49. Loop
  50.  
  51. 'Message Box when tasks are completed
  52. MsgBox "Task Complete!"
  53.  
  54. ResetSettings:
  55. 'Reset Macro Optimization Settings
  56. Application.EnableEvents = True
  57. Application.Calculation = xlCalculationAutomatic
  58. Application.ScreenUpdating = True
  59.  
  60. 'i lied
  61.  
  62. MsgBox "Jose starves dogs to death, deliberately"
  63.  
  64. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement