CODEWITHSUNDEEP
excelvba
robvt
robvt

Reputation: 1

Is there a way to open Excel files from a folder, perform actions, then close or delete the files using a VBA macro?

I would like to;

New Excel source files are automatically copied into the source folder each day, so I need to open each source file, copy data to my table and then remove the files, ready for the next days files.

I have only been able to complete the steps related to copying the data fields from a single source file to the data table. I am unsure of how to do the iterative part to open one file after another. I am not an experienced VBA user. I need to open a source file, copy data from it to my table, then close/delete the source file and move on to the next source file.

Upvotes: 0

Views: 295

Answers (1)

EuanM28
EuanM28

Reputation: 252

Use Task Scheduler to run a bat file that runs a .vbs to open and runs macros in your excel file. Explanation and example here

The bat file will contain something along the lines of "D:\My Drive\EM SSC\PA\AttNew\Automation.vbs"

The .vbs file would be something along the lines of

Set xlsxApp = CreateObject("Excel.Application")
xlsxApp.Visible = True

Set xlsxWorkbook = xlsxApp.Workbooks.Open("D:\My Drive\EM SSC\PA\AttNew\AutomationProject.xlsm")
xlsxApp.Run("Macro1") 'Name of your macro

Closing your workbook would contain something like this, in your ThisWorkbook VBA editor

Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Closes all other 
worksheets par "MainSheet", saves user time not having to delete 
imported sheets everytime

For Each ws In ThisWorkbook.Worksheets
Application.DisplayAlerts = False

If ws.Name <> "MainSheet" Then 'If a worksheet is not named "MainSheet" it gets deleted
ws.Delete
End If

Next ws
Application.DisplayAlerts = True

MsgBox ("All sheets are deleted except specific sheet - After this, you 
can click either 'Save' or 'Don't Save' button") 'Message box to reasure user is okay with either option when closing the file

End Sub

I am not sure about next stages, but let me know how this works so far

Upvotes: 1

Related Questions