I have attached a file (Excel). This is a sample file of an regular download from SAP. I run that Job on a daily base and save the results as an .xls file, but the Data is almost like "throwing dirt at the wall", which means: I have lots of columns with junk information and also some random columns that are empty and rows as well.
In the good news department> I have consistently the same Headers that I need to "rescue" and import into a new and neat table in Access (to further analyze). In our sample this would be Headername1 and Headername5. Everything else must go, before it gets imported into Access. What follows are the steps that I need in VBA Code:
1. Look for Folder C:\Cleanupfolder\Cleanup.x
ls
2. Have a Dialog Box pop up> Which Headernames do you need?
3. Now go Row by Row (it is most of the time the 8th row, sometimes more seldomly it is higher up, like the 6th row) and look
for the Headername I feed into the Dialog box. Take decision> THIS is THE column that will stay. Next. Where is the second (or third?)
Headername feed into Dialogbox. Now, hold on to those COLUMN NAMES, because...
4....Every column WILL be deleted, with exception of the ones from step 3. Everything shifts to the furthermost left.
5. Mostlikely there are a few rows ABOVE my headernames. They have to go too. Delete ehm.
6. Save this file as it is. Clean. Now import that into Access (creating the table? Or should i have a tempTable waiting for the info?)
Thanks guys. I am calling out. I really tried hard to accomplish this, learned a lot in the process, but my code parts are the work of a madman going crazy (I still have lot to go in terms of Visual Basic Coding... :-(
Thanks a lot. 500 Dingos to the guy who hits the Pinata ;-)
Start Free Trial
