I currently have a macro that grabs the necessary data I need from the excel file which is the same format each time and saves it into a new file. I then use python to clean up the data and insert it into a database. The macro I am describing is as follows:
Sub CreateFormattedFile() ' ' CreateFormattedFIle Macro ' ' Keyboard Shortcut: Ctrl+q ' Application.GoTo Reference:="CreateFormattedFIle" Dim cell As Range Set cell = Range(Range("b5").End(xlDown), Range("b5").End(xlToRight)) Dim myCSVFileName As String Dim myWB As Workbook Dim tempWB As Workbook Dim rngToSave As Range Set rngToSave = cell rngToSave.Copy Application.DisplayAlerts = False On Error GoTo err Set myWB = ThisWorkbook myCSVFileName = myWB.Path & "\" & "CSVs" & "\" & "CSV-Exported-File-" & VBA.Format(VBA.Now, "MMM-dd-yyyy") & ".csv" Set tempWB = Application.Workbooks.Add(1) With tempWB .Sheets(1).Range("A1").PasteSpecial xlPasteValues .SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False .Close End With err: Application.DisplayAlerts = True End Sub
I want to run this macro once on any new excel file that is placed within a certain folder. How can I automate this process? If there is a way to do so through python then that would be preferable as I will be executing the python code when I want to insert the data thus if it would make it so I only need to execute one executable. It would also eliminate the issue of having to constantly check if a file has been added.