No announcement yet.

Turning off Automatic Formatting (Dates)

  • Filter
  • Time
  • Show
Clear All
new posts

  • Turning off Automatic Formatting (Dates)

    I've been trying to find information on turning off one of excel's default features. I have a very large chunck of VBA code that extracts data from an acess database and outputs it onto excel, however some data is being "Confused" as a dates.
    What I need to do is make sure that a value such as "1SEP" isn't being confused for September the first, and formatted. I've tried setting the NumberFormat property to "Text" (or any other format), but that doesn't work because after the value was initially converted to a date, it cannot be converted back without returnning a number (The date seriel number I assume?).
    Basically, I was wondering if there is a way to turn off this feature before I start outputting my data. Thanks in advance for the responses,


  • #2
    Try adding a single quote to the beginning of each date-like value as you output it. This should force Excel to treat the value as a string. The single quote will appear in the formula bar, but when you access the value of the cell using (for example) ActiveCell.Value, it will be omitted.


    • #3
      Thanks for the reply.

      A neat little brutal way to foce excel to do my bidding, but I'm not sure if it will work out well with my project because I do not really know which data will be "Date-like" and which will be normal. (I'm pulling this all from an access database created by a separate program.) I could add a single quote to all my non-numeric data (Which basically resides in a certain column), but that doesn't seem an ideal solution for a large excel sheet just because of a single possible co-incidence.
      I was thinking I could try to query for the format in advance, then if it is a date format then add that approach, but that won't work because, like I mentioned, as soon as the data is converted to a "Date" the actual value of the data becomes ruined.
      Thanks a lot for the response; I'm doubting that there's any other solution to this irksome problem, but one can always hope. Wishing there was a property .disableannoyingassumptions = false,



      • #4
        Okay I just wasn't thinking very well...There's a much easier solution than appending the data to force it to be a string, I just didn't think of it for some reason. Thanks for the response though.

        Because I know which columns will contain text only data (A "Name" type column), I just needed to format that as a "Text" type before any data is written to it. That makes a lot more sense than trying to change the format after a different format is already assigned to it.

        Basically, the code is:
        Range("B2").EntireColumn.NumberFormat = "@"