Search code examples
sqlexcelvbaformatcell

VBA - Convert a few numeric cells as text

I have a column called MyColumn that stored numbers. I want to convert this column to text and, for the first few rows (e.g., rows 2 to 5), I want to stored the values as text. This is similar to double-clicking the cell and pressing ENTER when Excel starts showing an exclamation mark (warning) on the cell saying "Number stored as text". The reason I want to do this is because I want to import this data into SQL and I want to ensure that SQL interprets the column as a text column. I have the following VBA code to do this which converts the column to text but doesn't do the second part i.e., simulating the double-click+ENTER. Please can someone advise.

MyColumn.NumberFormat = "@"
For i = 2 To 5
    MyColumn.Cells(i, 1).Activate
    SendKeys "{F2}"
    SendKeys "{ENTER}"
Next i
Solution

  • Insert programatically a single quote before the text which will remain always text