Expanding local processing of one file to a folder of many files

elmetal

New Member
Joined
Apr 17, 2013
Messages
15
I have a VBA Script that does a few things.

Sheet 1 is the button to start the process. Sheet 2 (name is prepped) is a template sheet with 800+ columns.

The process right now is to open the file to process, which is named TemporaryExport. So you have this template and the TemporaryExport open.

It basically runs this sub function on the TemporaryExport file which basically matches the column header names to the "prepped" sheet and copies it all over.

When that is done, it exports that "prepped" sheet to a final file and deletes "temporaryExport" to prepare the folder for the next file. I do this 6 times for 6 different files.
After each one, I have to go to the folder and rename the file to the proper name


It works perfectly fine. Here's the issue. Right now I export 6 files from access via a form button and they all export to TemporaryExport.XLSX. So I do one by one, then open my template excel, run the VBA, rename the file when it's all done, then export next file from access, open template, run vba, rename etc.


What I would like to do instead is to have 2 folders, for simplicity's sake let's call it G:\Export for the files exported from access, and G:\Ready for the complete post processed files in excel. So my goal is to export all 6 files from access with one button, and then once exported to 6 separate excel files, the post processing runs for each file in the folder and the subsequent filename is G:\Ready\OriginalFilename.xlsx


Not sure if all that makes sense.

Ideally it would be great if it could also only run for the present files in the folder (in case I have only one file that day instead of all 6 it doesn't have any errors)


Here's the short VBA that calls the main processing function


File-Copy-icon.png

VBA Code:
Sub Update()


Dim TempWorkbookPath As String

Worksheets("Prepped").Range("A4:AFF50000").Clear


Application.Run "'" & Excel.ActiveWorkbook.FullName & "'!copyDataBlocks"
PathName = "" & ThisWorkbook.Path & "\AccessExportedDone.xlsx"
Sheets("Prepped").Copy
ActiveWorkbook.SaveAs Filename:=PathName, _
FileFormat:=xlWorkbookDefault, CreateBackup:=False
ActiveWorkbook.Close
Sheets("Prepped").Range("A4:AFF50000").Clear
MsgBox "Exported successfully to " & PathName
ActiveSheet.Cells(1, 1).Select
Workbooks("TemporaryExport.xlsx").Activate
TempWorkbookPath = ActiveWorkbook.FullName
ActiveWorkbook.Close
Workbooks("Template.xlsm").Activate
Kill TempWorkbookPath
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Watch MrExcel Video

Forum statistics

Threads
1,127,808
Messages
5,627,010
Members
416,214
Latest member
boston814

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top