Macro to delete blank rows & columns from files

[Alka Bajaj]

Hello Expert,
There are number of report exported daily from SAP application. All these are stored in a folder.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I need to merge all this reports into one . But before I do, I have to perform following steps<o></o>
<o> </o><o> </o>
Steps Performed:<o></o>
<o> </o>
1. Open the file ( Since the files are exported, every time I receive a warning message “Excel export: The file you are trying to open, '[filename]', is in a different format than specified by the file extension…. Do you want to open the file now”). <o></o>
2. Click “Yes’<o></o>
3. Delete first 4 blank rows<o></o>
4. Delete 6<SUP>th</SUP> blank row<o></o>
5. Delete first 5 blank columns<o></o>
6. Save the file as excel<o></o>
7. Loop through all the files in a folder<o></o>
<o> </o>
Appreciate if you can guide me to automate above steps using macro. If I can automate above, for all the files in a folder.
<o></o>
<o>I am not able to attach the sample file. Let me know if it is required to be mailed.</o>
<o></o>
Awaiting your support and help.<o></o>
<o> </o>
Many thanks!<o></o>
<o> </o>
Regards,<o></o>
Alka <o></o>

 

[Trevor G]

Try something like this

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Open_My_Files()<br><SPAN style="color:#007F00">'Open all workbooks</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MyFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> myPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>myPath = "M:\Access Files\" <SPAN style="color:#007F00">'Change path</SPAN><br>MyFile = Dir(myPath)<br><br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> MyFile <> ""<br><SPAN style="color:#00007F">If</SPAN> MyFile <SPAN style="color:#00007F">Like</SPAN> "*.xls" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'Check file extension</SPAN><br>Workbooks.Open myPath & MyFile<br>Range("1:4,6:6").Delete Shift:=xlUp<br>Columns("A:E").Delete Shift:=xlToLeft<br>ActiveWorkbook.Save <SPAN style="color:#007F00">'You will need to check this out</SPAN><br>ActiveWorkbook.Close <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>MyFile = Dir<br><SPAN style="color:#00007F">Loop</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Alka Bajaj]

Thanks Trevor.

It works