I have a process that requires me to run a query from a home grown app that exports the data to an excel file. Since my database, is formatted a bit differently this requires me to massage this data so I can simply copy and paste the data into my Production metrics database. I have written some code, and gotten help on this forum, to help clean up, rearrange and sort this data. I have to pull this data every week from a separate database, massage it into the form I need and then copy and paste it into a Production Metrics excel sheet that I have built locally here.
I need is some help with four things with the MIDataCleanup VB script:
1. Cleaning up my script. Since I am relatively new to this I know things can be scripted more efficiently.
2. Current MIDataCleanup script loses data when I run it. The data from column K "Outage start time" disappears when it gets to the date and time separation process.
3. I need to do a clean data process. You can see in cell N2 that there is a space in the cell and these issues result in formula errors when I copy and past the information from MIDataCleanup file to the Production Metrics file. I got some help here with a clean data process that was built for the production metrics page, but after using it thought that it would be better to do it on the data from the MIDataCleanup page as I didn't have to run the clean data on the whole excel Production Metrics file. So I copied the code into the MIDataCleanup script and then tried to adjust it to run on sheet 1 in the MIDataCleanup file. However, I wasn't successful.
4. Column E requires a True/False value. The source data reports this a null or Yes. What I need to do is change this to a True for Yes and False for a null value. I got this to work, sort of as you can see the results go all the way down to row 33 and I only need that to go to the last row of data.
I know this is a lot and may be confusing so my apologies up front. I have attaced my files for your review.