operate VBA macro on multiple files in a directory

[skkakkar]

Hi,
I have contract notes files in .xls format towards sale of equity shares from the brokerages. I have to do some editing in these files like deleting some rows, adjusting width of columns and inserting date of contract.
a) Contract-A.xls shows original file.
b) Contract-B.xls shows modified file
c) VBA macro code used for deleting rows and modifying column width is shown in modify_contractnotes.xlsm file.
d) Presently I open the file and copy and paste the contents on the macro-enabled workbook sheet . After macro operation I transfer back the contents by copy and paste to a master file(Contract-C.xlsx) and clear the contents in the macro enabled workbook to make it ready for another operation.
e) It is time consuming process for around 60 files per month.
f)After this operation I run RDBMerge add-in to consolidate and normalize all these files in a directory. RDBMerge smoothened out merged cells also.

Is it possible to have a macro-enabled workbook open all the time and the contract files be opened one after other and macro contained in macro enabled workbook by modifying its code suitably operate on these contract files. Any other way to automate and reduce cycle time is also welcome.
Files are attached in modify_contractnotes zipped folder.

 

[Ranman256]

My methodology is:

In access, I click on the IMPORT button.
This scans the folder for the excel files,
1 by 1, it opens the file
formats, deletes rows, etc
then saves to the same file everytime to import...File2Import.xls.
This file is already attached/linked in the db as an exteral file, so

after the file is saved to File2Import.xls,
then it runs the qaImport query to append the xl data to a table.

until all xl files in the folder have imported.

 

[skkakkar]

Hi Ranman256

The contract file provided by me has combination of merged cells and normal cells. I have Access 2007 and it is not imported in right way. I do not find facility of repeated automatic import of files in Access 2007. Purpose of deleting some portion of file is to remove specially formatted portion of contract note and leave table portion which can be easily imported. Please review the structure of files attached to appreciate my problem.
Thanks for your kind indulgence in this matter.

 

[Ranman256]

This is all custom code. The method is:
open the next excel file (in excel)
use excel macros to correct the file (which you record in XL by fixing a file)
save the file to the import filename
close excel
in access , import the corrected file

 

[skkakkar]

Hi Ranman256
Your guidance works fine. Thanks a lot