Database download...how to check errors in the least steps?

[pete234]

Ok, I'm learning more and more but here's what I'm trying to do.

We have a database that exports a dump to .csv file on a web page. I save this sheet as .xls and then reopen.

There are 5 columns of information that can contain invalid data due to the fact that users leave placemarkers in the fields in order to save the record. These markers are often forgotten about and the record is changed to "closed" therefore, they don't check back. The markers are not consistent.

So I'm trying to figure out a better way than sorting and manually searching each line of order numbers and account numbers, to find the invalid data. I tried using Validation, but this seems I would have to recreate these rules each time (unless I could save it in a template?) Not sure if it could be a macro, but all I need to know is which database id's contain invalid data, and which column and item is invalid. thx

 

[zilpher]

Re: Database download...how to check errors in the least ste

Hi Pete

Welcome to the board.

Do you have control over the database dump? If so, could you alter the SQL to prevent these records being selected?

Otherwise, you should create an addin that:

Checks the ACTIVE workbook is the right type of book to work on

Runs thru each row looking for the data you know is incorrect (placeholders as you called them). For this part you could use the Find function.

Here's a starter for ten on finding those placeholders, I have assumed it will be a '¬'

You will also need to create a menu, there's no finer way IMHO than Mr Walkenbach's tip 53 http://j-walk.com/ss/excel/tips/tip53.htm

Hope this gets you started in the right direction.

Z

*********************
Sub FindData()
Dim rngRange As Range, strFirstRange As String

If TypeName(Selection) <> "Range" Then Exit Sub
Set rngRange = Selection.CurrentRegion.Find("¬")

If rngRange Is Nothing Then Exit Sub
strFirstRange = rngRange.Address

Do
Debug.Print rngRange.Row
Set rngRange = Selection.CurrentRegion.FindNext(rngRange)
Loop While Not rngRange Is Nothing And Not rngRange.Address = strFirstRange

End Sub
*********************

 

[pete234]

Re: Database download...how to check errors in the least ste

Thanks, I'll try some of these ideas out and see how that goes. Already jumped into another project too. Fun, fun...

PS: No control over the db it is hosted from the outside.