5. Vbscript file RUN_MACRO.VBS Dim xlApp, xlBook Set xlApp = CreateObject("Excel.Application") '~~> Change Path here Set xlBook = xlApp.Workbooks.Open("C:\dq_export\Macro_run.xls", 0, True) xlApp.Run "Macro_run!Loopfiles" 'xlBook.Close 'xlApp.Quit Set xlBook = Nothing Set xlApp = Nothing 'WScript.Echo "Finished." 'WScript.Quit 6. Excel Macro code (this code is located inside of the the macro_run.xls file) Sub Loopfiles() Dim wb As Workbook Dim myPath As String Dim myfile As String Dim myExtension As String Dim FldrPicker As FileDialog 'Optimize Macro Speed Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual myPath = "c:\dq_export\csv\" myfile = Dir(myPath & "\" & "*.csv") 'Loop through each Excel file in folder Do While myfile <> "" 'Set variable equal to opened workbook Set wb = Workbooks.Open(Filename:=myPath & myfile) ';;;;;;;;;;;;;;WRITE YOUR CODE HERE FormatResult testMacro ' wb.Close SaveChanges:=True With wb .SaveAs Replace("c:\dq_export\excel\" & myfile, ".CSV", ".xls"), 50 'UPDATE: .Close True End With 'Get next file name myfile = Dir Loop 'Message Box when tasks are completed ' MsgBox "Task Complete!" ResetSettings: 'Reset Macro Optimization Settings Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Application.DisplayAlerts = False Application.Quit End Sub
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
12 Experts available now in Live!