Correct bad Excel Data with vba 1

[GingerR]

Hi--I used to write big macros in Excel years ago, but since using MS Access, haven't been in Excel too much for about 8 years. I need some help figuring out how to correct a problem in Excel that is affecting my MS Access application.

At my company, there's a web-based application which collects engineering data. You can run tabular reports and export the results to Excel. There's a bug we discovered that if any of the data in a "cell" begins with a dash ("-"), all of the fields after that, for all of the rows after that, are translated as "text" even if they are "numeric" or "dates". So we end up with an Excel file that has wrong data types. Some people fiddle with the Excel file and just use Excel - they of course cannot sort because a single column will have date-type dates and text-type "appearing" dates, so essentially the file is useless to them; I import the data into MS Access to crunch it up and display reports and charts and such that the web site doesn't provide. SO...I always got import errors. My solution to date was to do this:

In the resulting Excel file, the first column should be numbers but as you scroll down the file, you can see that at some point they are left-aligned. Simply right-aligning them doesn't work: you have to select the left-alingned ones and click on the little Help-pop up and choose "Make these into numbers" or whatever it says. Ok, simple enough.

The second problem that I need help with is that there are four "date-type" columns. As usual, you can scroll down the file and eventually there will be some dates left-aligned. Again, it's not the "alignment", it's that they are exported from the web site as "text". BUT just selecting them and changing their "FORMAT" to "Date/Time" doesn't work either. My solution is to scroll to the right where there is no more data, and paste in some formulas; copy the results and Paste-Special back over the original wrong dates. Here are my forumulas (orig 'bad' data is in column I):


This is in column "R":

=IF(I2="","",IF(TYPE(I2)=1,"",DATEVALUE(I2)))

Then to the immediate right of that:

=IF(I2="","",IF(TYPE(I2)=2,DATE(YEAR(R2),MONTH(R2),DAY(R2)),I2))


Believe me, I fiddled and fiddled around with this to get this to work; there's no simple solution. In the past, this worked fine without the "IF(XX = "","") part, but lately a new problem has emerged. If the date is blank, it's not really blank. There's something in there that is causing a new error. For the past few weeks I have to physically go into the file, sort on the column in question (I sort ascending but all the blanks go to the bottom), select the first blank cell, phyically hit the DELETE key, then drop down that to the rest of the cells below. Then there are no problems. The error I get when I do not physically hit the delete key in each "blank" cell is "NUMERIC FIELD OVERFLOW" (this is an MS Access error when importing the file).

Ok, so, sorry this is so long of a post. There are lots of users who actually export these excel files (for their own engineering groups) then use my MS Access tool to import and crunch up the numbers.

Do you know how to re-write my forumulas above to make this invisible whatever-it-is in a "blank" date field get deleted, or better yet some code to automatically go thru all the data and "fix" it? Even have a user selects a whole column and then run codes that fixes the "date" data that makes date-appearing data become dates and blank-appearing data become truly blank would be an improvement.

Mainly the problem is this date column that has "blanks" that are really not "blank" I don't know what is really in there or how to fix it.

Can you help? If you ever need some MS Access answers, let me know. Thanks a ton.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[SkipVought]

Hi,

I did not slog thru your entire post.

The problems is that you have column(s) that have BOTH text and numbers. BIG MISTAKE!!!

values beginning with a DASH -- are these supposed to be negative numbers or are they truly text?

Ig you really have BOTH text and numbers in the same column, make TWO columns.

This will be a constant plague, unless you fix the sheet.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[GingerR]

Hi--this isn't the issue. The excel file gets exported this way from a company web-based tool; Microsoft will not fix the bug so we are trying to figure out a work-around. Each column should be all dates, all numbers or all text. Please read the whole post for the issue. Believe me, if I could have summed it up in one statement, I would. There's some funky things going on in this file.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[SkipVought]

If its a simple matter of the NUMERIC data being stored as TEXT...

Enter the value 1 in an EMPTY cell

Copy that cell

select the COLUMNS containg the offending values

Edit > Paste Special - MULTIPLY

All your TEXT becomes NUMERIC, including dates. Format as needed.

BTW, as you have discovered...

[red]Formatting changes NOTHING.[/red] The underlying VALUE remains unchanged.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[GingerR]

Ok, here's what I really need:

A date field is stored as text. I have written formulas to take care of it if there is data in the cell. However some of the cells are "blank" (no data) except obviously they are not really "blank" there is some invisible character in there that is causing problems when importing to Access. I've tried to discover what it is, but i can't.

If i write a formula

=if(A1 = "","YES","NO") it will report as YES. But still import errors. the only thing i can do to get rid of it is physically hit the DELETE key. Just wondering how to make a cell that appears as blank change to be whatever it is the delete key does.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[SkipVought]

So the other part is not an issue? This is what you REALLY need?

Dim r As Range, iDateColumns(0) As Integer, i As Integer[b]
iDateColumns(0) = Columns("C").Column 'adjust array limit and add column data[/b]
With ActiveSheet.UsedRange
   For i = 0 To UBound(iDateColumns)
      For Each r In ActiveSheet.Range(ActiveSheet.Cells(.Row, iDateColumns(i)), ActiveSheet.Cells(.Rows.Count + .Row - 1, iDateColumns(i)))
        If Trim(r.Value) = "" Then r.Clear
      Next
   Next
End With

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[GingerR]

Thanks--that worked. I tweaked it to go thru the four offending columns and if "", clear it and if not "", convert it to a "date".

Thanks.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html