Excel: Inserting data and not autoformat in vba

Question

The data I am trying to insert is from a database, and while inserting the data it gets auto formatted to scientific format, is it possible not to auto format it? I don't want it to be in scientific format but in text format

Example that gets auto formatted: 20.E01

Answer 1

Format the worksheet columns to the appropriate excel type before you assign cell values:

Columns("A:A").NumberFormat = "0.00"

Or to be quick and dirty prefix everything with a ' to have it interpreted as text.

Answer 2

Set NumberFormat to "#" (like so: Sheet1.Cells.NumberFormat = "#"). This will only show numbers as integers i.e. no decimal part and no scientific notation. Set NumberFormat like "#.#" and so on to get precision.