0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.