Cleaning the files using macro

[swathidas]

Hi Gurus,

I am new to vb coding and in fact this is my first xls macro which I am not sure how to start, need your guidance here.

Our source files will be pushed to Z:\Maintenance folder here we have more than 500 xls files, these xls files have two tabs 1. Data Load strategy and 2. Data.

I need to clean the data in first tab i.e. in Data Load strategy tab, here in this tab the data is swapped and jumbled across the columns I need to keep the data which is starting with a number and delete rest of the data. I have attached the sample file.

Till now I was doing it in unix which was pretty much straight forward using sed commands, but as the source will be sending to windows machine we need to clean this in windows environment itself.

Please tell me how to

1.start, is there any particular function which will print the records starting with numbers in a macro.
2.I need to automate this process, that if I run the macro the macro should clean the files present in the source folder without any user input.

Thanks,
Swathi Das

[Leith Ross]

Hello swathidas,

Welcome to the Forum!

Which column needs to be checked for numbers?

[swathidas]

Hi Leith Ross,

Column A in Data Load strategy tab should be checked for numbers.

Thanks,
Swathi Das

[Leith Ross]

Hello Swathi,

This macro should do clean any xls or xlsx files in the folder "Z:\Maintenance".

Please Login or Register  to view this content.

[swathidas]

Hi Leith Ross,

Thanks for the macro , I will execute this and let you know.

where can I start learning the xls macro programming and how many days it will take to learn this completely.


Thanks,
Swathi Das

[swathidas]

Hi Leith Ross,

I tried executing this code,I am getting compilation error

Please Login or Register  to view this content.

.


Please help.

Thanks,
Swathi Das

[Leith Ross]

Hello Swathi,

Sorry about that. Here is the corrected code. The change is marked in bold text.

Please Login or Register  to view this content.

[swathidas]

Hi Leith Ross,

Sorry for troubling you , I executed the macro successfully but the macro is not cleaning the file . can you please tell me if I am missing some thing.

Thanks,
Swathi Das

[Leith Ross]

Hello Swathi,

If you can post one the files that needs to be cleaned, it will help me troubleshoot the problem accurately.

[swathidas]

Hi Leith Ross,

Please find the attached sample data file.

Thanks,
Swathi Das

[Leith Ross]

Hello Swathi,

I updated the macro. Part of the problem was I corrected the worksheet name's spelling in my test. You have the name as "Data Load stratergy" and I corrected the spelling thinking it was a typing error. If all of your files have this sheet name spelled this way, you can the variable WksName to match it. If a worksheet can not be found because either it doesn't exist or the name is misspelled, there is no message to alert you. The macro simply continues on to the next workbook.

Replace the macro in your workbook with this one. Change the worksheet name if needed. Currently, it is the same as in the posted file.

Please Login or Register  to view this content.