Set Excel cell equal to string containing date without leading apostrophe

user_7296893 0
Viewed 280

I've got some code that's using .CopyFromRecordset to write strings to an Excel sheet. This recordset contains strings that look like dates and numbers, like 1 and 09-08-2018, and these are correctly left as strings.

I've recently added some additional processing that adjusts some cell values. However, when writing values back to the cells, I'm unable to preserve their current state (string, formatting: general, no leading apostrophe).

The following code illustrates my problem:

Public Sub MCVE()
    With Range("A1")
        Debug.Print .Value '09-08-2018
        Debug.Print .Value2 '09-08-2018
        Debug.Print VarType(.Value) = vbString 'True
        Debug.Print .NumberFormat 'General
        Debug.Print .PrefixCharacter 'Zero-length string
        .Value = .Value 'I want to manipulate the value here too
        '.Value2 = .Value2 'Yields the same result
        Debug.Print .Value '08-09-2018
        Debug.Print .Value2 '43351
        Debug.Print VarType(.Value) = vbString 'False
        Debug.Print .NumberFormat 'm/d/yyyy
        Debug.Print .PrefixCharacter 'Zero-length string
    End With
End Sub

I can manually set the .NumberFormat to @ to force it to text, but then both .NumberFormat and .PrefixCharacter change, and that causes trouble further along when importing it into another program. I can also pad the string with an apostrophe before assigning it, but that changes .PrefixCharacter too.

I've tried using .Value2 instead of .Value, no difference. .Text is read-only, so I can't use that for assigning. Setting things like Application.Calculation = xlCalculationManual also has no influence.

This seems very trivial, but I've yet to find a working solution after hours of trying.

vba excel
1 Answers

The following code:

Public Sub MCVE()
    Dim OldFormat As String
    With Range("A1")
        OldFormat = .NumberFormat
        .NumberFormat = "@"
        .Value = .Value 
        .NumberFormat = OldFormat
    End With
End Sub

solves the problem in Excel 2010 and 2013, but (as reported in comments) changes PrefixCharacter to apostrophe in 2016.

This version seems to work in all versions:

Public Sub MCVE()
    With Range("A1")
        .NumberFormat = "@"
        .Value = .Value 
        .ClearFormats
    End With
End Sub
user_39640750
Related