excel empty cells to ""

[hamking01]

I'm trying to import an excel sheet into an Access database via ASP.net pages. I need to make sure that all blank cells within Excel are "". How can I go about doing this. I've also posted this in the MS Office forum. Thanx

 

[Killian01]

if you are importing a range using VBA you could use something like:

Set myRange = Selection
For Each Cell In myRange
If IsEmpty(Cell.Value) Then
Cell.Value = ""
End If
Next

 

[Loomah]

hamking01
I'm a little confused by what you mean "blank" because as far as Excel is concerened "" is the same as Empty. Do you mean that you want to check for cells that have a value but appear empty eg a value of " "

The following code is a little overkill but may lead to what you are after. Test on a range of cells that have values like " ", "", "X", " 2 ", " " etc

Sub a()
Dim c As Range
Dim i As Integer
Dim boo As Boolean

For Each c In Selection
boo = True
MsgBox "cell " & c.Address(False, False) & _
    " value is """ & c.Value & """"
    If Len(c) > 0 Then
        For i = 1 To Len(c)
            If Mid(c, i, 1) <> " " Then
                boo = False
                Exit For
            End If
        Next
    End If
    If boo Then MsgBox "cell " & _
        c.Address(False, False) & " 'appears' empty"
    
    If IsEmpty(c) Then
        MsgBox "cell " & c.Address(False, False) & " is empty"
    Else
        MsgBox "cell " & c.Address(False, False) & " is not empty"
    End If
Next
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[hamking01]

Loomah, used your code to figure out that the Access database is not accepting values with cell values "". If I change the "" to " ", it works. However, I have to do this manually in each cell to make sure they're all " ". I've tried Killian's code to format the entire region with the following:

Range("A1:BA15").Select
Set myRange = Selection
For Each Cell In myRange
If IsEmpty(Cell.Value) Then
Cell.Value = " "
End If
Next

This will format all the cells with "" to " ". The text, date, and number formatted cells are all intact in excel. However, once I import it into an asp.net datagrid, the date and number cells are blank, while the text cells are present. Is the code above somehow formatting these cells and causing them not to be displayed in the datagrid?

I've also posted this in asp.net forum.

 

[Loomah]

hamking01
The code shouldn't be doing anything to any cells that aren't empty. It will just ignore them.

You could try changing it slightly from
If IsEmpty(Cell.Value) Then
to
If Cell = "" Then
but it shouldn't make any difference to what the VBA is doing.

Did you declare both Cell & myRange as Ranges? I think I'm clutching at straws now!!

Unfortunately I don't know the first thing about asp.net - or anything.net!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[PHV]

the Access database is not accepting values with cell values ""
Simply allow the fields to be Null and then don't touch your cells ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244