Zero Length String Microsoft Access

问题:

In excel, I have a text string, which contains numbers and text, that I strip the numbers out of with a formula. However, sometimes it is possible that the text string will be blank to begin with so if this happens I use "" to return a blank instead of a 0. When I link this excel sheet to an access database it will not let me format this column as currency because it is picking up the "" as text along with the stripped out numbers as numbers. Any solutions on how to fix this? Is there another way besides "" to make a cell completely blank and not a zero length string?

This is along the lines of the problem I am having: http://support.microsoft.com/kb/162539

回答1:

Here is a quick routine that reduces formulas to their values and strips zero length strings to truly blank cells.

Sub strip_zero_length_string()
    Dim c As Long
    With Sheets("Sheet1").Cells(1, 1).CurrentRegion
        .Cells = .Cells.Value
        For c = 1 To .Columns.Count
            .Columns(c).TextToColumns Destination:=.Cells(1, c), _
              DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
        Next c
    End With
End Sub

I'm using .CurrentRegion so there can be no completely blank rows or columns within your data block but that is usually the case when preparing to export to a database.