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
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
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
